Updating a field in a record stored in a table

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

Guest

Can someone tell me how to change/update a field in a record using VBA code?
I need a button on a form to be able to store a 1 in a particular field in an
already existing record, but "brain lock" is preventing me from doing this.
Thanks in advance.

Nick
 
ndunwoodie said:
Can someone tell me how to change/update a field in a record using
VBA code? I need a button on a form to be able to store a 1 in a
particular field in an already existing record, but "brain lock" is
preventing me from doing this. Thanks in advance.

Nick
Click to expand...

dim strSQL as String

strSQL = "UPDATE TableName " &
"SET FieldName = 1 " & _
"WHERE PrimaryKeyFieldName = SomeValue"

CurrentDB.Execute strSQL, dbFailOnError
 
Is the table already open as a recordset?
Are you positioned on the correct record?
If not, you will need to provide info on how you know which table and which
record you want to modify.
 
ndunwoodie said:
Sorry, but this code is giving me a syntax error.
Click to expand...

You did change it to conform to the names of your table and fields right?
Post the exact code you ran.
 
Here it is....

strSQL = "UPDATE students" &
"SET txtMBusDetention = 1" &_
"WHERE txtStudent = Forms!frmMissBus!cboName"
CurrentDb.Execute strSQL, dbFailOnError
 
Hi,
I think it's missing an underscore character _ at the end of the first
line.

strSQL = "UPDATE students" &_
 
ndunwoodie said:
Here it is....

strSQL = "UPDATE students" &
"SET txtMBusDetention = 1" &_
"WHERE txtStudent = Forms!frmMissBus!cboName"
CurrentDb.Execute strSQL, dbFailOnError
Click to expand...

If you look at my sample you will see that I included a space at the end of each
line so that when the whole thing is put together it creates a valid SQL string.
You also have no underscore on the end of your first line and there must be a
space between the ampersand and the underscore on the second line.

Last, you need to delimit the form reference so that the VALUE of the reference
ends up in the string instead of the form reference itself.

strSQL = "UPDATE students " & _
"SET txtMBusDetention = 1 " & _
"WHERE txtStudent = '" & Forms!frmMissBus!cboName & "'"

CurrentDb.Execute strSQL, dbFailOnError
 
Rick,
Thanks for the replies. The syntax errors are fixed but now I get an error
stating "too few parameters. 1 expected".
 
ndunwoodie said:
Rick,
Thanks for the replies. The syntax errors are fixed but now I get an
error stating "too few parameters. 1 expected".
Click to expand...

First I'll clarify my previous post about delimiting the form reference.
When you run a query in the Access GUI the query can use what is called the
"Expression Service" to automatically resolve form references like
Forms!frmMissBus!cboName. If the Jet database engine (the one that Access
uses by default) is processing a query directly it does not have the benefit
of the expression service and so form references have to be explicitly
defines as parameters in the query.

There are two primary methods to execute an action query in VBA code...

DoCmd.RunSQL "SQL Statement or Query Name"
or
CurrentDB.Execute "SQL Statement or Query Name"

I prefer the second method because you can handle errors better and because
it doesn't produce the "Are you sure" prompts (which RunSQL does). However;
RunSQL does use the expression service so in some cases I will still use it.
That being said, if you delimit the form reference so that the SQL string
has the value pulled from the form reference instead of the reference itself
it solves the problem so that is what I usually do.

You can also get "missing parameters" if you spelled a field name wrong so
that is what I would double-check first.
 
I'm sorry to report that I did have a field name misspelled. Now my problem
is that the code doesn't do any updating at all. My field, txtMBusDetention,
in the table is just as empty after running the code as before(it is spelled
correctly). It is supposed to have a 1 in it.
 
ndunwoodie said:
I'm sorry to report that I did have a field name misspelled. Now my
problem is that the code doesn't do any updating at all. My field,
txtMBusDetention, in the table is just as empty after running the
code as before(it is spelled correctly). It is supposed to have a 1
in it.
Click to expand...

And you get no errors? Is that field a number or text DataType? If it's
text you need to put quotes around the 1. Since the SQL string is already
inside double-quotes you have to use single quotes to avoid a conflict.

strSQL = "UPDATE students " & _
"SET txtMBusDetention = '1' " & _
"WHERE txtStudent = '" & Forms!frmMissBus!cboName & "'"
CurrentDb.Execute strSQL, dbFailOnError
 
ndunwoodie said:
I had tried that - no effect. Still does not enter a value in the
field in the SET statement.
Click to expand...

In another post you indicated that the bound column of your ComboBox on the
form was the Student ID number even though it is displaying the student
name. This will not work in your query if you want the query to find the
students name. Your query is effectively saying...

"update all records where the students name = 123"

Since there is no such student the query is working correctly. It is just
updating zero rows because zero rows match the criteria. You either need to
apply the criteria to the Student ID field or grab the other column from
your ComboBox by using...

Forms!frmMissBus!cboName.Column(n)

....where 'n' is the zero-indexed column number containing the students name.
 
My statement, "WHERE txtStudentID = '" & Forms!frmMissBus!cboName & "'", is
asking to update only the field where the ID# (txtstudentID in the table
students) is the same as the ID# in the combo box cboName. The bound column
in the combo box (column 0) is text in the form of an 8 digit number - the
ID number. Is this not correct?
 
ndunwoodie said:
My statement, "WHERE txtStudentID = '" & Forms!frmMissBus!cboName &
"'", is asking to update only the field where the ID# (txtstudentID
in the table students) is the same as the ID# in the combo box
cboName. The bound column in the combo box (column 0) is text in the
form of an 8 digit number - the ID number. Is this not correct?
Click to expand...


Well then that should work. As someone else suggested you need to send the
final SQL string to the debug window so you can examine it. If the problem
is not obvious by looking at it, then you need to copy it into a blank query
SQL view and let Access tell you what is wrong with it. Generally though an
action query that runs without error (but with no visible changes being
applied to the data) indicates that the criteria is incorrect resulting in
zero rows being updated.
 
Here it is....

strSQL = "UPDATE students" &
"SET txtMBusDetention = 1" &_
"WHERE txtStudent = Forms!frmMissBus!cboName"
CurrentDb.Execute strSQL, dbFailOnError
Click to expand...

Sorry to jump in late. I see that Rick has handled to quoting issues of the
parameter in the criterion: I wonder if you also have a data-type problem
in the SET clause. The field is called "txtMBusDetention" but you are
setting it to a numerical 1. Try this:

strSQL = "UPDATE Students " & _
"SET txtMBusDetention = ""1"" " & _
"WHERE txtStudentID = """ & Forms!frmMissBus!cboName & """"


Hope that helps


Tim F
 
Back
Top