populating a text field from a combo box selection.

[k_c_morrissey]

I have a combo where the list is populated with a bit of VB code to create a two field list. When the user selects the item they want from the list it displays the item in the combo box and should populate a text box in the same row with the value from the second column.

This combo is part of an orders subform and the user is selecting a product from the list which displays the product_name and the product_id.

The problem is that the product_id is not filled in and access returns the following error:
Run-time error ‘3101’:
The Microsoft Jet database cannot find a record in the table ‘product_table’ with key matching field(s) product_id.

This is the VB code that populates the list :

   Private Sub comboCatagory_ID_AfterUpdate()
   Dim sProd_description As String
   sProd_description = "SELECT products_table.product_id, products_table.prod_description " & _
           "FROM products_table " & _
    "WHERE products_table.prod_catagoryID = " & "'" & Me.comboCatagory_ID.Column(0) 
   	 	Me.comboProd_description.RowSource = sProd_description
   	 	Me.comboProd_description.Requery
   End Sub
[end code]

What am I doing wrong?

Thx
     Kev.

 

[Uncle Gizmo]

What if you replace:

Me.comboProd_description.RowSource = sProd_description
with
Me.comboProd_description.RowSource = "(Valid SQL String)"

Where "(Valid SQL String)" is an SQL string that returns a known value from your table.

In other words, Get it working properly with known values, and then replace the hard coded SQL String with your variable. This way you eliminate potential problems and make it much easier to debug your code.

 

[Uncle Gizmo]

something else you may need to check .
How have you got your combo box set up? Could you post the the settings,

column count
column widths

 

[k_c_morrissey]

Attached are 5 files which are screen grabs of the properties of the two combo boxes, the text box and the form. the 5th file is the VB for the two combos.

I am ver much a novice at VB, so I am at a bit of a loss about what I can and can't remove and still make it work. I am trying to get your previous suggestion to work, but keep getting debug errors which I struggle to resolve.

Any help and guidance is gratefully excepted.

Thx
Kev.

 

[Uncle Gizmo]

If you run this SQL as a select query, does it produce the desired results?

"SELECT products_table.product_id, products_table.prod_description " & _
"FROM products_table " & _
"WHERE products_table.prod_catagoryID = " & "'" & Me.comboCatagory_ID.Column(0)

 

[k_c_morrissey]

The suggested code change is the same as the first 3 lines of the comboCatagory_ID_AfterUpdate() sql. If I delete the rest of the SQL and then run it exacly as suggested) I get another debug screen with a 3058 error (see attached screen grab), which I think is telling me that I can not leave the product_id empty as it is a key field in the products table.

The " UNION SELECT 999999,'Add New Product' FROM products_table" & _
" ORDER BY 1" was added to attach on option to add a product not listed. This doesn't seem to cause any extra problems at this stage!