Combobox selecting wrong value

[lrw0831]

On my main form I have a Product drop down. Once a product is select, the Module combo box is to be popluated with modules related to that product.

The combo box populates correctly, but if I click on any module, it automatically selects the first item in the box.

Example:

My Product is CA, the modules are Assessment, Capacity, Implementation, Planning, Evaluation, REports, Other.

If I select CA, then under module I select Implementation, the module combobox popluates with "assessment"

Here is my code for the Product on change event

Private Sub Product_Change()
Dim t As String
 
    t = Me.Product.Value
 
    cboModule.SetFocus
    cboModule.RowSource = "Select [product id], Module FROM Module where [product id] = " & t & ";"
    Product.SetFocus
 
End Sub

Can anyone help me with this problem?

Thank you

[NeoPa]

You need to surround t with quotes (Line #7) :

"... = '" & t & "';"

[NeoPa]

Unless I miss my guess, you also want to consider using the AfterUpdate event rather than the Change event.

[lrw0831]

Adding the quotes did not work. It caused it to not populate the module combobox.

[NeoPa]

What's the value of t when this happens? It seems that something you're saying is not quite accurate.

Add a "MsgBox t" line before you set up the SQL and post in here the value displayed please.

[lrw0831]

I am new to Access and I'm not sure how to do this. Could you tell me what to add to the code?

[NeoPa]

Of course. No problem.

Private Sub Product_Change()
Dim t As String
 
    t = Me.Product.Value
    MsgBox t
 
    cboModule.SetFocus
    cboModule.RowSource = "Select [product id], Module FROM Module where [product id] = " & t & ";"
    Product.SetFocus
 
End Sub

The line I added is found at #5.

To get to where the code is go into Design mode of the form, Select the [Product] control, Hit Alt-Enter to view the proerties, Find the On Change property, Click on the elipsis button to the right of it when it is selected.

Now you are in the VBA Editor window. Code can be manipulated from here. Paste in this version over the top of your earlier version.

[lrw0831]

Thank you for your help. I was able to get this to work.

[NeoPa]

Very pleased to hear it. Well done :)