Run time error 3061 Too few parameters expected 4

0
votes
CurrentDb.Execute "UPDATE Customer set [FirstName] = " & Me.FirstName & _
                                    " ,[LastName] = " & Me.LastName & _
                                    " ,[PhoneNumber] = '" & Me.PhoneNumber & _
                                    "' ,[Address] = '" & Me.Address & _
                                    "' ,[City] = " & Me.City & _
                                    " ,[State] = " & Me.State & _
                                    " ,[ZipCode] = " & Me.ZipCode & " WHERE ([E-mail] = '" & Me.email & "')"

what is wrong with this update query. Please help.

2
sqlms-accessvba
From the looks of the error, the table you are trying to update does not contain those column names (or at least 4 of them). Post the info for your table for additional assistance. – Jiggles32

2 Answers

2
votes

Once again, consider SQL parameterization, an industry best practice when working with SQL, which is supported in MS Access using PARAMETERS clause and QueryDefs. You avoid messy concatenation, quote enclosures (which you are missing quite a bit for your text fields), and possibly SQL injection as such fields are open to users.

SQL (save as a stored Access query)

PARAMETERS [FirstNameParam] Text(255), [LastNameParam] Text(255),
           [PhoneNumberParam] Text(255), [AddressParam] Text(255),
           [CityParam] Text(255), [StateParam] Text(255),
           [ZipCodeParam] Text(255), [EmailParam] Text(255);
UPDATE Customer 
   SET [FirstName] = [FirstNameParam],
       [LastName] = [LastNameParam],
       [PhoneNumber] = [PhoneNumberParam],
       [Address] = [AddressParam],
       [City] = [CityParam],
       [State] = [StateParam],
       [ZipCode] = [ZipCodeParam]
WHERE ([E-mail] = [EmailParam]);

VBA

Dim qdef As QueryDef

Set qdef = CurrentDb.QueryDefs("mySavedParameterQuery")

' BIND VALUES TO PARAMETER PLACEHOLDERS
qdef![FirstNameParam] = Me.FirstName
qdef![LastNameParam] = Me.LastName
qdef![PhoneNumberParam] = Me.PhoneNumber
qdef![AddressParam] = Me.Address
qdef![CityParam] = Me.City
qdef![StateParam] = Me.State
qdef![ZipCodeParam] = Me.ZipCode
qdef![EmailParam] = Me.Email

' EXECUTE ACTION QUERY
qdef.Execute dbFailOnError

Set qdef = Nothing
1
votes

Just a guess from looking at your SQL and the error - I suspect your 4 field names with two words should likely have spaces between the two words: [FirstName] becomes [First Name], [Last Name] becomes [Last Name] and so on

So SQL string should actually be

"UPDATE Customer set [First Name] = " & Me.FirstName & _
                                        " ,[Last Name] = " & Me.LastName & _
                                        " ,[Phone Number] = '" & Me.PhoneNumber & _
                                        "' ,[Address] = '" & Me.Address & _
                                        "' ,[City] = " & Me.City & _
                                        " ,[State] = " & Me.State & _
                                        " ,[Zip Code] = " & Me.ZipCode & " WHERE ([E-mail] = '" & Me.email & "')"

As @Jiggles32 says - the only way to know for sure is to show us your field names from the Customer table