Try this:
Create a table named "tblNextNum".
It has one field "NextNumber" of type Number - Long Integer.
Add the max number from the field "JobNumber" in table "tblJobDetails". If the largest jobnumber is J2364, then enter 2364 in the table.
Then modify your code:
Code:
Dim lngNextNum As Long
lngNextNum = Nz(DMax("NextNumber", "tblNextNum"), 0) + 1 'gets the number from the new table
Me.TextJobNumber = "J" & Format(lngNextNum, "0000")
CurrentDb.Execute "Update tblNextNum Set NextNumber = " & lngNextNum, dbFailOnError ' updates the NextNumber to the current lngNextNum