selecting from combo box to fill list box

[buddyr]

Hello,
first problem I am having creating this database is:
Can I create a combo box to select different tables to display in list box?
3 tables
tblGuitar, tblPercussion, tblKeyboards

I want to be able to select Guitar, Percussion, or Keyboard in combo box.
(combo box called cmbOne).
And then in ListBox ( called listOne) have each table display depending on choice selected in combo box.
Thank you,

[DonRayner]

You could use the "After Update" event for your combobox to set the rowsource for your listbox. Here is an example of how you could do it.

Public Sub cmbOne_AfterUpdate()
Dim stSQL as string
Select Case Me.cmbOne
Case is = "Guitar"
    stSQL = "Select tblGuitar.* From tblGuitar;"
Case is = "Percussion"
    stSQL = "Select tblPercussion.* From tblPercussion;"
Case is = "Keyboard"
    stSQL = "Select tblKeyboards.* From tblKeyboards;"
Case Else
    stSQL = ""
End Select
Me.listOne.RowSource = stSQL
End Sub

[NeoPa]

If cmbOne had the table names in as the bound column (either by having that as a single column or as a separate bound column) then you could set the RecordSource for the ListBox to something like :

SELECT *
FROM [Forms]![FormName].[cmbOne]

You would probably still need the Requery after any selection though (Name of ListBox assumed to be lstTwo) :

Public Sub cmbOne_AfterUpdate()
  Call Me.lstTwo.ReQuery
End Sub

[FishVal]

Hello, buddyr.

Why do you have three different tables. Wouldn't it be more suitable to have only one?

In a case the answer is "because though they share some fields the rest are very different" I could propose you a solution which, IMHO, is much more suitable and more relevant in terms of normalization rules.

Regards,
Fish

[buddyr]

thank you Don and NeoPa

I will have to read about normilization to understand
Thank you

[NeoPa]

I would always recommend that Buddy. It is such a useful thing to understand.