Update Query for Current Record Only

[Adrian]

I have an update query which I want to run when a record is given focus in a
form.

But I do not know how to reference this either in the event property or the
query itself so that the query does not update all the records in the target
table.

Can anyone else?

 

[fredg]

I have an update query which I want to run when a record is given focus in a
form.

But I do not know how to reference this either in the event property or the
query itself so that the query does not update all the records in the target
table.

Can anyone else?

Not much to go on here.
What is the name of the field you wish to update?
What is it's datatype?
Where do you get the value you wish to update the field to?

You don't need a separate query.

Each record should have it's own Prime Key unique record number.
Let's assume it's named [RecordID], and that it is a Number datatype.

Code an event on the form (perhaps the form's current event if you
wish to do this as you navigate to each record):

Dim strSQL as String
strSQL = "Update MyTable Set MyTable.[FieldName] = SomeValue Where
MyTable.[RecordID] = " & Me.[RecordID]

CurrentDb.Execute strSQL, dbFailOnError

If you get an error that Access can't find Execute, make sure you have
a reference set to the Microsoft DAO 3.6 Object Library.

Open any VBA code window. Click on Tools + References.
If you don't see the DAO library checked up near the top of the list,
navigate down the list until you find it. Place a check in it's check
box. Close the list and save.

You'll have to adapt the above syntax to fields of different
datatypes.

 

[Adrian]

Sorry for the lack of detail.

The field I want to update will be 'Status' in table 'Documents', which is a
text field.

The value I want to update this field to is "Being Edited", but I might
change my approach on this where the value is determined by weather a tick
box (called 'Edit' in the same table) is checked or not.

How would this fit in to the code you provided below?

I have an update query which I want to run when a record is given focus in a
form.

But I do not know how to reference this either in the event property or the
query itself so that the query does not update all the records in the target
table.

Can anyone else?

Not much to go on here.
What is the name of the field you wish to update?
What is it's datatype?
Where do you get the value you wish to update the field to?

You don't need a separate query.

Each record should have it's own Prime Key unique record number.
Let's assume it's named [RecordID], and that it is a Number datatype.

Code an event on the form (perhaps the form's current event if you
wish to do this as you navigate to each record):

Dim strSQL as String
strSQL = "Update MyTable Set MyTable.[FieldName] = SomeValue Where
MyTable.[RecordID] = " & Me.[RecordID]

CurrentDb.Execute strSQL, dbFailOnError

If you get an error that Access can't find Execute, make sure you have
a reference set to the Microsoft DAO 3.6 Object Library.

Open any VBA code window. Click on Tools + References.
If you don't see the DAO library checked up near the top of the list,
navigate down the list until you find it. Place a check in it's check
box. Close the list and save.

You'll have to adapt the above syntax to fields of different
datatypes.

 

[Ron2006]

Since you are sitting on the record and you are actually changing
(clicking) the "edit" tick box all you need to do is:


In the afterupdate event of the tick box

if me.tickboxname = true then
me.Status = "Being Edited"
else
do something else if you want
endif


You need to think about WHEN are you going to change the status back
to something else otherwise the status will remain "being edited"
after you finish the changes.

Ron

 

[Adrian]

Thanks,

What would you do for the following 2 scenarios:-

a) If on selecting a record (in a form called "Documents" so the current
record has the focus) you wanted to run a select query called "History" for
example and display the result in a subform/report. So that as you moved
through the record in form "Documents" the results subform/report would
change/refresh as you selected a new record?

b) Expanding on the answer you gave me below, if i wanted to update the
field "status" purely by selecting a record (in a form called "Documents" so
the current record has the focus) without ticking the tick box what would I
do.

 

[Ron2006]

For Part A:

Possibility One:
If the main form is bound to the table Documents then when you add the
subform the wizard will ask for the linking field between the subform
and the main form and everything will be automatic.

Possibility Two:
If the main form is not bound to the table Documents but contains a
subform listing the documents then
create a txtbox on the main form (visible = false)
In the oncurrent event of that subform move the matching key field
to that field.
manually enter into the history subform in the parent/master -
Child fields the two matching fields.

For Part B:
put the code I mentioned in the first post in the OnCurrent event of
the form. If you do this then I think you will have a problem with
every record saying "Being Edited" simply because you have looked at
the record (You need some way of changeing the status back to whatever
is the default status. This is particularly true if you try to use the
oncurrent event.)

Ron

 

[Adrian]

Thanks Ron,

Last question, is there anyway with for Part B to limit the code so that it
will only updated the current selected record, I do plan to put something
into the event property to change the value back afterwards based on another
set of criteria. I just don't want to update every record in the table, just
the current one.

Thanks for your help with Part A

 

[Ron2006]

An update of the record you are sitting on in the OnCurrent ONLY
occurs to the record you are sitting on AND to every record you sit on
as you go through the records. Remember it will occur on every record
you are sitting on unless you limit the code somehow.

to change the field back to not "Being Edited" you will have to put
the code in the afterupdate event of any and every field that is
related to the logic of putting it back or in the before update event
of the "Form" not a particular field.

While sitting on a record, you can do anything you want to the data of
that record. You just have to determine what is that best event to use
to trigger the execution of that code.

Ron