Dynamically Change data source

[BradleyGK]

I have ten separate tables of data. Each table is sorted
by product type. Each record set in the tables is sorted
by a serial number.

In a form I would like to input the product type and
serial number and then have the form pull up part number
and other information from the correct table.

So when I enter the product type the code in the form for
the part number will choose the correct table out of ten
and then the serial number entry tells the correct record
set inside the table to pull the part number.

1) Is this possible to dynamically change the data source
2) and if so HOW?

thanks for any input

 

[Marshall Barton]

I have ten separate tables of data. Each table is sorted
by product type. Each record set in the tables is sorted
by a serial number.

In a form I would like to input the product type and
serial number and then have the form pull up part number
and other information from the correct table.

So when I enter the product type the code in the form for
the part number will choose the correct table out of ten
and then the serial number entry tells the correct record
set inside the table to pull the part number.

1) Is this possible to dynamically change the data source
2) and if so HOW?

You can set the form's RecordSource property to an SQL
statement that uses the values in your form header
txtProductType and txtSerialNum text boxes.

Dim strSQL As String
strSQL = "SELECT part, field2, field3, ... " _
& " FROM " & txtProductType _
& " WHERE SerialNum = " & txtSerialNum
Me.RecordSource = strSQL

A word of warning is due here. The presence of multiple
tables with the same fields is a strong indicator that your
database is not properly normalized. You should have only
one table with a ProductType field instead of separate
product tables.