Can anyone tell me the problem with this code

[Guest]

When I try to select any objects in the list box, I get this error message:

Unable to Update
Error 3061: Too few parameters. Expected 1

This is in the After Update of the list box.

Public Sub List31_AfterUpdate()
On Error GoTo Err_List31_AfterUpdate

Dim db As Database
Dim ws As Workspace
Dim strSQL As String
Dim blnInTransaction As Boolean
Dim varItem As Variant
Dim strtable As String
Dim strvar As String

' Make sure the current member record has been saved.
If Me.Dirty Then Me.Dirty = False

Set ws = Workspaces(0)
Set db = ws.Databases(0)

ws.BeginTrans
blnInTransaction = True

strtable = "itblPRODUCT_ANSWERS"
strvar = "txtproduct_no"

' Add each hobby selected in the list box.
With Me.List31
For Each varItem In .ItemsSelected
'change this
strSQL = _
"INSERT INTO " & strtable & " (txtEVENT_ID, " & strvar & ")
VALUES (" & _
Me.autEVENTID & ", " & .ItemData(varItem) & ")"

db.Execute strSQL, dbFailOnError
Next varItem
End With

ws.CommitTrans
blnInTransaction = False

Exit_List31_AfterUpdate:
Set db = Nothing
Set ws = Nothing
Exit Sub

Err_List31_AfterUpdate:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, _
"Unable to Update"
If blnInTransaction Then
ws.Rollback
blnInTransaction = False
End If
Resume Exit_List31_AfterUpdate

End Sub

 

[Anne]

Hey Biggles -

That message means that the query is using a field name
that is not in the table. Does
table "itblPRODUCT_ANSWERS" have fields
called "txtEVENT_ID" and "txtproduct_no"? that's what the
query is expecting, but those look more like form control
names than field names to me.

The best way I've found to debug this kind of problem is
to put a breakpoint in your code just after you set
strSQL. Then run the code, it will stop at that line.
When the code stops, go to the immediate window (ctrl-G)
and type ?strSQL then hit the enter key. This will
display the value of strSQL in the db window. Now copy
that and paste it into a query in SQL view and run the
query, it often give you a better error message and you
can play around with the query until you get it right and
then transfer the changes back to the code.

Hope that helps - anne

-----Original Message-----
When I try to select any objects in the list box, I get this error message:

Unable to Update
Error 3061: Too few parameters. Expected 1

This is in the After Update of the list box.

Public Sub List31_AfterUpdate()
On Error GoTo Err_List31_AfterUpdate

Dim db As Database
Dim ws As Workspace
Dim strSQL As String
Dim blnInTransaction As Boolean
Dim varItem As Variant
Dim strtable As String
Dim strvar As String

' Make sure the current member record has been saved.
If Me.Dirty Then Me.Dirty = False

Set ws = Workspaces(0)
Set db = ws.Databases(0)

ws.BeginTrans
blnInTransaction = True

strtable = "itblPRODUCT_ANSWERS"
strvar = "txtproduct_no"

' Add each hobby selected in the list box.
With Me.List31
For Each varItem In .ItemsSelected
'change this
strSQL = _
"INSERT INTO " & strtable & " (txtEVENT_ID, " & strvar & ")
VALUES (" & _
Me.autEVENTID & ", " & .ItemData (varItem) & ")"

db.Execute strSQL, dbFailOnError
Next varItem
End With

ws.CommitTrans
blnInTransaction = False

Exit_List31_AfterUpdate:
Set db = Nothing
Set ws = Nothing
Exit Sub

Err_List31_AfterUpdate:
MsgBox "Error " & Err.Number & ": " &

Err.Description, vbExclamation, _

 

[Dirk Goldgar]

When I try to select any objects in the list box, I get this error message:

Unable to Update
Error 3061: Too few parameters. Expected 1

This is in the After Update of the list box.

Public Sub List31_AfterUpdate()
On Error GoTo Err_List31_AfterUpdate

Dim db As Database
Dim ws As Workspace
Dim strSQL As String
Dim blnInTransaction As Boolean
Dim varItem As Variant
Dim strtable As String
Dim strvar As String

' Make sure the current member record has been saved.
If Me.Dirty Then Me.Dirty = False

Set ws = Workspaces(0)
Set db = ws.Databases(0)

ws.BeginTrans
blnInTransaction = True

strtable = "itblPRODUCT_ANSWERS"
strvar = "txtproduct_no"

' Add each hobby selected in the list box.
With Me.List31
For Each varItem In .ItemsSelected
'change this
strSQL = _
"INSERT INTO " & strtable & " (txtEVENT_ID, " & strvar & ")
VALUES (" & _
Me.autEVENTID & ", " & .ItemData(varItem) & ")"

db.Execute strSQL, dbFailOnError
Next varItem
End With

ws.CommitTrans
blnInTransaction = False

Exit_List31_AfterUpdate:
Set db = Nothing
Set ws = Nothing
Exit Sub

Err_List31_AfterUpdate:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, _
"Unable to Update"
If blnInTransaction Then
ws.Rollback
blnInTransaction = False
End If
Resume Exit_List31_AfterUpdate

End Sub

The most likely cause would be a misspelling of one of the field names in
the target table. Another possibility is that you are not wrapping a text
value in quotes (within the constructed SQL string), or that you are
wrapping a non-text value in quotes. Is txtEVENT_ID a text field, as its
name suggests? You don't appear to be putting quotes into the SQL string
around Me.autEVENTID. That could be it.

 

[Sandra Daigle]

I agree with Anne about the best way to debug SQL Strings. This is a good
practice.

Your specific problem is probably due to text data that is not wrapped in
quotes when it gets to Jet (the database engine). Try wrapping the value in
quotes. I prefer to use pairs of double quote characters ("). In the
following statement you'll see a two series of 3 double quote characters. In
each series one of these is used as the VBA string delimiter. The remaining
pair is converted to a single character which remains in the SQL string.

strSQL = _
"INSERT INTO " & strtable & " (txtEVENT_ID, " & strvar &
") VALUES (" & _
Me.autEVENTID & ", """ & .ItemData(varItem) & """)"