Run Time error 3061 Too Few parameters. Expected 6. Unable to update table from listbox

Question

All,

I am running the below SQL and I keep getting error 3061. Thank you all for the wonderful help! I've been trying to teach myself and I am 10 days in and oh my I am in for a treat!

Private Sub b_Update_Click()
Dim db As DAO.Database
Set db = CurrentDb

strSQL = "UPDATE Main" _
       & " SET t_Name = Me.txt_Name, t_Date = Me.txt_Date, t_ContactID = Me.txt_Contact, t_Score = Me.txt_Score, t_Comments = Me.txt_Comments" _
       & " WHERE RecordID = Me.lbl_RecordID.Caption"
       CurrentDb.Execute strSQL

You're passing in a literal string "Me.txt_Name" (etc etc) instead of the value itself. — Tim Williams, Jan 07 '16 at 23:27
You need to build the actual values into the SQL string, so `strSQL = "UPDATE Main SET t_Name = '" & Me.txt_Name & "', t_Date...` — Tim Williams, Jan 07 '16 at 23:29
Be careful with that approach. Instead use `parameterized queries` -- several examples on SO about it. Here's one: http://stackoverflow.com/questions/16568461/is-it-possible-to-pass-parameters-programmatically-in-a-microsoft-access-update — sgeddes, Jan 07 '16 at 23:37
I'm not sure why no one has mentioned this yet, but I believe you need to declare `strSQL` as a variable. Somewhere above the line that you use `strSQL` ; input the following `dim strSQL as String` — LiamH, Jan 08 '16 at 08:43

DelNC · Answer 1 · 2016-01-08T01:07:24.823

0

I am not sure but, you can try somethink like that

if you knom the new value to insert in the database try with a syntax like this one

UPDATE table
SET Users.name = 'NewName',
    Users.address = 'MyNewAdresse'
WHERE Users.id_User = 10;

Now, if you want to use a form (php) You have to use this

if(isset($_REQUEST["id_user" ])) {$id_user = $_REQUEST["id_user" ];}
else {$id_user = "" ;}
if(isset($_REQUEST["name" ])) {$name= $_REQUEST["name" ];}
else {$name = "" ;}
if(isset($_REQUEST["address" ])) {$address= $_REQUEST["adress" ];}
else {$adress= "" ;}

if you use mysql UPDATE table SET Users.name = '$name', Users.address = '$adress' WHERE Users.id_User = 10;

i don't know VBA but I will try to help you

edited Jan 08 '16 at 01:07

answered Jan 07 '16 at 23:41

DelNC

1
2

Well I might have taken the wrong approach. I have a form and I put a listbox in it. When they hit search I use row source to display the values in the listbox. Well, I want to use a double click event to display the info in the form.... – Nkay_ Jan 07 '16 at 23:53
I prepared an example of form with a listbox with an example of how to retrieve data and I post – DelNC Jan 08 '16 at 00:01
Wow. That would be great! Thank you! – Nkay_ Jan 08 '16 at 00:02
I have a website you can help you https://support.office.com/en-us/article/Update-data-by-using-a-query-f01eb7a3-a673-4edd-a425-f6769d2bb717 – DelNC Jan 08 '16 at 01:20
Some help for listbox with VBA http://www.java2s.com/Code/VBA-Excel-Access-Word/Forms/Selecttheitemsprogrammatically.htm – DelNC Jan 08 '16 at 01:47
Thank you everyone for the awesome advice. I honestly appreciate it ! – Nkay_ Jan 08 '16 at 03:53

LiamH · Answer 2 · 2016-01-18T08:15:10.877

Going on from my comment, you first need to declare strSQL as a string variable. Where your error expects 6 values and access doesn't know what they are. This is because form objects need to be outside the quotations of the SQL query, otherwise (as in this case) it will think they are variables and obviously undefined. The 6 expected are the 5 form fields plus 'strSQL'.

Private Sub b_Update_Click()
Dim db As DAO.Database
dim strSQL as string
Set db = CurrentDb

strSQL = "UPDATE Main" & _
         " SET t_Name = '" & Me.txt_Name & "'," & _
         " t_Date =#" & Me.txt_Date & "#," & _
         " t_ContactID =" & Me.txt_Contact & "," & _
         " t_Score =" & Me.txt_Score & "," & _
         " t_Comments = '" & Me.txt_Comments & "'," & _
         " WHERE RecordID = '" & Me.lbl_RecordID.Caption & "';"

       CurrentDb.Execute strSQL
end sub

Note how I have used double quotes to put the form fields outside of the query string so access knows they aren't variables. If your field is a string, it needs encapsulating in single quotes like so 'string'. If you have a date field it needs encapsulating in number signs like so #date# and numbers/integers don't need encapsulating.

Look at the code I have done and you can see I have used these single quotes and number signs to encapsulate certain fields. I guessed based on the names of the fields like ID's as numbers. I may have got some wrong so alter where applicable... Or comment and I will correct my answer.

This is great. Thank you so much for taking the time to break it down. I am getting a syntax error. The only fields that contain numbers is contact id & score. Also, t_Comments has multi lines would that cause an issue? Again, I appreciate what you are doing. — Nkay_, Jan 08 '16 at 19:45
I'll have a look in to the syntax soon, I'm not sure whether the syntax error might be arising from. any syntax error code etc? No I don't think multi line text fields can cause a problem if it is just a text field, maybe ensure characters are under 255? — LiamH, Jan 08 '16 at 20:24

Run Time error 3061 Too Few parameters. Expected 6. Unable to update table from listbox

2 Answers2