Update problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to update a field in a record by pushing a button on a form.
The following code is giving me this error: "Too few parameters. 1 expected"
Any thoughts?

strSQL = "UPDATE students " & _
"SET txtMBusDetention = 1 " & _
"WHERE txtStudent = '" & Forms!frmMissBus!cboName & "'"
CurrentDb.Execute strSQL, dbFailOnError
 
First, a person's name isn't a good item for this. What if you have two
people with the same name?

Is the form frmMissBus open when this runs? It will need to be to get a
value from the combo box cboName and the value of cboName can't be Null. You
are using apostrophes for the string delimiter, does the person's name have
an apostrophe in it (i.e. O'Hare)? If so, try changing the WHERE clause line
to

"WHERE txtStudent = """ & Forms!frmMissBus!cboName & """"

I replaced each apostrophe with two double quotes. This will delimit the
string with double quotes instead of apostrophes. They have to be doubled up
so that VBA realizes that the quote should be part of the passed string and
not the end of the WHERE string.
 
I made your changes but still get the "Too few parameters. Expected 1."
error. Also, to answer your points, the bound column for cboName is the
person's ID number. Second, the form is open when the code runs.
 
Is the field txtStudent in the table the ID number for the student then?
That is what you are passing to it. Also, if the data type of the txtStudent
field is a number, then the WHERE clause would be

"WHERE txtStudent = " & Forms!frmMissBus!cboName
 
Add

DEBUG.PRINT strSQL
STOP

Take the output and paste it as the SQL for a new query and then select
query design - you should be able to figure out the problem from there.
If I recall correctly the error has to do with the fields. I'm not the
SQL guru so I can't answer it off the top of my head. BTW - I always use
the query builder to create any SQL statement that I use in code, once
the basic statement is buildt I then modify it by hand. I can NEVER
remember the INNER JOINS and stuff.
 
I had misspelled a field, but now the problem is the fact that the field
referenced in the SET statement is not changed in the table after the code is
run. Any thoughts?
 
If the table isn't updated and you don't get an error, then I would expect
there to be nothing that matched your parameter to update. Try creating the
query in the query designer. You can copy and paste the SQL directly into
SQL view (without all of the quotes and other VBA stuff). Open the query and
it will show you how many records will be updated by the query. Does it show
any? Go back to design view and click the Run button on the toolbar, what
happens?
 
If its not SET then the WHERE CRITERIA is not being met. Have you tried
the suggestion that I made about doing a DEBUG.PRINT and pasting the
result into the SQL window of a new query?
 
Back
Top