Hobart007
asked on
CurrentDb.Execute syntax error - can't figure out where I am going wrong!
I am working on a piece of code that updates a table field based on a value in an unbound listbox but am getting an error from line 22.
Error is: Run-time error '3061': Too few parameters. Expected 2.
I am sure that this is just a syntax issue but am not sure where I've gone wrong.
Error is: Run-time error '3061': Too few parameters. Expected 2.
I am sure that this is just a syntax issue but am not sure where I've gone wrong.
Private Sub Command12_Click()
'--- only process Save if there is data
If IsNull(ROLEUPDATE) Then
MsgBox "Please select a role."
ROLEUPDATE.SetFocus
Exit Sub
End If
'--- transfer data from text boxes to table fields
Dim myVar As Byte
myVar = MsgBox("Are you sure?", vbYesNo + vbQuestion)
If myVar = vbNo Then
MsgBox "Edit cancelled."
Exit Sub
End If
CurrentDb.Execute "Update ROSTER set MBR_ROLE=" & Me.ROLEUPDATE & " Where MBR_NAME=" & Me.Combo23
End SubASKER
Just dropped your code in and get the same error except not it is just 1 expected parameter and not 2.
Getting closer!
Getting closer!
Try this:
CurrentDb.Execute "Update ROSTER set MBR_ROLE=#" & Me.ROLEUPDATE & "# Where MBR_NAME=" & Chr(34) & Me.Combo23 & Chr(34)
ASKER
Now the error is as follows:
Run-time error '3075' Syntax error in date in query expression '#ROLE1'.
ROLE1 is the selected item in the listbox. This field on the table to be updated is a text field so I am not sure where this is coming from.
Run-time error '3075' Syntax error in date in query expression '#ROLE1'.
ROLE1 is the selected item in the listbox. This field on the table to be updated is a text field so I am not sure where this is coming from.
Well, the first one should have worked. Not seeing two parameters in the WHERE clause. So, there must be a typo some where
Are you sure that ROLEUPDATE is a control on the Form ?
CurrentDb.Execute "Update ROSTER set MBR_ROLE=" & Me.ROLEUPDATE & " Where MBR_NAME=" & Chr(34) & Me.Combo23 & Chr(34)
Are you sure that ROLEUPDATE is a control on the Form ?
CurrentDb.Execute "Update ROSTER set MBR_ROLE=" & Me.ROLEUPDATE & " Where MBR_NAME=" & Chr(34) & Me.Combo23 & Chr(34)
ASKER
Here are all of the names:
On form:
Listbox with update value: ROLEUPDATE
Combobox with record primary key value: Combo23
Table: ROSTER
Field: MBR_ROLE
PK: MBR_NAME
Just copied and pasted all three pieces of code above into the line and all three return the same errors. I've no clue here.
On form:
Listbox with update value: ROLEUPDATE
Combobox with record primary key value: Combo23
Table: ROSTER
Field: MBR_ROLE
PK: MBR_NAME
Just copied and pasted all three pieces of code above into the line and all three return the same errors. I've no clue here.
Humm ... looks ok.
Guess we can try brackets:
CurrentDb.Execute "Update ROSTER set [MBR_ROLE]=" & Me.ROLEUPDATE & " Where [MBR_NAME]=" & Chr(34) & Me.Combo23 & Chr(34)
If not, Can you upload this db?
mx
Guess we can try brackets:
CurrentDb.Execute "Update ROSTER set [MBR_ROLE]=" & Me.ROLEUPDATE & " Where [MBR_NAME]=" & Chr(34) & Me.Combo23 & Chr(34)
If not, Can you upload this db?
mx
ASKER
Uploading it now. This is killing me. The form in question is EDIT MEMBER
CRITERION-LOOT.accdb
CRITERION-LOOT.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome! Thank you.
You are welcome.
Sorry ... I SHOULD have seen that from the git go ...
mx
Sorry ... I SHOULD have seen that from the git go ...
mx
Also, add this ... to cover exposing any errors that may occur:
CurrentDb.Execute "Update ROSTER set [MBR_ROLE]=" & Chr(34) & Me.ROLEUPDATE & Chr(34) & " Where [MBR_NAME]=" & Chr(34) & Me.Combo23 & Chr(34) , dbFailOnError
CurrentDb.Execute "Update ROSTER set [MBR_ROLE]=" & Chr(34) & Me.ROLEUPDATE & Chr(34) & " Where [MBR_NAME]=" & Chr(34) & Me.Combo23 & Chr(34) , dbFailOnError
CurrentDb.Execute "Update ROSTER set MBR_ROLE=" & Me.ROLEUPDATE & " Where MBR_NAME=" & Chr(34) & Me.Combo23 & Chr(34)
mx