Hello. I'm struggling to figure out what I am missing as it relates to a SQL statement I am trying to execute through VBA. Quick description: I have an Access Form that is capturing items in a Listbox. I am trying to loop through the items in the listbox, and for each run an update statement against an Access table to set a flag to TRUE. Problem is when I pull the value from the listbox into a variable I've defined (as LONG), I receive Too Few Parameters Expected 1. However, if I remove the variable from the SQL statement and hard code the value in, the statement executes without issue. That tells me that the statement is valid, but the value is not passing from the variable to the sql statement. I am sure there may also be a better more efficient way of accomplishing setting this flag, but this is the method I've chose thus far. I also tried to put a test textbox in place with a value, and then convert that value to ensure it wasn't a data type issue but still no luck. Thoughts?
Block of code:
Dim n As Integer
Dim varPurOrdDetailID As Long
Set MyDB = CurrentDb()
'Update All Line Items To be Marked for Invoiced
For n = 0 To Me.ListLineItemIDToInvoice.ListCount - 1
varPurOrdDetailID = Me.ListLineItemIDToInvoice.ItemData(n)
MyDB.Execute "Update PurchaseOrderDetail Set LineItemInvoiced = True WHERE PurchaseOrderDetail.ID In (varPurOrdDetailID);"
'MyDB.Execute "Update PurchaseOrderDetail Set LineItemInvoiced = True WHERE PurchaseOrderDetail.ID = varPurOrdDetailID;"
Next n
MyDB.Execute "Update PurchaseOrderDetail Set LineItemInvoiced = True WHERE PurchaseOrderDetail.ID = 306;" -->> this works without issue if value is hard coded