combo box with conditional data sources

[ralphyehle]

At the music school students study violin, viola or cello. Based on their instrument they have three different piece lists.

I would like to create a combo box to display the pieces. I can do that with a separate box for each instrument's piece list.

What I need help with is how to create a single combo box that will select the data sources dependent on the value in a text box that identifies which instrument the student studies.

If txtInstrument = "Cello"
Then cboPieces combo box data source is tblCelloPieces.PieceName,
If txtInstrument = "Violin"
Then cboPieces combo box data source is tblViolinPieces.PieceName,
If txtInstrument = "Viola"
Then cboPieces combo box data source is tblViolaPieces.PieceName

Then I will only need one form rather than one for each instrument.

 

[jzwp22]

You are referring to cascading combo boxes. Here is a link to a site that has some excellent demo databases just for that purpose: http://www.utterangel.com/utterangel.aspx

 

[boblarson]

You are referring to cascading combo boxes. Here is a link to a site that has some excellent demo databases just for that purpose: http://www.utterangel.com/utterangel.aspx

Unfortunately I don't think they are. It looks to me that this database is not normalized (you should not have separate tables (tblViolinPieces,tblViolaPieces, etc.) for the data.

1. Read about normalization here:
http://support.microsoft.com/kb/283878

2. If you get your database normalized, pulling data or assigning it to controls is not going to be a big deal at all, because you can just limit things via queries.

 

[jzwp22]

After Bob's response, yes I agree you have a normalization issue; sorry I missed that (and thanks Bob). That needs to be fixed before you go further. So, please read up on normalization as Bob suggests.

The basic structure I have below should get you started in the right direction if I interpreted your application corrrectly.

You will need a for the instruments and a table for the pieces

tblInstruments (each instrument would be 1 record in this table)
pkInstrumentID primary key, autonumber
txtInstrumentName

tblPieces (each piece would be 1 record in this table)
pkPiecesID primary key, autonumber
txtPieceName

You will also need to link the instrument with its associated pieces, this table should do the trick

tblInstrumentPieces
pkInstrPiecesID primary key, autonumber
fkInstrumentID foreign key-->tblInstrument
fkPiecesID foreing key-->tblPieces

 

[ralphyehle]

thank you to both of you for help.
Turned my head around how this works.
And got it to work! Yah

 

[boblarson]