Listbox execute SQL error

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi groupies

My form has 2 list boxes. The left box lists items in a truck the right box
lists items on a rig site. My code for moving from the truck to the rig is
fine but it is not working properly when moving items from the rig to the
truck.

I use the following code to move items from the rig to the truck:

Private Sub cmdRemoveOneSite_Click()
Dim strSql As String
Dim strWhere As String
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
10 Set db = CurrentDb()

20 If Me.lstOnSite.ItemsSelected.Count = 0 Then
30 DoCmd.CancelEvent
40 Else

50 Set rst = db.OpenRecordset("Select * from tblTruckItems where
lngTruckID=-1")
60 For Each varItem In Me.lstOnSite.ItemsSelected
70 With rst
80 .AddNew
90 .Fields("lngTruckID") = Me.lngTruckID
100 .Fields("StrSKUNumber") = Me.lstOnSite.ItemData(varItem)
110 .Fields("dtmTransactionDate") = Me.txtDate
120 .Update
130 End With
140 Next varItem
150 rst.Close

160 For Each varItem In Me.lstOnSite.ItemsSelected
170 strWhere = strWhere & "[SKU Number]='" &
Me.lstOnSite.ItemData(varItem) & "' Or "
180 Next varItem
190 strWhere = Left(strWhere, Len(strWhere) - 4)
200 strSql = "UPDATE tblWorkOrderDetails SET dtmDateIn = me.txtDate Where
[Work Order ID]=" & Me.txtItemWO & " AND (" & strWhere & ");"

210 db.Execute strSql
220 Set db = CurrentDb
230 Set rst = Nothing
240 Set db = Nothing
250 Me.lstOnSite.Requery
260 Me.lstTruckInventory.Requery
270 End If

280 lstTruckInventory.Requery
290 lstOnSite.Requery
End Sub

It seems to be working until it reads line 200 and tries to run 210. The
code breaks and I get a parameter error stating "Too few parameters.
Expecting 1." I think the problem is with the date but I can not seem to fix
it. The textbox being referred to, txtDate, works for adding the data to the
truck but .........

Any ideas?
 
Jet knows nothing about controls on forms. The reference to txtDate needs to
be outside of the quotes.

strSql = "UPDATE tblWorkOrderDetails " & _
"SET dtmDateIn = " & Format(me.txtDate, "\#yyyy\-mm\-dd\#") & _
"WHERE [Work Order ID]=" & Me.txtItemWO & " AND (" & strWhere & ");"
 
You make it seem so easy.......

Thank you very much Doug!

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Douglas J. Steele said:
Jet knows nothing about controls on forms. The reference to txtDate needs
to be outside of the quotes.

strSql = "UPDATE tblWorkOrderDetails " & _
"SET dtmDateIn = " & Format(me.txtDate, "\#yyyy\-mm\-dd\#") & _
"WHERE [Work Order ID]=" & Me.txtItemWO & " AND (" & strWhere & ");"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


CJ said:
Hi groupies

My form has 2 list boxes. The left box lists items in a truck the right
box lists items on a rig site. My code for moving from the truck to the
rig is fine but it is not working properly when moving items from the rig
to the truck.

I use the following code to move items from the rig to the truck:

Private Sub cmdRemoveOneSite_Click()
Dim strSql As String
Dim strWhere As String
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
10 Set db = CurrentDb()

20 If Me.lstOnSite.ItemsSelected.Count = 0 Then
30 DoCmd.CancelEvent
40 Else

50 Set rst = db.OpenRecordset("Select * from tblTruckItems where
lngTruckID=-1")
60 For Each varItem In Me.lstOnSite.ItemsSelected
70 With rst
80 .AddNew
90 .Fields("lngTruckID") = Me.lngTruckID
100 .Fields("StrSKUNumber") = Me.lstOnSite.ItemData(varItem)
110 .Fields("dtmTransactionDate") = Me.txtDate
120 .Update
130 End With
140 Next varItem
150 rst.Close

160 For Each varItem In Me.lstOnSite.ItemsSelected
170 strWhere = strWhere & "[SKU Number]='" &
Me.lstOnSite.ItemData(varItem) & "' Or "
180 Next varItem
190 strWhere = Left(strWhere, Len(strWhere) - 4)
200 strSql = "UPDATE tblWorkOrderDetails SET dtmDateIn = me.txtDate
Where [Work Order ID]=" & Me.txtItemWO & " AND (" & strWhere & ");"

210 db.Execute strSql
220 Set db = CurrentDb
230 Set rst = Nothing
240 Set db = Nothing
250 Me.lstOnSite.Requery
260 Me.lstTruckInventory.Requery
270 End If

280 lstTruckInventory.Requery
290 lstOnSite.Requery
End Sub

It seems to be working until it reads line 200 and tries to run 210. The
code breaks and I get a parameter error stating "Too few parameters.
Expecting 1." I think the problem is with the date but I can not seem to
fix it. The textbox being referred to, txtDate, works for adding the data
to the truck but .........

Any ideas?
Click to expand...
Click to expand...
 
Back
Top