Update Queries and Record Locks

[mattieflo]

Hello. I have a button that runs an update query that just reads a
'DateCompleted' field from table 'tblOrders'. If DateCompleted is not null,
it changes boolean field 'Completed' to true.

My problem is I have multiple users in a form that runs off tblOrders that
are in records as this query is happening. When they try to move off that
record, they get the record lock warning asking whether they need to save the
changes or drop the changes.

Is there a way to get the update query to only update unlocked records? Do
you guys have any suggestions on how to handle this?

Many thanks!

 

[John Spencer (MVP)]

How about you don't do the update at all?

The value of the boolean field is completely dependent on whether or not
DateCompleted has a value. So just check if DateCompleted is Null (Not
Complete) or DateCompleted is not Null (completed). THis way your value will
always be accurate or at least in agreement with whether there is a date in
the DateCompleted field

You can even use an expression in a query

Field: CompletedYN: IIF(DateCompleted Is Not Null,"Complete","Not Complete")

Or if you want to return True/False all you need is:

Field: CompletedYN: DateCompleted Is Not Null


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[mattieflo]

Hey John,

Sorry John I misspoke on the earlier post so I apologize. Instead of a
boolean checkbox, its actually a status combobox.

When the completed field is filled out on the form, I have it automatically
switch it to Pending Status. From there, people wanted another form which
pulled everything in Pending status so they could check it one last time.
When they hit the command on that form, it switches everything in Pending
Status to Completed status. I think there is one last part of the process
that requires them to check all Pending status orders before marking them as
completed.

 

[John Spencer (MVP)]

Well, I know of no way to do what you want. You might minimize the problem
by only updating those records that need to be changed. Something like the
following.

UPDATE tblOrders
SET Completed = "Completed"
WHERE DateCompleted is Not Null
AND Completed = "Pending Status"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County