Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

UPDATE not working

Status
Not open for further replies.

ailyn

Programmer
Sep 15, 2005
108
BE
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
 
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 - UK
 
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top