Setting numbers

I have a number in a form/table that is incremented by 1 using this code:

Private Function NextQASRNumber()

    Dim lngNextNumber As Long
    Dim strSQL As String

    lngNextNumber = Nz(DLookup("[Somenumber]", "tblLastSomenumber"), 0) + 1
    strSQL = "UPDATE tblLastSomeNumber SET Somenumber = Somenumber +1"
    CurrentDb.Execute strSQL, dbSeeChanges
    NextSomeNumber = lngNextNumber

End Function

Open in new window


Private Sub Form_Dirty(Cancel As Integer)
On Error GoTo Err_Form_Dirty

    If IsNull(Some_N) Or Some_N = 0 Then
        Me.txtSome_N = NextSomeNumber
        Me.txtUserID = DLookup("[dbUserID]", "LOCALtblCurrentUser")
        Me.txtUser_Name = DLookup("[CurrentUser]", "LOCALtblCurrentUser")
        Me.txtDateCompleted = Date
    End If

    Exit Sub

    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Source: " & Err.Source
    Resume Exit_Form_Dirty
End Sub

Open in new window

But the problem is, if the user clicks an Undo command button on the form I use this:

Private Sub cmdUndoRecord_Click()
On Error GoTo cmdUndoRecord_Click_Err

    Dim strSQL As String

    On Error Resume Next
    DoCmd.RunCommand acCmdUndo
    strSQL = "UPDATE tblLastSomeNumber SET Somenumber = Somenumber -1"
    CurrentDb.Execute strSQL, dbSeeChanges
    If (MacroError <> 0) Then
        MsgBox MacroError.Description, vbOKOnly, ""
    End If

    Exit Sub

    MsgBox Error$
    Resume cmdUndoRecord_Click_Exit

End Sub

Open in new window

which sets the number back by 1.  But the issue is this...  If a 2nd user has created a record with the form before the 1st user does the undo,  he/she gets the next number and then the 1st user sets the number the 2nd user got back by 1.

Is this messed up?  What is a better way around all of this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you are trying to simulate an autonumber, WHY?  You have exactly the same problem that the built-in autonumber has.  Once a number is committed, there is no consistent way to back it out.  You really can't do this better than Access can so just use an autonumber OR keep your current method but eliminate the -1 action.  What is generated is committed and you can't back it out.

I'm assuming that your initial DLookup() is getting a number from a table that has only a SINGLE record.  If not, you are getting a random number because you have no criteria.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Steve,

if you need to make sure all numbers are used, and are assigned with the next one available, you need to make a table of the numbers with an indicator field such as yes/no that I will call Flag.  While a new record is being created, you might grab the next available number using DMin for the Flag is not marked as used, and then mark it as used in the numbers table. If the user doesn't actually save that change to the record, the Flag is cleared, making that number ok to use again

have an awesome day,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
Crystal, I think I like your suggestion and will be getting back into the project on Monday.  Thanks for the tip.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Steve, did that work for you? ... is it Monday yet? (smile)
SteveL13Author Commented:
Yes.  It is now Monday.  (Sorry)  Great suggestion.
Reusing an ID is considered poor practice.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.