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
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