search through on mutliple tables

[pronate]

hi,

I have a few tables....10 of them. User need to enter a search criteria into a textbox in a form to search through all the tables. What is the best way to do it?

All the tables contain a common field i call "Part no", the user only search this field.

Thanks.
Sam

 

[KenReay]

Hi

What do you want to extract?

If all tables have same format, or you just want to extract say PartNo, Description

Immediate thought is a UNION query, so

SELECT PartNo from tblONE WHERE partNo = [Enter Part]
UNION
SELECT PartNo from tblTWO WHERE partNo = [Enter Part]
UNION
SELECT PartNo from tblTHREE WHERE partNo = [Enter Part]
...etc
SELECT PartNo from tblTEN WHERE partNo = [Enter Part] Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[pronate]

thanks..

is it possible that i use the docmd.runsql to run this in VB?

That is:
==================================================

Private Sub Text25_AfterUpdate()

Dim enterpart As String

enterpart = Me.Text25


DoCmd.RunSQL "SELECT [Part No] FROM [13-XXX] WHERE [Part No] = [EnterPart] UNION SELECT [Part No] FROM [HTR3-Tip] WHERE [Part No] = [EnterPart];"

I get an error that says:

RunSQL action requires an argument consisting of an SQL statement.

Please help, thanks!

 

[KenReay]

Hi

Yes, because DoCmd.RunSQL requires an action query (ie UPDATE, DELETE..etc)

If you want to process the extracted data, which I assume you do, you need to make a recordset, either explicitly in code, or as the recordsource of a form or report

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK