How to save cascading combo box value

[mar_t]

I have 2 cascading combo box (code below), selected values in my 2 combo box are saved in my tblMain under the following fields namely: [Product] and [Description]. Now, I want to add a ProductID (Number) field in my lookup table (tblChoice) and save that value in ProductID field in tblMain. Please help.

Private Sub cboProduct_AfterUpdate()
 Dim strSource     As String
      
  strSource = "SELECT Description " & _
"FROM tblChoice " & _          
"WHERE Product = '" & Me.cboProduct & "' ORDER BY Description"
  Me.cboDesc.RowSource = strSource
  Me.cboDesc = vbNullString
      
   End Sub

[NTC]

it is a common concept that a business has a list or look up table; i.e. products table and another is perhaps the customer table for repeat customers. these change and are updated occasionally. but the real action is the 'transaction' table - perhaps called a Sales table.

in the Sales table one selects a Product, and one selects a Customer. there are 2 ways to approach this.

one way is in defining the fields in the Sales table - by making them to be look up fields that point to their respective list table. the advantage of this approach is if you need a continuous form format for your sales main form. there are disadvantages for professional programmers and some confusing factors if you don't understand/control what is being bound during first set up.

another way, which you seem to be going toward, is to use Combo/List box to display the look up info. In this case you then need to write the selected data into the fields of the Sales table. So in the 'AfterUpdate' event of the combobox you then put the vba code to write the values to the transaction table fields in the main form. the main form can not be continuous form.

in this second approach, assuming your main form contains the Sales table fields you can rely on simple write code in the AfterUpdate event like:
me.CustomerNameField= me.Column(1)

in your post you are showing a Select query being called in the AfterUpdate. I'm not sure why.

hope this helps you.

[mar_t]

it works! , thank you.