Update Query specific to Record

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

Guest

I'd like to make it so that my Update Query so that it will only update a specific record. For example, if the user changes the location of a piece of equipment, the Update Query should place the old location in the Comments field. It works, but it updates all the records rather than just the one that had the location change. Can anyone help?
 
Hey, When you place the data that will update your
selected field, yes it will update all the records. You
need to add some criteria like the specific records ID
number or location ID or Name. Make sense?.

-----Original Message-----
I'd like to make it so that my Update Query so that it
Click to expand...
will only update a specific record. For example, if the
user changes the location of a piece of equipment, the
Update Query should place the old location in the
Comments field. It works, but it updates all the records
rather than just the one that had the location change.
Can anyone help?
 
If you are using the Query Grid, then select the Field [SerialNumber] in the
next available Column and in the criteria row of this column, place the
value of the current record.

--
HTH
Van T. Dinh
MVP (Access)



Kim said:
Yes, it makes perfect sense ... and I was trying to go in that direction
Click to expand...
but I don't know what to write. What I want to say is something like "where
[SerialNumber] = whatever the current record is". Please help one more
time?
 
Great, but I don't know how to make the criteria row the value of the current record. It'll be variable ... dependent on the record that the user chooses to modify. Can you be more specific? How do I tell Access to apply the change to the record that the user selected?

Thanks!

Kim.

Van T. Dinh said:
If you are using the Query Grid, then select the Field [SerialNumber] in the
next available Column and in the criteria row of this column, place the
value of the current record.

--
HTH
Van T. Dinh
MVP (Access)



Kim said:
Yes, it makes perfect sense ... and I was trying to go in that direction
Click to expand...
but I don't know what to write. What I want to say is something like "where
[SerialNumber] = whatever the current record is". Please help one more
time?
:
Click to expand...
Click to expand...
 
I can't be more specific if I don't know the set-up of your database.
Remember, I can't see your database.

My *guess* is that you implicitly referred to the CurrentRecord of the
Active Form. In that case, it may be more efficient as well as avoiding the
possible locking problem to change the value of the Comments Field by VBA
code rather than an Update Query.

For your information (if you want to persist with the Update Query), you can
use the reference to the Control on the Form as the Parameter for your
Query. For example, if you have a TextBox "txtSerialNumber" that is bound
to the Field "SerialNumber" of the Current Record on your Form, you can
refer to the TextBox in the criteria row. Something like:

Forms!YourForm!txtSerialNumber

When the Query is executed, Access will replace the above reference with the
value in the TextBox.

Perhaps, you need to check your Access book(s) on Parameter Queries and see
how Parameters are implemented.

--
HTH
Van T. Dinh
MVP (Access)


Kim said:
Great, but I don't know how to make the criteria row the value of the
Click to expand...
current record. It'll be variable ... dependent on the record that the user
chooses to modify. Can you be more specific? How do I tell Access to apply
the change to the record that the user selected?
 
Back
Top