Question Auto generated number

[Maclain]

The format is different though? report number has 4 digits and job number has 5, will this not matter?

 

[nanscombe]

Doh!

I didn't spot that.

 

[Maclain]

Got it working, many thanks!

When I tab to Customers, although its a drop down, pressing down arrow moves to the next field. Any way to stop that?

Sorted that one too.

Thanks for all your help here guys. Absolute Legends!

 

[nanscombe]

IF you wanted to tweak it, here is an updated bit of code that would allow you to set the number of zeros. I've added an optional parameter with a default value of 4.

Public Function nextIdString(ByVal nisFieldName As String, ByVal nisTableName As String, ByVal nisPrefix As String, Optional ByVal nisNumberOfZeros As Integer = 4) As Variant

    nextIdString = Nz(DMax("[" & nisFieldName & "]", nisTableName, "[" & nisFieldName & "] Like '" & nisPrefix & "*'"), nisPrefix & "0")
    ' nisPrefix & "0" gives you a default value if one is not found in the table

    nextIdString = Val(Mid(nextIdString, Len(nisPrefix) + 1)) + 1
    ' Get next numerical value by looking at the highest value number after the prefix and adding 1

    nextIdString = nisPrefix & Format(nextIdString, String(nisNumberOfZeros, "0")) ' create next string Id
    ' Create new ID string by concatenating the formated number to the prefix
End Function