Recordset SQL query issue. 1

[prodtest]

Hi,
I am trying to write a simple peice of code that when actioned from a button opens two recordsets, it then cycles through one of the recordsets and updates a stores bin location from data held in the other recordset.
The code is below, however when run I get a "data mismatch" error appear pointing at the sql query.
I have checked the data types in both tables for the serial number and product code fields and they are both set to text. I dont see where I am going wrong, can some please help ?

Thanks
Ben


Private Sub Command0_Click()

Dim db As Database
Dim inputdb As Recordset
Dim carddb As Recordset

Set db = CurrentDb()

'populate inputdb recordset with contents of input table
Set inputdb = db.OpenRecordset("input")

'move to first record in inputdb recordset
inputdb.MoveFirst

'repeat until end of inputdb recordset
Do While Not inputdb.EOF

'open carddb recordset and populate with results of
'a select query where the product code & serial number
'matches the product code & serial number in the current
'open record in inputdb recordset
Set carddb = db.OpenRecordset("SELECT * FROM card WHERE product_code = " & inputdb![product_code] & " AND serial_number = " & inputdb![serial_number])

'set bin location on record returned by carddb recordset
'to match the
'bin location on the current open record in inputdb
'recordset
carddb![bin_location] = inputdb![bin_location]

'close carddb recordset
carddb.Close

'move to next record in inputdb recordset
inputdb.MoveNext

'repeat
Loop

'close inputdb recordset
inputdb.Close


End Sub

 

[RivetHed]

If both those fields are text you'll need text qualifiers in your where clause.

Set carddb = db.OpenRecordset("SELECT * FROM card WHERE product_code = '" & inputdb![product_code] & "' AND serial_number = '" & inputdb![serial_number]& "';")

If you still get problems try using debug.print to get what text you're actually producing and then pasting it into a query window to see if you can work out what's going wrong.

 

[prodtest]

RivetHed you are a star !!
Thanks for that, now works a treat after the changes and me forgetting to add in the Edit and Update lines.

Thanks again

 

[RivetHed]

No problem.

 

[PWise]

no need to open two recordsets and loop
all you need is

Dim db As Database
Dim inputdb As Recordset
Dim carddb As Recordset
Set db = CurrentDb()
db.execute "UPDATE card  INNER JOIN input ON ([input.product_code] = card.[product_code]) AND (input.serial_number = card.serial_number) SET card.bin_location= [input]![bin_location];"