Trying to pull data from multiple tables into one form 1

[JimboSHU]

Ok In a form I have four comboboxes. One allows you to choose a code number, the second allows you to choose the name associated with that code number, the third allows you to pick what product line this item is is going to be made in, and finally the fourth is linked to the third and narrows the product line down even further.

What I need to do is based on these combobox selections have a set of textboxes pull data from one of four tables. The data is formatted the same way in each table, it's just that the information itself is different depending on your selection and obviously some certain selections will pull from the same table. Any help will be appreciated.

 

[allanon]

The only thing that comes to mind is a case statement.

 

[JimboSHU]

Can you give me an example on how one would look, I'm pretty new at this.

 

[allanon]

The stuff you want to do is quite complex and I really cannot help you with that without a lot of time. Here is what I can do.

I can show you how to use a case statement with a combo box

I set up a test form and on that for I placed a combobox called cboTest. I placed a buttom called cmdTest.

In the combobox I placed these values in the row source:
"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine";"Ten"

row source type is value list. Note that if you are using a lookup to a table then you do not need the value list and there will most likely be a key and your displayed field will be the second field.

In the click event of the button I placed this code:

Select Case Me.cboTest.Column(0)
Case "One": MsgBox ("The is the first case statement.&quot
Case "Two", "Three", "Four": MsgBox ("This is the second case statement.&quot
Case Else: MsgBox ("This is always the last case statement.&quot
End Select

If you have a key on the combobox you column might be (1) instead of (0) but it still works the same.

That's all there is to a case statement.

Have fun

 

[JimboSHU]

This Maybe a Shot in the dark but in your opinion do you think it would be easier if I included merged all the data from the four tables into one and where the data is the same type but just different rename it's field. For example if I had a field named Code in all the tables but the data in them were different but I decided to add the Code data from those other tables into one I would make it CodeA, CodeB, CodeC, etc. Would it be easier then to have the textbox pull that specific data based on the combobox selections?

 

[allanon]

Hmmmm, without seeing exactly how you are doing this I am having a tough time visuallizing your situation. If you are creating a query to read tables and you are not sure which table until selections have been made then here is what I would do.

You can have as many columns as you like in a combo box. I would have a column with a width of zero and in that colum I would store the table name that entry is associated with. Then the query would use that column for the table name.

The option that you mentioned of combining all of the tables would work. You may be able to write a query that would diplay the info as if the tables were combined and then just do a select on the query.

 

[JimboSHU]

<quote>You can have as many columns as you like in a combo box. I would have a column with a width of zero and in that colum I would store the table name that entry is associated with. Then the query would use that column for the table name</quote>

That sounds like a good idea. What I need to know is how I would form that query or code that would set those textboxes recordset to the table of choice when that column is selected. I'm a beginner so please bear with me but so far you have been very helpful..