Help creating queries from combobox input

[argsemapp]

Access newbie here. I have a form with two tables for Record Sources [tbl_Composition] & [tbl_ProductRelationships], and a combobox (cmb_SelProdName) whose Row Source is a ProductName field from [tbl_ProductRelationships].

When a user selects a choice from the combobox I'd like to query [tbl_Composition] for the ProductName (they should match) and get the entire row for that product which contains information I need to feed to a report. Here's my vba for the AfterUpdate on the combobox:

Private Sub cmb_SelProdName_AfterUpdate()


Dim db As Database
Dim rec As DAO.Recordset
Dim RecQry As String


Set db = CurrentDb
RecQry = "SELECT * FROM [tbl_composition] WHERE [tbl_composition].ALLOY_DESC = " & Me.cmb_SelProdName


Set rec = db.OpenRecordset(RecQry)


End Sub

This is giving me a Variable Not Defined error when I make a selection on my combobox, and I'm a bit stuck. I'm asking myself, what does "Set db=CurrentDb" do when the form has two dependencies? Any help would be greatly appreciated.

[ItsMe]

Is tbl_composition in the current DB or is it somewhere else?

Maybe the value of the combobox is not the correct type. Are there multiple columns in the combo? Are you trying to match a PK like a long integer or a text data type with your WHERE clause?

[Bob Fitz]

If "Me.cmb_SelProdName" holds a text value rather than a number, you may need:

RecQry = "SELECT * FROM [tbl_composition] WHERE [tbl_composition].ALLOY_DESC = '" & Me.cmb_SelProdName & "'"

What are you going to do with the recordset "rec".

[argsemapp]

tbl_composition should be- it's in the Record Source of the form (SELECT tbl_composition.*, tbl_ProductRelationships.* FROM tbl_composition, tbl_ProductRelationships.

There's just a single column in my combo. The combobox value and the associated value on the other table are both Text, and as far as I can tell they are character for character...

However I did some cleaning up and I'm getting "Run-time Error '3464': Data type mismatch in criteria expression. Everything should be strings & be playing nicely together...

[ItsMe]

Are you using the code example in Post # 3? CurrentDB should be fine since the table is in the Current DB. Although, if you are going to declare db and Bind it you should probably use
Dim db As DAO.Database

[argsemapp]

The quotations, and calling db a DAO.Database seemed to fix the problem! (or at least stop any errors).

If I wanted to bind the first couple contents of this new recordset to a textbox on my form what would be the easiest way to do this?

[Dal Jeanis]

By Bind, do you mean you want to display it, or do you mean you want the user to be able to update it?

If you want to display it, then you just need to check if there are any records, then read the first record and assign the value to the unbound text box.That looks roughly like this, although it's aircode.

rec.movelast 
rec.movefirst 
IF rec.recordcount>0 then 
'  txtMyField = rec.Fields("MyField").value
'       maybe the above format 
'       or maybe the below format ->
   txtMyField = rec!MyField
Else
   txtmyField = ""
End if