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

nector

Member
Local time
Today, 16:20
Joined
Jan 21, 2020
Messages
381
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

Well-known member
Local time
Today, 15:20
Joined
Feb 7, 2020
Messages
1,989
@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:
Code:
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.
Code:
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:20
Joined
Feb 19, 2002
Messages
43,491
in the case of ebs17 suggestion, could you elaborate more on this?
Sorry. I can't. If you want help on @ebs17's suggestion, please wait for him or someone else to answer.
 

shura30

New member
Local time
Today, 15:20
Joined
Mar 12, 2024
Messages
16
Sorry. I can't. If you want help on @ebs17's suggestion, please wait for him or someone else to answer.
I would like to understand better what do you mean about dragging the whole table across the network

A record is randomly selected from a pool of tasks to be completed
the file has been modified quite a bit since I opened the post,
I can't really select randomly as tasks need to follow a FIFO pipeline. Since they have a opened date and time stamp I might randomize at least the time but I'd rather a more clean solution that I can't think of rn.

A button can complete saving the existing record. In the event procedure for this:
that's going to be the next update.
the form loads the record with the query you posted then adds a note field and an exit status
along with with the record, the save button adds that note and exit field into another table with an update query
in this case, the:

Code:
If Me.Dirty Then Me.Dirty = False
NewTask

would still work or do I need to clean fields manually before loading the next task?

Code:
SELECT * FROM tblTasks WHERE False

is it better to put this in the code itself or in the form bound field?
right not the form field is left empty and all the fields are bound to the query in vba
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.
I could add a 'locked by user field' then an if statement that checks the right user locked the record then loads it into the me.recordsource
but this still wouldn't cover the double access right when:

Code:
.Fields("GoForEdit") = Now
happens
another thing that's missing is a release record piece of code for when access itself gets (properly) closed
handling a crash might be a little bit tricky tho
 

ebs17

Well-known member
Local time
Today, 15:20
Joined
Feb 7, 2020
Messages
1,989
need to follow a FIFO pipeline
So change "ORDER BY Rnd(ID)" To "ORDER BY ID"

If you want to know more precisely, you would have to measure this time under exactly your possible conditions in order to judge how likely it is that two users will trigger the selection at the same time within this time period.
Code:
Set rs = CurrentDb.Openrecordset( _
         "SELECT TOP 1 * FROM tblTasks WHERE GoForEdit Is NULL ORDER BY ID", _
         dbOpenDynaset)
   With rs
      .Edit
      .Fields("GoForEdit") = Now
      myID = .Fields("ID")
      .Update
      .Close
   End With
GoForEdit and ID should of course be indexed in order to run the query efficiently.

I'll put your other questions aside. You obviously haven't worked with a bound form yet.
Do that first, gain knowledge and experience, read tutorials, watch videos, read posts here in the forum. Then we can continue talking.
My attitude: A forum offers help for self-help. Self-help requires the presence of certain basics.
I feel overwhelmed by writing an instruction manual for completely clueless people and I won't do it.
 
Last edited:

spaLOGICng

Member
Local time
Today, 06:20
Joined
Jul 27, 2012
Messages
154
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.
There is no need to reopen the form when the Record is saved.

In the underlying record source to the Form, simply filter out those records that are in use. When the User on other FE's click the save but, simply requery the form, or apply a new filter. A lot of resources are wasted to close and reopen a form. Use the simplest built-in features that are available.
 

shura30

New member
Local time
Today, 15:20
Joined
Mar 12, 2024
Messages
16
I feel overwhelmed by writing an instruction manual for completely clueless people and I won't do it.
no worries, I look for pointers not spoonfeeding, thanks anyways for your support so far
A lot of resources are wasted to close and reopen a form. Use the simplest built-in features that are available
still learning!
 

ebs17

Well-known member
Local time
Today, 15:20
Joined
Feb 7, 2020
Messages
1,989
Code:
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 ID", _
         dbOpenDynaset)
   With rs
      If Not .EOF Then
         .Edit
         .Fields("GoForEdit") = Now
         myID = .Fields("ID")
         .Update
         .Close

         Me.RecordSource = "SELECT * FROM tblTasks WHERE ID = " & myID  ' this line not in measurement
       Else
           ' no record available
       End If
    End With
End Sub
The procedure still needs a check for EOF to avoid errors.

For interest's sake, I measured the time required for record selection. On my computer with a local table it's around 4 milliseconds. So this is the problematic period where a double selection of the same record could occur.

This residual risk could be avoided by using an additional log table in which the selected ID is written into a field with a unique index. As is well known, an error occurs with a duplicate.
Now you can encapsulate the entire action in a transaction; in the event of an error, a complete reset is carried out via rollback.
Now you just have to make a new selection.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:20
Joined
Feb 19, 2002
Messages
43,491
I would like to understand better what do you mean about dragging the whole table across the network
How many records can a user look at at one time? One. How many records are in your table? 10,000, 100,000, 500,000. Do you think it makes sense to bring 500,000 rows from the server into memory on your local PC when the user can't possibly work with more than one at a time? That is exactly what happens when you bind a form to a table or to a query that has no where clause. This is how many samples you see are coded. Once all the data has been brought across the LAN to memory on your PC, then you can use the Access filter methods to allow the user to find what he wants. Each time the user closes the form and reopens it, all those records are brought across the network again. So, depending on how the users use the application, This could easily be 20 times per hour or more. The bigger your tables are, the slower this process is and the larger the burden it places on your network. If you have ever read any questions from an OP who decided to convert to SQL Server to solve a slowness issue with Access only to find his problem has become much worse after the conversion due to the inefficiency of using this technique.

Open a form, bound to your largest table. Keep in mind if you are working with tiny tables you can use seriously poor techniques and nothing matters. Did the x show up immediately in the 1 of x in the navigation bar? It didn't because Access is fetching the rows a few at a time from the server. The form is displayed as soon as enough records to populate it have been downloaded. The rest are sucked down a little at a time behind the scenes.
 

shura30

New member
Local time
Today, 15:20
Joined
Mar 12, 2024
Messages
16
That is exactly what happens when you bind a form to a table or to a query that has no where clause

sadly this is what most tutorials teach right from the start, in my case, I moved away from closing and reopening the form each time and every query has where clause.
Best practices aren't really explained anywhere without digging.

This residual risk could be avoided by using an additional log table in which the selected ID is written into a field with a unique index.

Tested with a few users working on the file and in the span of a few minutes around 10 records were simultaneously locked by 2 different users.
No errors or warnings on anyone's end, checked the table where an update query logs the result of their editing and the records had the same exact ID and lockedBy value which came from the Now() assignment


I thought of adding the username who got to the record first:

Code:
Sub NewTask()
   Dim rs As DAO.Recordset
   Dim myID As Long
   Dim lockTime as date
   Dim locked as Long

locktime = Now()

   Set rs = CurrentDb.Openrecordset( _
         "SELECT TOP 1 * FROM tblTasks WHERE GoForEdit Is NULL ORDER BY ID", _
         dbOpenDynaset)
   With rs
      If Not .EOF Then
         .Edit
         .Fields("GoForEdit") = lockTime
         .Fields("lockedBy") = TempVar("username")
         myID = .Fields("ID")
         .Update
         .Close
     
        locked = dlookup("ID", "tblTasks", "ID = " & myID & " and GoForEdit = " & locktime & " and lockedBy = & TempVar("username"))
         if myID = locked then
            Me.RecordSource = "SELECT * FROM tblTasks WHERE ID = " & myID
        Else
            NewTask
        End if
   
       Else
           ' no record available
       End If
    End With
End Sub

I think there's still the possibility of a record getting assigned to 2 users but the probability of this happening should lower drastically

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

tbh I haven't found anything on the subject
In form desing, the recordsource is not set, I handle it via the code you provided

if I got this correctly, it's just a dummy query to not have any record bound to the form before the vba querying starts

but having
Code:
SELECT * FROM tblTasks WHERE False
isn't the same as leaving the source empty?
 
Last edited:

ebs17

Well-known member
Local time
Today, 15:20
Joined
Feb 7, 2020
Messages
1,989
isn't the same as leaving the source empty?
With the empty query the form is shown completely.

No errors or warnings on anyone's end, checked the table where an update query logs the result of their editing and the records had the same exact ID
With a unique index?
How do you add new records?
In addition, you did not notice the reference to the transaction. You should code this yourself to understand how it works.
 

shura30

New member
Local time
Today, 15:20
Joined
Mar 12, 2024
Messages
16
With a unique index?
The full quote mentioned the GoForEdit field, I'm not using another table to store an indexed row as in my opinion it would wield the same results

the table I mentioned is part of the database design, users get a record from tblTasks, add a note and an exit value then that record is saved to another table for reporting purposes. Keep in mind these ID are searched on an external CRM, some users found themselves working the same external record as another colleague then the reporting table showed saved records with identical IDs and GoForEdit values in the reporting table. Of course ID is not a primary key in the reporting table as these tasks are exported from the CRM and can come back to tblTasks after some time.
In addition, you did not notice the reference to the transaction.
trying to think how to apply a transaction negotiation on that code,
the main issue is the fact that once we store "myID = .Fields("ID")" as a variable, without an additional field, there's no way to tell if we locked the correct ID,
if two or more users simultaneously store the same myID value, of course that's valid in their code but an issue for the logic I want
 

ebs17

Well-known member
Local time
Today, 15:20
Joined
Feb 7, 2020
Messages
1,989
Of course ID is not a primary key in the reporting table
But the ID should be clear (unique index), otherwise you don't have to complain about multiple selections.

Below I have a modified suggestion. An ID (Long) field with a unique index and a LockedDT (Date) field with an index are expected in the tblTasks table. To protect against multiple selections, there is a table tblLog with the ID (Long) field, with unique index.
The same record can be selected by different users in a short period of time. In no case can the same ID be written twice in tblLog. Here the error is used directly for program control.
For additional clarity I have included a measurement, the stopwatch class comes from...
Code:
Sub NewTask_2()
    Dim QSU As New QpcStoppuhr
    Dim db As DAO.Database
    Dim rsTasks As DAO.Recordset
    Dim rsLog As DAO.Recordset
    Dim myID As Long
    Dim i As Long
    Dim xTime As Double
  
    QSU.StartT
    Set db = CurrentDb
    Set rsLog = db.OpenRecordset("SELECT ID FROM tblLog", dbOpenDynaset, dbFailOnError)
    Set rsTasks = db.OpenRecordset( _
        "SELECT TOP 3 * FROM tblTasks WHERE LockedDT Is NULL ORDER By ID", _
        dbOpenDynaset)
    With rsTasks
        Do While Not .EOF
            myID = .Fields("ID")
            i = i + 1
            On Error Resume Next
            rsLog.AddNew
            rsLog.Fields("ID") = myID
            rsLog.Update
            If Err.Number > 0 Then
                i = i - 1
                Err.Clear
            End If
            If i = 1 Then Exit Do
            QSU.StopT
            xTime = QSU.time
            QSU.RestartT
  
            .MoveNext
        Loop
        .Edit
        .Fields("LockedDT") = Now
        .Fields("UserID") = 5       ' representative of the registration
        .Update
        .Close
    End With
    rsLog.Close
    QSU.StopT
  
    MsgBox "ID " & myID & " was selected" & vbCrLf & _
        QSU.time + xTime & " milliseconds the process took time" & vbCrLf & _
        xTime & " milliseconds the blocking of the record took time"
  
    'Me.RecordSource = "SELECT * FROM tblTasks WHERE ID = " & myID
End Sub
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:20
Joined
Feb 19, 2002
Messages
43,491
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
I use a different method than what you are currently discussing. It does continue to show the task as long as it is incomplete and people can view the record. No one else can update it though. When the task is selected I run an update query that updates the selected client record if it is not already locked. I added LockedByID and LockedDT to the client record and it is displayed when the record is viewed so you can see who has the record locked.
 
Last edited:

shura30

New member
Local time
Today, 15:20
Joined
Mar 12, 2024
Messages
16
No one else can update it though.
I my case, users have no added value but wasted time in seeing a record they can't use/edit
they get their task, work on it, release and move to the next

The same record can be selected by different users in a short period of time.

how does access engine handle simultaneous update requests?
I was thinking to change the way we look at this by using an update query instead of a select then edit

so we straight up update the first record that meets the criteria with "LockedDT" and "UserID" then query back via the same parameters which would represent a unique combination as "UserID" is also unique

I think we're allowing the issue by picking and storing locally an ID regardless of a strong WHERE clause
 

ebs17

Well-known member
Local time
Today, 15:20
Joined
Feb 7, 2020
Messages
1,989
how does access engine handle simultaneous update requests?
With a unique index, the first entry is accepted, the second entry throws an error. With the code presented, if an error occurs, the next record is selected and is checked.
What happens if there is a double entry at the identical time? I don't know if this is technically possible. If in doubt, you will then generate a double error. The record remains unselected during this access and is available for reselection.
 
Last edited:

shura30

New member
Local time
Today, 15:20
Joined
Mar 12, 2024
Messages
16
With a unique index, the first entry is accepted, the second entry throws an error.
What do you think of running the update query instead of a select like we're doing?

I was referring on general access behavior not the code you posted.

If we look at this from the update statement point of view at some point 2 users might run at the same time something like:

Timelocked = now
Update top 1 set lockedDT = timelocked, lockedby = username where lockedDT is null and lockedby is null

Select top1 where lockedDT = timelocked and lockedBy = username

Which user wins here? Would it be better to run such query from access rather than executing it from vba?
 

ebs17

Well-known member
Local time
Today, 15:20
Joined
Feb 7, 2020
Messages
1,989
I see it this way: Before updating the table, the content must first be read, i.e. what corresponds to the filter (where lockedDT is null and lockedby is null). This takes some time. If a second frontend initiates exactly the same process during this time, it can address exactly the same record and would then overwrite the entries if no real lock on the record triggers an error.

What can happen: I don't know exactly. Something like this would have to be determined empirically using mass testing. Until then, I believe that anything can happen: Both users get the same record, only one user gets the record while the other one gets nothing, or in the event of an error, both get nothing.
Therefore, my approach was to trigger a controllable and evaluable error in the event of double access as early as possible and with the least additional effort.

As a second safeguard, I increasingly like the measure of writing the UserID into the table with selection (this is already done in my code) and then comparing this UserID with the frontend user login after loading the record into the form. If there is no agreement, your own entries in the table must be withdrawn and then a new election is simply triggered.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:20
Joined
May 7, 2009
Messages
19,246
see post #11 again, using semaphore/lock file.
only one user can have a table locked.
once locked, immediately fetch the record, and update it's "flag" field, then unlock the semaphore table so other user can do the same.
the users will go to loops if the table is locked. it will not be locked forever since, automatically, via code it will update the "flag" field and issue an unlock. this will prevent users contending on same record or possible lock-up of system since they are contending on same record.

database - How to use semaphore to lock table? - Stack Overflow
 
Last edited:

Users who are viewing this thread

Top Bottom