Search multiple tables from one form field

[catrue]

Hello,

Stuck on a problem and was wondering if anybody has a possible solution. I currently have the beginnings of a database setup that uses a search field on a form to find a reference listing using a part number input by the user. It finds the part number, and then displays all the information about it on another part of the form. My question/problem is, I now want to expand this database to multiple specialties, all with different parts in their respective listings, so they would have different tables. The tables would be the same format, just different information. No one table would ever contain the same info.

Q: How would I set up a form to search for a unique part number over multiple tables, and return that parts info?

It works great for one table, but I am stuck on the multiple tables.

Thanks in advance!

 

[llkhoutx]

Don't you want to search multiple tables, as opposed to forms, for parts info? Searching tables is quite easy via VBA. However, you don't specify what you want to do with the info, once you get it. More information is required to provide you further help.

A solution might look like the following:

dim db as dao.database
dim rs as dao.recordset
dim strSQL as string
'if partnumber is a string
strSQL="[PartNo]= " & chr(34) & forms!CurrentformName!PartNumber & chr (34)
'if partnumber is a number
strSQL="[PartNo]= " & forms!CurrentformName!PartNumber
set db=currentdb
'sTableName is an argument to this code
set rs=db.openrecordset(sTableName,dbopendynaset)
rs.findfirst strSQL
if rs.nomatch then
msgbox "No record found!"
goto ExitHere
end if
'rs is positioned on the record you want
'rs.fields(0) through rs.fields are the fields returned
'do whatever you want
ExitHere:
rs.close
db.close
set rs=nothing
set db=nothng

Note that the Microsoft Dao 3.6 Object Library reference must checked to use DAO code.

 

[catrue]

I am only using one form. I want that one form to use multiple tables in a common search field.

 

[llkhoutx]

I can't understand what you mean by

I want that one form to use multiple tables in a common search field.

especially, "common search field."

 

[catrue]

I have a form, that has a text field on the left side of the form that a user will input a part number (ie, 1234). They hit the search button and it will look in the table to find that part number. On the right side of the form, there are more text fields that will be filled in with the parts information if a match is found (stock number, reference, nomenclature, etc). "common search field" refers to just one text field to input the search string into.

With one table it works fine, I just can't get it to look in more than one table. I just tried creating a Union Query and it seems to pull the information like I want, but I need to play with it some more. Not sure if thats the best way to go.

Hope that clears up what I am doing.

 

[llkhoutx]

Just create separate subforms for each table to be searched with the PartNo as the Master Link - even if one field is all that's displayed, PatNo on the subform can be hidden. I don't think that you'll have to Requery each subform on the PartNo AfterUpdate event, they should juyst tumble to the correct reocrd. If they don't, requery them.