Cascading Combo and on NotInList value

[bennybee]

hi.

i have a main table which has the following fields:

Subject:
Liquor
Tobacco
Kava

Other_Agenda_Item.
Liquor_Other
Tobaccther
Kava_Other

The subject is a table with those values in it, and i have 3 tables set up for Liquor_Other, Tobaccther etc, where the user has the different Agenda items for those items.



now i have up the cascading combo box so that when a subject is selected, it will change the row source of the Other_Agenda_Item field, so that it only displays the corresponding other agenda items.
e.g. if liquor is selected in the subject field, in the Other_Agenda_Item field, it will show
- Liquor Act.
- Alcoholism
...

now, what i want to do is be able to set it up so that i can add a new agenda item into the specific table.
so if there is a new agenda item for liquor, i can select liquor from the subject field, then in the Other_Agenda_Item field, it will display the current values.
when i type in "Problem Drinking" for example, it will execute a On NotInList event procedure.

I know how to do these, but i cant get it to only add the new agenda item to the specific table. so it will only add "problem drinking" to the liquor_other table.

can i use the following segment of code.

Set Db = CurrentDb
Set Rs = Db.OpenRecordset(CurrentTable, dbOpenDynaset)

and use CurrentTable as a parameter for the OpenRecordset value?
or is the a way to set it to the current table (ie. the table which the is being displayed in the Other_Agenda_Item field, rather than having to specify directly which table it is associated with.)

the idea is that it will add the agenda item to the specific table that is selected based on the Subject.

any help??

 

[Pat Hartman]

You should not use three tables to hold a single set of data. Combine the three tables into one and add a code that identifies which subject each item relates to. You can then use cascading combos to get the appropriate list. That should also solve your add new row issue.