Primary key not incrementing

[benjee]

Hello,

I'm having problems trying to increment the JobNo primary key when i want to create a new job. The code in comments is the code im working on.

The rest of the code is the procedure that kicks in when the Add Job is clicked from a customer details form (that appears prior to the Job Details form).

I want the Job number to increment automatically when this Add Job button is pressed (from the customer details screen).

regards

Private Sub AddJob_Click()

On Error GoTo Err_AddJob_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim JobnoID As Long

If IsNull(Me![Surname]) Then
MsgBox "Enter customer information before entering a job"
Else
'DisplayJob is a global variable defined in BasMisc
'and tested in frmJobSheet On Open
DisplayJob = False
stDocName = "frmJobSheet"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

/* DoCmd.GoToRecord , , acLast
JobnoID = JobNo
If JobnoID = 0 Then
JobNo = 1
Else
DoCmd.GoToRecord , , acNext
JobNo = JobnoID + 1
End If
*/ End If

Exit_AddJob_Click:
Exit Sub

Err_AddJob_Click:
MsgBox Err.Description
Resume Exit_AddJob_Click

End Sub

 

[KevinM]

Alternatatively you can use the DMax function to look up the last ID in the table and simply add 1 to it.

 

[benjee]

Thanks Kevin,

do you literally replace the DoCmd with the DoMax function and keep the rest of the code the same?

 

[KevinM]

Private Sub AddJob_Click()

On Error GoTo Err_AddJob_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim JobnoID As Long

If IsNull(Me![Surname]) Then
MsgBox "Enter customer information before entering a job"
Else
'DisplayJob is a global variable defined in BasMisc
'and tested in frmJobSheet On Open
DisplayJob = False
stDocName = "frmJobSheet"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

'Lookup last Job ID and add 1
JobnoID =DMax("[JobNoID]","Job Table Name") +1
' Change above to your JobID field and Job Table names

Exit_AddJob_Click:
Exit Sub

Err_AddJob_Click:
MsgBox Err.Description
Resume Exit_AddJob_Click

End Sub

 

[benjee]

Kevin,

Darn thing still isnt incrementing. Its remaining at zero still. Have i put the code in the wrong place? Is the Add Job button the right place to put it? Im sure it is though.

Ive tried taking out the Dim JobnoID As Long code, and reverting back to calling the variable by its proper name i.e JobNo.

Just to clarify tblJob is the table name & JobNo is the primary key for that table.

 

[KevinM]

Try...

Forms!frmJobSheet!Jobno =DMax("[JobNo]","tblJob") +1

 

[neileg]

Am I stupid to ask why you don't use an autonumber for the primary key? Even if you have a special requirement for the Job Number, I prefer an autonumber as the key.

 

[benjee]

Thanks for both your help guys.

Both methods work now!

Although im abit of a dumbass not figuring out i could use autonumber as the datatype earlier.