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

[shura30]

Why are you deleting tasks? Why are they not marked complete and left in the table?

tasks come from an external crm, they often come back with an updated status. Also no point in keeping a huge tblTasks as the one where edited records are stored is worth (for analysis purposes)

anyways, the update method still allows the same record to end to two different users, I really have no idea why it's still possible so I might need to test your solution

if you feel like wasting more time, this is the query that updates the record, I did split it into 3 different strings as I'm hating the double quotes and the readability & _
feel free to stomp on my sql skills :

        contattiQuery = "SELECT codice " & _
                        "FROM tabellaSR " & _
                        "WHERE datalavorazione Is NULL And tipoBacklog = """ & TempVars("backlog") & """ And Substato Like ""*contatto eff"" And DateDiff(""h"", tabellaSR.[Data Aggiornamento], Now()) < 7"
    
        top1query = "SELECT TOP 1 Codice" & _
                    " FROM tabellaSR" & _
                    " WHERE dataLavorazione Is NULL" & _
                        " and codice NOT IN (" & contattiQuery & ")" & _
                        " and tipoBacklog = """ & TempVars("backlog") & """" & _
                    " ORDER BY substato LIKE ""*contatto eff"", substato DESC, DateValue(format(tabellaSR.[Data Aggiornamento], ""dd/mm/yyyy"")), timevalue(format(tabellaSR.[Data Aggiornamento], ""hh:mm:ss""))"

        lockQuery = "UPDATE tabellaSR " & _
                    "SET dataLavorazione = """ & lockTime & """, lockedBy = """ & TempVars("Username") & """, stored = ""no"" " & _
                    "WHERE Codice IN (" & top1query & ")"
        
        dbSR.Execute lockQuery, dbFailOnError
        
        If CBool(dbSR.RecordsAffected) Then
            lockedID = DLookup("Codice", "tabellaSR", "dataLavorazione = """ & lockTime & """ AND lockedBy = """ & TempVars("Username") & """")
        
        Else
            MsgBox "Non ci sono SR da lavorare, torno al login"
            DoCmd.Close acForm, Me.Name, acSaveYes
            DoCmd.OpenForm "Login"
            Exit Sub

 

[arnelgp]

if you feel like wasting more time, this is the query that updates the record,

49 post and still no solution, use IBM (I Better Manual).

 

[ebs17]

@shura30:
You want suggestions, you discuss some of them, but you don't understand them (at least not visibly), and you probably don't really want to try them out either.

You persist in your idea.
In my example in #33, I had determined times of around 5 milliseconds for a local and small table tblTasks for the actual locking (selecting ID and writing ID to log table) and around 10 milliseconds for the overall process before loading the record.
In the first mentioned time of 5 milliseconds, a theoretical risk of double selection remains.

Now compare with your variant, which you can add a rough time measurement to:

'...
Dim sngTime As Single
sngTime = Timer
dbSR.Execute lockQuery, dbFailOnError
MsgBox Timer - sngTime & " seconds were needed for this"
' ...

Your WHERE part with subqueries and complicated criteria is obviously very complex and will take a lot of time, especially if you access a backend table via the network instead of a local table and the table is also slightly larger. This is the time window where multiple selection is possible. Please tell us what time you determined.