Random Autonumber and increment number in the same table

[brother]

Hello!

I'm currently building a db at work. In one of the tables I need both a random autonumber field and a increment number field. Can this be achieved at table level, or do I need to apply some VBA at form level?

Thanks!

 

[Uncle Gizmo]

I think the best way to approach this problem would be to set up the table with an auto number field set to “new values” =“random” this would give you your random auto number field.

With regard to the other field, it’s a very common requirement to have something like an invoice number that increments without any missing numbers. This isn’t possible with an auto number field because any deleted records are discarded and the next increment carries on as if the discarded record was present.

Therefore there are various solutions for generating an incremental number list, I would search the forum for “DMAX” possibly and invoice, not sure really but the solution is within the forum somewhere!

Regarding generating your own incremental number, If you are using such a system with multiple users whom have access to the database at the same time, then there can be an issue regarding when you generate the new number, you may have a situation where two users generate the same number which would cause a conflict which you may have to resolve.

 

[brother]

Thanks Gizmo! I will search the forums and check it out.

Fortunately the system will only have one user at any given time. It's not important that the system remembers deleted records or not.

I wrote some code for the continuous form I'm using. Put on the forms before-update event:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strIncrID As String

    'Run code if Increment field is empty
    If IsNull(Me.txtIncrementID.Value) Then
    
        Set db = CurrentDb()
        
        'Get max record number
        strSQL = "SELECT Max(tblRanIncr.IncrementID) AS MaxOfIncrementID " & _
                 "FROM tblRanIncr;"
        Set rs = db.OpenRecordset(strSQL)
            With rs
                strIncrID = Nz(rs.Fields("MaxOfIncrementID"), "")
            End With
        rs.Clone
        Set rs = Nothing
        
        If IsNumeric(strIncrID) Then
            Me.txtIncrementID.Value = strIncrID + 1
        Else
            Me.txtIncrementID.Value = 1
        End If
    End If

 

[brother]

I think I'll go for your (much simpler) solution Gizmo,

me.invoicenumber = nz(dmax("invoicenumber", "invoicedetails"), 0) +1

Found in this thread.

Thanks again for your tip!