set of data available depends on category selected

[sponge]

Hi,

In a form, I am using two comboboxes per record (i.e. when you add a new record, two more comboboxes for that record appear).

My goal is to have the second combobox show entries specific to what the first combobox was, with certain caveats (see below).

My current approach is using three tables - tblTypes, tblOptions, and tblEntries. tblTypes would store the different types of entries. Each of the types in tblTypes would contain options depending on its ID.

Basically, the first combobox will list the data in tblEntries and the second combobox will list the data in tblOptions, depending on what "Entry" was selected. New "Entry's" for the first combobox would be created in tblEntries, where the ID in this table would indicate what "Options" are available for that entry. As shown in the sample table below, I want to allow more than one entry with the same "ID" (i.e. there could be more than one entry with the same options, but the entry name would be different.)

A sample of what the tables would look like is below:

tblTypes
---------
ID |Type
1 TypeA
2 TypeB
3 TypeC

tblOptions
----------
ID | Option
1 Data1
1 Data2
1 Data3
2 Data1
3 Data4
3 Data5

tblEntries
----------
ID | Entry
1 Entry1
2 Entry2
2 Entry3
3 Entry4

Is this approach sound? If so, how would I create the comboboxes to use with this set of data? I tried doing so but ran into a certain problem - that is, the first combobox doesn't allow you to "select" entries with the same ID - it lets you select the first one, but no other... (using a query that simply selects all of the entries and its associated ID - I used the ID to populate the second textbox appropriately.

If this approach sounds unreasonable, how would you approach it? And after creating the tables, how would you go about setting up the combo boxes?

Any help would be much appreciated.

Thanks in advance.

 

[The_Doc_Man]

Search this forum for the topic "Cascading Combo Boxes" for a series of posts detailing the process you described.

 

[ejstefl]

A good tutorial: http://www.fontstuff.com/access/acctut10.htm

 

[sponge]

Hi,

I tried creating comboboxes already but ran into a certain problem - that is, the first combobox doesn't allow you to "select" entries with the same ID - it lets you select the first one, but no other (yet it'll still show the other item with the same id in the combobox)... This was using a query that simply selects all of the entries and its associated ID - I used the ID to populate the second textbox appropriately.