hide a record while it's being edited in a form

[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

 

[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

 

[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?

 

[nector]

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.

why not use XML to create specific menus for users, I strongly do not think what you are suggesting is workable.

 

[ebs17]

@shura30
If you understand the code in #14, you know what's going on. A record is randomly selected from a pool of tasks to be completed (timestamp field GoForEdit is empty, i.e. NULL). Random to further minimize an overlap with another user. A timestamp is set in this record using the recordset so that it is no longer considered for further selection by others. The record ID of this record is remembered via the myID variable. I can't see anything about a user ID that Pat is talking about.
Finally, this record is loaded into the bound form and can now be edited. A record is now a very filtered RecordSource.

Now how can a process be:

buttons the form gets reopened every time with a quick succession of DoCmd.Close acForm and DoCmd.OpenForm

That's not necessary. That's too much effort.
A button can complete saving the existing record. In the event procedure for this:

If Me.Dirty Then Me.Dirty = False
NewTask

The NewTask procedure is then called in the event procedure. This selects and loads a new record; filtering on the record ID causes the edited record to disappear. Of course, the form should remain open.

So that an entire table is not loaded when the form is first started, the standard RecordSource is set to empty quantity.

SELECT * FROM tblTasks WHERE False

In Form_Open you can now also call NewTask and get a new record.

What still remains open:
- Completing the processing of the last record in conjunction with closing the form.
- In the NewTask procedure, for a tiny millisecond period (selecting the record and setting the timestamp), there remains the risk that two users will make the call at the same time and accidentally address the same record.
So if you have a lot of hard-working users who work extremely intensively, you would have to install a mechanism so that an error is triggered in the event of double access and this then leads to one of the users simply making a new record selection again.