UPDATE not working

[ailyn]

I have a form Orders(tblOrders) with a subform OrderdetailstblOrderDetails). In the subform we can fin ProductId and BlowerId that come from tblProducts and tblBlowers. This way we can make one order and order several products. the thing is when I click on a button for saving the order and the order details, I need to mark the corresponding productIds and BlowerIds from the subform as sold (a yes/no field in the tblProducts or tblBlowers).
I was trying this:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'set values here!
'loop?
Dim stAvai As String
Dim stAvailabl As String
Dim strSetProd As String
Dim strSetBlow As String
stAvai = "='" & Me.OrderDSubform.Form!ProductIdctrl.Value & "'"
stAvailabl = "='" & Me.OrderDSubform.Form!BlowerIdctrl.Value & "'"
strSetProd = "UPDATE Products " & _
"SET Products.[Avail]= True " & _
"WHERE Products.[ProductId]" & stAvai & " ;"
'DoEvents?
DoCmd.RunSQL strSetProd
strSetBlow = "UPDATE Blowers " & _
"SET Blowers.[Available] = True " & _
"WHERE Blowers.[BlowerId]" & stAvailabl & " ;"
'DoEvents?
DoCmd.RunSQL strSetBlow
MsgBox "Record saved. Update the archives", vbInformation

I'm not sure if I need some kind of loop for when we have more than one orderdetail (= several products) for the same order. but this code already doesn't work, some kind of conflict on the criteria

 

[KenReay]

Hi

To begin

if BlowerId and / or ProdctId are numeric types you do not need the ' in the SQL string so

stAvai = "= " & Me.OrderDSubform.Form!ProductIdctrl.Value & " "
stAvailabl = "= " & Me.OrderDSubform.Form!BlowerIdctrl.Value & " "


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ailyn]

Thanks KenReay, the first problem is solved. now the appropriate field gets marked but only for the first orderdetail (first productId and first blowerId).How do I do it to mark them all?