Question same items repeating for multiple users (duplicate values showing)

[sunilvedula]

Hi All,

My database contains records with unique ID's . they can have account number repeating. i have a button which they click they get 1 record and it checks the account number if it has repeated and has not been resolved (identified by a status col) it will need to throw all of them to the same user. My problem is it does throw up but when multiple users click (approx 15 users) some users are getting the same records for multiple users. i am attaching the code i have written. Can anyone suggest a better way so that it does not show the same records to another user after it has been allocated to one.

'***** Loop for getting 1 case from the queue specified *******'
        DoCmd.SetWarnings False
        DoCmd.RunSQL "update TblWork set TblWork.EmpID = '" & Me.LblUserID.Caption & "' " & _
        "where TblWork.ID in (select Top 1 TblWork.ID from TblWork where TblWork.Queue = '" & VQ & "' and TblWork.EmpID IS NULL ORDER BY TblWork.SLA, Tblwork.Reportdt, TblWork.Amt, TblWork.Cardnumber);"
        DoCmd.SetWarnings True
 
        '**** passing the variable to check for multiple transactions for the same account ****'
 
        VACCT = DLookup("[Cardnumber]", "[qCheckSameAcctFQ]")
 
        '**** CHEKCING FOR MULTIPLE ACCOUNTS****'
 
            Do Until (IsNull(DLookup("[Acct]", "qSameAcct2FQ")) Or DLookup("[Acct]", "qSameAcct2FQ") = "")
 
        Check = DLookup("[Acct]", "qSameAcct2FQ")
        DoCmd.SetWarnings False
 
        DoCmd.RunSQL "Update TblWork set TblWork.EmpID = '" & Me.LblUserID.Caption & "', TblWork.Inputdt = '" & Now & "', TblWork.Status = null WHERE TblWork.ID = " & Check & ";"
 
        DoCmd.SetWarnings True
        Me.LstInbox.SetFocus
        Me.LstInbox.Requery
 
        Loop

 

[ezfriend]

I ran into the same issue for a similar project.

I use a queue of users without projects and a queue of project available.

Then I wrote another db just to check for those two queues.

As soon as a user finish is last project, his db (FE) insert an entry into the User Queue which mark him as finished first with a time stamp.

The admin module (monitor module) is constantly looking for any user that popus up in the queue and then assign the next project to him and delete from the two queue (project and user).

On the user side, his application is always contstantly looking for project assigned to him/her as long as there's no project in his queue. Once the admin module assign a project, it will automatically pop-up in the user's queue and the timer stops. User has the option to refresh to see if there are more project assign to him or her by admin.

Just another suggestion.

Peace,
EZ

 

[sunilvedula]

Maybe you can be more clear. i find it a bit difficult to understand. Are you trying to tell me that to write a module which will check automatically and drop the records once they are complete?

 

[sunilvedula]

Hi I have tried the following. It has brought down the no of time it shows but it still exists. I have passed a variable to a table and once it allocated all the records to the user it deletes the variable. If the variable exists it will wait till the variable gets deleted. I am below pasting the code if any one can helpme as to why do i still get duplicate values.

Public Sub CmdGetNext_Click()
'***** Loop for getting allocated number of cases from the queue specified *******'
VQ = DLookup("[Queue]", "qQueue")

'**** checking for the case availability*******'

If DLookup("[Check]", "qCheckCaseAvail") = 0 Then
MsgBox " No more cases available in the '" & VQ & "' Queue", vbCritical + vbOKOnly + vbDefaultButton1, " NO CASES"
Me.CmdGetNext.Enabled = True
Exit Sub
End If


'**** Once the case are there then it datbase entry code ****'
'*** creating a flag so as to make other person wait until one person gets his records*****'
If DLookup("[Flag]", "qFlag") = "WAIT" Then
Do Until (IsNull(DLookup("[Flag]", "qFlag")) Or DLookup("[Flag]", "qFlag") = "")
DoCmd.SetWarnings False
DoCmd.Hourglass (hourglasson)
DoCmd.SetWarnings True
Loop
DoCmd.SetWarnings False
DoCmd.Hourglass (hourglassoff)
DoCmd.SetWarnings True
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert into TblFlag values ('WAIT');"
DoCmd.SetWarnings True
If DLookup("[Queue]", "qQueue") = "BC" Then '*** BC specifies Business Card specified Fraud Queue****"

VNum = DLookup("[No]", "qQueue") '**** checking for no of cases allocated for each associate ****'
If IsNull(VNum) Or VNum = 0 Then
MsgBox " you have been assigned '" & VNum & "'Please contact your line manager to assign no of items in the BC queue", vbCritical + vbDefaultButton1 + vbOKOnly, "You have no items assigned"
Me.CmdGetNext.SetFocus
Exit Sub
Else

DoCmd.SetWarnings False
DoCmd.RunSQL "update TblWork set TblWork.EmpID = '" & Me.LblUserID.Caption & "', TblWork.Inputdt = '" & Now() & "' " & _
"where TblWork.ID in (select Top " & VNum & " TblWork.ID from TblWork where TblWork.Queue = '" & VQ & "' and TblWork.EmpID IS NULL ORDER BY TblWork.TranCode ASC, TblWork.ID);"
DoCmd.SetWarnings True


'**** passing the variable to check for multiple transactions for the same account ****'

VACCT = DLookup("[Cardnumber]", "[qCheckSameAcctFQ]")

'**** CHEKCING FOR MULTIPLE ACCOUNTS****'

Do Until (IsNull(DLookup("[Acct]", "qSameAcct2FQ")) Or DLookup("[Acct]", "qSameAcct2FQ") = "")

Check = DLookup("[Acct]", "qSameAcct2FQ")
DoCmd.SetWarnings False

DoCmd.RunSQL "Update TblWork set TblWork.EmpID = '" & Me.LblUserID.Caption & "', TblWork.Inputdt = '" & Now & "', TblWork.Status = null WHERE TblWork.ID = " & Check & ";"

DoCmd.SetWarnings True

Me.LstInbox.SetFocus

Me.LstInbox.Requery
'TSum = DLookup("[Count]", "qSameAcctFQ")
Loop
End If
Else
'***** Loop for getting 1 case from the queue specified *******'
DoCmd.SetWarnings False
DoCmd.RunSQL "update TblWork set TblWork.EmpID = '" & Me.LblUserID.Caption & "', TblWork.Inputdt = '" & Now() & "' " & _
"where TblWork.ID in (select Top 1 TblWork.ID from TblWork where TblWork.Queue = '" & VQ & "' and TblWork.EmpID IS NULL ORDER BY TblWork.TranCode ASC, TblWork.ID);"
DoCmd.SetWarnings True

'**** passing the variable to check for multiple transactions for the same account ****'

VACCT = DLookup("[Cardnumber]", "[qCheckSameAcctFQ]")

'**** CHEKCING FOR MULTIPLE ACCOUNTS****'

Do Until (IsNull(DLookup("[Acct]", "qSameAcct2FQ")) Or DLookup("[Acct]", "qSameAcct2FQ") = "")

Check = DLookup("[Acct]", "qSameAcct2FQ")
DoCmd.SetWarnings False

DoCmd.RunSQL "Update TblWork set TblWork.EmpID = '" & Me.LblUserID.Caption & "', TblWork.Inputdt = '" & Now & "', TblWork.Status = null WHERE TblWork.ID = " & Check & ";"

DoCmd.SetWarnings True
Me.LstInbox.SetFocus
Me.LstInbox.Requery

'TSum = DLookup("[Count]", "qSameAcctFQ")
Loop

End If
'*** Removing the flag after records have been entered ****'
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete TblFlag.Flag from TblFlag;"
DoCmd.SetWarnings True


Me.LstInbox.SetFocus
Me.LstInbox.Requery
VTIME = Now()
'*** checking for the no of cases in the inbox *****'
If DLookup("[Count]", "qCount") = 0 Then
Me.CmdGetNext.Enabled = True
Else
Me.CmdGetNext.Enabled = False
End If
End Sub