Search multiple fields/tables-one field parameter

[tinkertoy]

I have searched and this doesn't seem to come up anywhere. I want to search for a barcode from two different tables, the field name is only slightly different in both. The tables are related by the Primary key field from the Main table, MediaID. I know I need to create a query that joins the two tables, to include the 2 barcode fields and the MediaID. I am creating a form with an unbound field for the code or whatever that the user can run the barcode in (they can type it in or scan it) that will search the two tables with the barcode fields. What happens when the barcode is located with the search button, the form with the linked MediaID will open and "show" the information that was requested.

Properties:

tblMedia - PK - MediaID; Barcode
tblRelatedMedia - RelMediaBarcode; FK - MediaID
frmMediaInventory (subform has the related media info in it)

qrySearchBarcode - table1: tblMediaInventory - MediaID & Barcode
table2: tblRelatedMedia - RelMediaBarcode
Linked by MediaID(One to Many)

Search form - frmSearchBarcode - 1 label with brief instruction, 1 unbound Barcode Field, 3 commands - Search, Clear & Exit

Thank you in advance!! :~)

 

[DougP]

Use my Lovely FAQ
faq702-1177

DougP, MCP

 

[tinkertoy]

Thank you - it looks great and reads like it will work - but, in step #4 - what is the 'sub other form'? and in the record source the object in the brackets [OPENSYSTEMCUST] what do I use, my qrySearchBarcode? I have the code figured, I think behind the search button, on the line MySQL = "SELECT * FROM [PhoneLog] WHERE " I replace the bracket object with qrySearchBarcode, would that be correct? The module is fine, then I get to the Click event of the subform:


Dim SyncCriteria As String
Dim f As Form, rs As Recordset

'Define the from object and recordset object for the AutoCAD form
Set f = Forms(Screen.ActiveForm.FormName)
Set rs = f.RecordsetClone

' define the criteria used for the sync
SyncCriteria = "[YourField]='" & Me![YourField] & "'"

' find the corresponding record in the Parts table
rs.FindFirst SyncCriteria
f.Bookmark = rs.Bookmark

Where is the part that goes to the Main form, frmMediaInventory?