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!

 

[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!