[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-22
13
Medium Priority
?
300 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

0
Comment
Question by:Hobart007
  • 8
  • 5
13 Comments
 
LVL 75
ID: 35450268
Try thisL

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

mx
0
 

Author Comment

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

Getting closer!
0
 
LVL 75
ID: 35450301

Try this:

CurrentDb.Execute "Update ROSTER set MBR_ROLE=#" & Me.ROLEUPDATE & "# Where MBR_NAME=" & Chr(34) & Me.Combo23 & Chr(34)
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Hobart007
ID: 35450328
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.
0
 
LVL 75
ID: 35450352
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)
0
 

Author Comment

by:Hobart007
ID: 35450385
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.
0
 
LVL 75
ID: 35450412
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
0
 

Author Comment

by:Hobart007
ID: 35450456
Uploading it now.  This is killing me.  The form in question is EDIT MEMBER
CRITERION-LOOT.accdb
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 35450519
Here you go:

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


mx
0
 
LVL 75
ID: 35450529
0
 

Author Closing Comment

by:Hobart007
ID: 35450532
Awesome!  Thank you.
0
 
LVL 75
ID: 35450546
You are welcome.
Sorry ... I SHOULD have seen that from the git go ...

mx
0
 
LVL 75
ID: 35450553
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
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question