hide a record while it's being edited in a form (1 Viewer)

[shura30]

Hello everyone!

I have this database in order to assign a task to each logged on user. The db is split between be and fe already and everything is on a network drive.

Admins import tasks on the main table where there is a 'inUse' boolean column defaulting as false.

A query reads a few fields from the table and filters out the 'inUse=true' records.

Records from the query are loaded into a form where I disabled record navigation, on "form_load" the record is set "inUse=true" and a DoCmd.RunCommand acCmdSaveRecord is run, if another FE instance is open, this record should never be loaded from the query and so the form.

There's a 'save and continue' button, on_click it simply closes the form and reopens it with a new record where 'inUse=false'

What could go wrong in a network environment between each FE database?could the query and form slow down and show the same record to two or more users?

Is this logic viable?I searched around the web and couldn't find anything like this, I've seen the record lock options but it's not really what I need, 'inUse=true' records should never appear to anyone else beside the one who gets it first.

 

[Gasman]

DoCmd.RunCommand acCmdSaveRecord is run, if another FE instance is open, this record should never be loaded from the query and so the form.

Timing issue?
The form load only runs once, so you will only ever affect one record, until you load the form again.

That way I did something like this, was to get the team leaders to allocate the records to particular users, and the form only loaded records for that user, and marked them complete, when the work had been done.

 

[arnelgp]

is your database split?
you should consider employing a "semaphore" using a dummy table.
you first, remove the recordsource of your form.
all users must open this dummy table Exclusively before setting the recordsource of the form.
only 1 user can have Exclusive use of the table (recordset), therefore only 1 user can first set the query to the form.
after successfully, getting exclusive use of the table, set "inUse=true", then Unlock the dummy table.

 

[shura30]

after successfully, getting exclusive use of the table, set "inUse=true", then Unlock the dummy table.

Then another FE would get an error.
I'm trying to setup a flow of records in a way where everyone gets their own without causing interruptions to others

 

[Gasman]

So assign records like I (or the TL's did)

 

[shura30]

So assign records like I (or the TL's did)

This would defeat the purpose of having a centralized database

 

[Gasman]

This would defeat the purpose of having a centralized database

Really, worked fine for one of the big 4 banks?
Have it your way.

 

[CJ_London]

The db is split between be and fe already and everything is on a network drive.

This would defeat the purpose of having a centralized database

that is not very clear - implies all users are using the FE on the network drive and do not have their own local copy. Please clarify

 

[arnelgp]

Then another FE would get an error

no error will produce if you the recordsource is a query with no records.
if the table is Locked, you need to inform the user that there is a lock and
will retry until it is unlocked and give him the option to exit the form.

the lock is very quick and will not last forever.

 

[shura30]

that is not very clear - implies all users are using the FE on the network drive and do not have their own local copy. Please clarify

Yes everyone has their own FE
But that's not the issue, I'm trying to gather ideas on how to automatically assign a task exclusively to a user preventing as many issues as I can.
Once a task is inUse no one else should be able to load it in the form

 

[arnelgp]

here is a simple demo to Manually place a lock.
Open form1.

For Client/server use OpenTableExclusively2() to place a lock just create a dummy table in the BE and create a link.
passed the Linked dummy table name to the function.

 

[arnelgp]

But that's not the issue, I'm trying to gather ideas on how to automatically assign a task exclusively to a user preventing as many issues as I can.
Once a task is inUse no one else should be able to load it in the form

then the best solution is Gasmans suggestion.
you can use the Client computer name or IP address, and
put extra field to your table to put that info.

with this you can Distribute the work evenly.

 

[Gasman]

then the best solution is Gasmans suggestion.
you can use the Client computer name or IP address, and
put extra field to your table to put that info.

with this you can Distribute the work evenly.

We had several users who were allowed to work on high value transactions, others were not.
The TL just allocated 20 or 30 cases to a user for the day. They also had the ability to transfer any outstanding to someone else, or if the user went home sick.
So the TL each morning allocated (and each user had default allocation number) by pressing one button most days, and that was that job done.
Previously it was filtering Excel sheets and copy and paste individual workbooks for each user. DB was split, with BE on a TL's computer and each user had a copy of the FE on their computer for that day. They moved around a lot, but had to login with a unique identifier. If that identifier was not in the DB user table, then they did not get in. Not everyone had to use/or was allowed access to that DB.

You could have the user press a button for the next available record to work on (and even then two could do so at the same time), but that would minimise the chance of conflict I would have thought?

 

[ebs17]

A frontend learns nothing about the activity of another frontend or its communication with the backend. If you fetch records from the backend via query, the resulting recordset has the current status of this current state.

It is therefore practical to only fetch one record for processing at a time in order to avoid ongoing requery using a timer.

It could look something like this (shortened):

Sub NewTask()
   Dim rs As DAO.Recordset
   Dim myID As Long
   Set rs = CurrentDb.Openrecordset( _
         "SELECT TOP 1 * FROM tblTasks WHERE GoForEdit Is NULL ORDER BY Rnd(ID)", _
         dbOpenDynaset)
   With rs
      .Edit
      .Fields("GoForEdit") = Now
      myID = .Fields("ID")
      .Update
      .Close
   End With
   Me.RecordSource = "SELECT * FROM tblTasks WHERE ID = " & myID
End Sub

You see, instead of a Boolean InUse field, I prefer a Date field GoForEdit for a timestamp.

 

[shura30]

A frontend learns nothing about the activity of another frontend or its communication with the backend. If you fetch records from the backend via query, the resulting recordset has the current status of this current state.

It is therefore practical to only fetch one record for processing at a time in order to avoid ongoing requery using a timer.

It could look something like this (shortened):

Sub NewTask()
   Dim rs As DAO.Recordset
   Dim myID As Long
   Set rs = CurrentDb.Openrecordset( _
         "SELECT TOP 1 * FROM tblTasks WHERE GoForEdit Is NULL ORDER BY Rnd(ID)", _
         dbOpenDynaset)
   With rs
      .Edit
      .Fields("GoForEdit") = Now
      myID = .Fields("ID")
      .Update
      .Close
   End With
   Me.RecordSource = "SELECT * FROM tblTasks WHERE ID = " & myID
End Sub

You see, instead of a Boolean InUse field, I prefer a Date field GoForEdit for a timestamp.

I'm using this, looks like what I want
I have no experience updating a recordsource from vba, how do I change the fields values I show in the form?
say that one of the fields shows the ID of the task how do I assign the source to myID?
I hate the double quotes in vba and they hate me apparently

 

[Pat Hartman]

Records from the query are loaded into a form where I disabled record navigation, on "form_load" the record is set "inUse=true" and a DoCmd.RunCommand acCmdSaveRecord is run, if another FE instance is open, this record should never be loaded from the query and so the form.

The load event is the wrong event to use. Use the Current event for this purpose and don't forget to set the inUse flag to false when the user moves to a new record.

The Load event runs only once when the form is opened so it only ever can affect the first record of the recordset. If you NEVER allow the user to scroll to a different record or search for one, ie the user MUST ALWAYS close the form and return to a search form before moving to a new record, then using the Load event will do no harm. However, should you ever change the form or the way it works such that it can scroll to a different record, your code would fail because it will never lock any record except the first one to be viewed. Therefore, using the correct event now, even though now, it may not matter, is far better practice. Then you or your successor won't get bitten next year by a poor practice implemented this year. The form's events are not arbitrary. You need to understand when they fire and that helps with understanding what type of logic belongs in which event to achieve the best result. Logic that affects the form but not a specific record is best in the Open or Load events depending on whether it needs to run before (Open) or after (Load) the RecordSource is loaded. Logic that affects a specific record as this does, belongs in the Current record so the code runs for any new record the form scrolls to.

Do not allow yourself to fall into the sloppy trap of - it doesn't matter now, I'll change it later if I have to.

Also, rather than a flag, it is far better to have two fields - UserID and LockedDT. That way, you know who locked the record and when. I also do not prevent users from seeing a record that is being used by another person. However I do not allow person 2 to edit a record that has been locked by someone else. With both the flag and the more useful two field methods, you need a way to allow the locked record to be forcably unlocked by the Administrator should the lock persist due to a coding flaw or accidental shut down that prevented the unlock procedure from running when it should have.

 

[The_Doc_Man]

The method I always used was to have a UserID in the record that could be edited. If it was blank, I did an UPDATE to insert the user ID into the record. Then as a safeguard, I checked the record to verify that the record was marked for that user. (Because the update isn't quite instantaneous, another user could have gone for the same record at the same time.) Once I know that the indicated user IS the one who locked the record, I allowed fields to be edited. (See the form's .Allowxxxx properties that control whether you are allowed to edit, delete, or insert records.) Once the record is edited and is ready to be saved, you erase the UserID field on the form before you do the save operation.

Pat's comment about including a timestamp is also good, as a way to decide if the user has improperly abandoned the record.

 

[shura30]

I've adapted @ebs17 solution

this db assign tasks to users that other aren't allowed to touch hence the issue to prevent a record from being visible to others but admins that have access to the tables

to be more clear:

The load event is the wrong event to use

a suggestion I got elsewhere was:

(By writing it BEFORE the record is actually opened, you eliminate the risk of two people having the same record open at the same time, but just one hasn't saved it yet with the "In Use" flag set.)

in the last version of the code, VBA runs a query picking the first available record then immediately edits with '.Fields("GoForEdit") = Now' as ebs17 suggested
then the form loads with that single record as recordsource
with the added "save and get another", "save and close" and "release and logout" buttons the form gets reopened every time with a quick succession of DoCmd.Close acForm and DoCmd.OpenForm that guarantee a new unused record

Pat's comment about including a timestamp is also good, as a way to decide if the user has improperly abandoned the record.

I have a button to release a record, the form itself is stripped of the close button, the only event I need to cover is closing the database altogether
is the Form_Close() event the right one?I assume the form gets closed on db shutdown

Logic that affects the form but not a specific record is best in the Open or Load events depending on whether it needs to run before (Open) or after (Load) the RecordSource is loaded.

as of now, the open routine checks if there are record to be queried then on load I pick the record and use that as recordsource
so you're suggesting to do these all in the open, right?

anyways your suggestions are gold, thanks, I'm an access noob and learning a lot from this thread

 

[Pat Hartman]

Since you are swapping one recordsource query for another, I would never allow the form to naturally load the entire table (because that is what is actually happening) before you decide what record you actually want to view. This is why I pointed out how very important it is to actually understand a form's event model so you can figure out where your code needs to go. Based on your original description, it sounded like you were selecting records from a task list form. In that case, the select from the task list form should technically lock the record. If you are not using a task list form but the form itself is doing the selection as it now seems to be, then, yes - moving the update query and the modified RecordSource query set to the Open event prevents unnecessarily dragging all the table records across the LAN every time the form opens.

I frequently post the suggestion to never bind a form to a table or to a query without a where clause. This is less of a problem when your BE is ACE because Access and Jet/ACE are very closely coupled but once your BE is SQL Server or some other RDBMS, you NEVER want to drag the whole table across the LAN. You don't even want to do it with Jet/ACE if the table is more than ~ 50,000 rows. Since the method I suggest works equally well regardless of what BE you are using, it definitely makes your RDBMS bound forms more efficient but should you ever upsize from ACE to SQL Server, you're already much of the way there by just using good client/server techniques from the beginning.

Changing from a flag to the UserID as @ebs17 suggested is good but I also use a dateTime field so I can tell when the record got locked.

I have a button to release a record

That only works if you are on the the record. I am talking about if there is some failure and your form gets disconnected from the record. Your power goes out, the LAN has a hiccup, whatever. Having a separate form that uses the unlock code is necessary because your form will not open again to the record you were just on because it is still locked. You can change this by changing the query to allow a record locked by "you" to be selected as well as unlocked record. But, if you're not there, that still doesn't work. All in all, you should probably just use a separate form to unlock records.

 

[shura30]

Since you are swapping one recordsource query for another, I would never allow the form to naturally load the entire table (because that is what is actually happening) before you decide what record you actually want to view.

in the case of ebs17 suggestion, could you elaborate more on this?practically what am I supposed to do with that piece of code?