Update Another Table With Combo Box Selection

[Learn2010]

I have a form with a subform, i.e., frmMain and frmMainSub. frmMainSub is set to continuous forms. When I enter the ComboBox ExpenseType I set a field in that record named Edit to = ”X”. What I would like to do is update a field in another table, tblExpenseType.ExpenseType, with the selection I just made in that record. I can get rid of the “X” a step later.

How can I do this? If I don’t need the “X” I can get rid of it.

Thank you.

 

[jzwp22]

On what table is the combo box based? I assume that it is tblExpenseType. It sounds like tblExpenseType is just a lookup table, so why would you need to update a record in that table?

Could you please explain a little further? I assume that the main and subforms are each based on separate tables, so with tblExpenseType you are dealing with 3 tables. Could you provide more details on the fields in these tables and how they are related?

 

[Learn2010]

All of your guesses are correct. On the subform, ExpenseType is based on a table with a list. ExpenseItem is based on a query. I have a table of expense items that also contain the expense types. The query is based on that third table that is joined ExpenseType to ExpenseType.

What I am trying to get to happen is to for the third table to be populated with the combo box selection. That way, the query behind ExpenseItem will only display the items listed with that expense type.

I hope that makes sense.

 

[jzwp22]

OK, I'm even more confused. Could you please provide a list of your table/field names and how they are related? Something like the following perhaps?

tblExpenseTypes
-pkExpenseTypeID primary key,autonumber
-txtExpenseType

tblExpenseItems
-pkExpenseItemID primary key, autonumber
-fkExpenseTypeID foreign key to tblExpenseTypes


You could also post a screen shot of your relationship diagram.

 

[Learn2010]

I have a form with a subform, i.e., frmMain and frmMainSub. frmMainSub is set to continuous forms. When I enter the ComboBox ExpenseType I set a field in that record named Edit to = ”X”. What I would like to do is update a field in another table, tblExpenseType.ExpenseType, with the selection I just made in that record. I can get rid of the “X” a step later.



TBLEXPENSETYPES
ExpenseType
..Day Rate
..Per Diem
..Travel
..Miscellaneous


TBLEXPENSEITEMS
ExpenseType ExpenseItem
..DayRate ..Standard
..Per Diem ..Standard
..Travel ..Airfare
..Travel ..Mileage
..Miscellaneous ..Supplies
..Miscellaneous ..Speaker Fees


TBLEXPENSETYPE
ExpenseType



qryViewExpenseItems is tblExpenseType.ExpenseType linked to tblExpenseItems.ExpenseType


The table behind the ComboBox ExpenseType is TBLEXPENSETYPES.

The query behind the ComboBox ExpenseItem is qryViewExpenseItems.

When an expense type is selected in ComboBox ExpenseType, the selection needs to update TBLEXPENSETYPE.ExpenseType. The query behind ComboBox ExpenseItem now will only display the items in that expense type. For instance, if Day Rate is selected, only Standard would show up. If Miscellaneous were selected, Supplies and Speaker Fees would show up.

I hope this helps. I couldn’t find any other way to do it.

 

[jzwp22]

For the ExpenseItem combo box you only need it to be based on tblExpenseItems

What you are describing is not updating (as in updating an existing value in a field in a table) but rather filtering of the second combo box based on the selection made in the first combo box. This technique is called cascading combo boxes. Roger Carlson has a couple demo databases on his site.

Since you are doing this in a subform (continuous) it is a little more complicated. On Roger's site you want the intermediate cascading combo box example

Of special note, you will have to have fields in the subform's underlying record source for both the expense type and expense item even though having both goes against normalization rules.