I've been trying to figure out the solution for a problem I'm having for weeks now but I just couldn't get it right.
So I have a form (frmTracker) that would display several fields from my table (tblDailyTracker). The fields would be updated every day with hundreds of rows but several fields would be left blank. So the staff would fill the blank fields using the form provided.
What I would like to happen is that when the staff open their frontend form they would get the data from the rows with specific criteria.
The criteria would be (table pic provided)
https://drive.google.com/file/d/1j5A2kJ_SOOKb8opcBzsxMwDKedUCOMfW/view?usp=sharing
"Agent" field is null
"Outcome" field is null
"QueueName" field in not null
"Due" field is sort by ascending
For this I applied the below VBA on the form_open_event:
'filters agent's ID & blank Outcomes & blank QueueNames
Dim strFilter As String
strFilter = "[Agent] Is Null AND [Outcome] Is Null AND Not IsNull(QueueName)"
Me.Filter = strFilter
Me.FilterOn = True
It worked, by showing the row with blank "Agent", blank "Outcome" & not blank "QueueName". I could not get the form to show oldest date first however (any ideas?).
To make sure 2 staffs won't get the same row I placed a VBA in the form_current_event to fill the "Agent" field automatically with the staff's network ID & immediately save it.
If IsNull(Me.Agent) Then
Me.Agent = Me.NetworkID
DoCmd.RunCommand acCmdSaveRecord
End If
I then created a button (nextBtn) with the same filter VBA as above & it worked but I discovered that when 20 or more staffs are working on the same table, they would get the same row at the same time, which I'm guessing is because the table is not immediately updated to fill in the "Agent" field in the table (the code above didn't work immediately).
I would also like to point out that the table is updated every few hours with a fresh batch of new rows that needs to filled up. So that's why I think using filter in the nextBtn would be suitable as it will basically refresh the form with the new batch.
Could someone point me in the correct direction on what VBA can I use so that the staffs would be able to get the next row with matching criteria as above & doesn't overlap someone else's row (basically a better solution than mine above).
*note: the form is directly linked to the table, I'm not sure whether is this good or bad (any advice on this would be appreciated).
