We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

CurrentDb.Execute syntax error - can't figure out where I am going wrong!

Hobart007
Hobart007 asked
on
Medium Priority
316 Views
Last Modified: 2012-06-22
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.
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 Sub

Open in new window

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Try thisL

CurrentDb.Execute "Update ROSTER set MBR_ROLE=" & Me.ROLEUPDATE & " Where MBR_NAME=" & Chr(34) & Me.Combo23 & Chr(34)

mx

Author

Commented:
Just dropped your code in and get the same error except not it is just 1 expected parameter and not 2.

Getting closer!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:

Try this:

CurrentDb.Execute "Update ROSTER set MBR_ROLE=#" & Me.ROLEUPDATE & "# Where MBR_NAME=" & Chr(34) & Me.Combo23 & Chr(34)

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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)

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

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

Author

Commented:
Uploading it now.  This is killing me.  The form in question is EDIT MEMBER
CRITERION-LOOT.accdb
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:

Author

Commented:
Awesome!  Thank you.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
You are welcome.
Sorry ... I SHOULD have seen that from the git go ...

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.