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

[shura30]

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:

If Me.Dirty Then Me.Dirty = False
NewTask

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

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:

.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]

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.

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.

 

[spaLOGICng]

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]

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]

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.

 

[shura30]

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:

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

SELECT * FROM tblTasks WHERE False

isn't the same as leaving the source empty?

 

[ebs17]

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.

How to use transactions in Microsoft Access VBA

How to use transactions in Microsoft Access VBA when executing multiple queries with VBA code.

codekabinett.com

 

[shura30]

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]

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...

Speed tests and speed comparisons for everyone

The attached access file contains two tables, three queries, a module and a stopwatch class. If these elements are transferred to your own application, you will be able to carry out speed tests and speed comparisons for a large number of tasks without great effort. This solution is not as broad...

www.access-programmers.co.uk

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

 

[shura30]

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]

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.

 

[shura30]

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?

 

[jdraw]

Shura30,

You may find this article on queue based applications by Philipp Stiefel helpful for concept/background.
(same author as the Transaction article suggested by ebs17 in #31)

 

[ebs17]

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]

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

 

[shura30]

Shura30,

You may find this article on queue based applications by Philipp Stiefel helpful for concept/background.
(same author as the Transaction article suggested by ebs17 in #31)

Great resource thanks, this is the exact scenario I need to address and it's literally the article, almost word for word
Looks like the UPDATE statement is the way to go

quotes from the article:

There should be only one table relevant for managing the queue. You can either put a LockUser- and LockTime-Field (more on the latter in a minute) right into the facts table (queue table) or you can create an additional table for managing the locks. That table would need those two fields and the primary key of the locked record. - There is not much difference between these two approaches.

Any number of users can read the same data from a database at the same time. So, when querying the next free record first and then locking the record, it is possible that two users query the same, not yet locked, record. There will be some latency between reading the record and writing the lock, so this might happen more often than you think.


To prevent this, you need to write the lock information first. That forces the database to serialize the operation. No two user will ever be able to lock the same record. Only read the record from the queue after locking it.

 

[arnelgp]

Looks like the UPDATE statement is the way to go

but if there are 2 users who update on the same time? problem! you have a Deadlock or even a Lockdown, when 2 or more users compete for the same resources. Deadlock meaning your app now becomes unresponsive and the screen just turned white. the only way out is to Ctrl-Alt-Del!

 

[ebs17]

Note on the frequently mentioned transaction:
Line from #33

Set rsLog = db.OpenRecordset("SELECT ID FROM tblLog", dbOpenDynaset, dbFailOnError)

dbFailOnError represents a small transaction in itself. In the event of an error, a rollback occurs.

 

[shura30]

@ebs17
according to what we did so far, we change me.recordsource with a query from within vba, is there a way to unbind after loading without losing the values?
this is the scenario:
user loads a task, admin deletes that task from the main table
I still wish for the user to complete it, assign an exit status and save the information to the exit table
of course if someone deletes the record, the form will show #deleted and a bunch of nonsense is added to the exit table, is there a simple solution to this?

 

[ebs17]

Naturally. ADODB knows Disconnected Recordsets (your keywords for your own research).
Load the query into an ADODB recordset and assign this to the form as a RecordSource. Later, the recordset can be reconnected to the backend and will introduce its changes there.
With ADODB and OLEDB and T-SQL in the query you could also refer directly to the existing SQL Server and use it directly.

admin deletes that task

Immediately? Why?
As an alternative, you do not load the record into a bound form, but rather into a collection of unbound controls in a form.
After completing an edit, you then load the content into the desired tables using your own measures (append query, Recordset-AddNew).