Look for last no and add 1

[Alan#]

Hey guys im looking for help with coding a text field in my form, its looking for the last record and adding 1 to it everytime. This is basically an autonumber but i don't want to configure it as an autonumbe, i want to code it in my add record button!

 

[namliam]

look into the DMAX function see how far you get, come back if you run into trouble.

 

[Alan#]

My Table is Called Skips Delivered and my text box is called txtNo
Error 94: Invalid use of Null

Public Function NewNo() As Long
On Error GoTo NextNo_Err
Dim lngNextNo As Long
Find highest No in the Skips Delivered table and add 1
lngNextNo = DMax("[txtNo]", "Skips Delivered") + 1
'Assign function the value of the Next No
NewNo = lngNextNo
'Exit function now after successful incrementing or after error message
Exit_NewNo:
Exit Function
'If an error occurred, display a message, then go to Exit statement
NextNo_Err:
MsgBox "Error " & Err & ": " & Error$
Resume Exit_NewNo
End Function

 

[pr2-eugin]

Try using a Nz(), DMax can return a Null value if the criteria is not met.

Public Function NewNo() As Long
On Error GoTo NextNo_Err
    Dim lngNextNo As Long
   [COLOR=Green] 'Find highest No in the Skips Delivered table and add 1[/COLOR]
    lngNextNo = Nz(DMax("[txtNo]", "Skips Delivered"), 0) + 1
   [COLOR=Green] 'Assign function the value of the Next No[/COLOR]
    NewNo = lngNextNo
    [COLOR=Green]'Exit function now after successful incrementing or after error message[/COLOR]
Exit_NewNo:
    Exit Function
    [COLOR=Green]'If an error occurred, display a message, then go to Exit statement[/COLOR]
NextNo_Err:
    MsgBox "Error " & Err & ": " & Error$
    Resume Exit_NewNo
End Function

 

[MarvinM]

Alan,

You said in your original post that this field [txtNo] is a text field. Right? You may need to convert your text to a number, add 1, then convert the result back to a string to use it in your table. I'm not sure if the automatic conversions work all the way through this function or not. I just wanted to throw in two cents, just in case you still have problems getting it to run.