Runtime error 13 type mismatch

[ShaunG]

I have code to auto Generate the Next Number, Im trying to put a prefix "J" in front of the number to get an outcome J1264 when generated, the field is text

I formated the field



"J"0000 but get a runtime error 13

the code be hind my Generate button is -

Private Sub cmdGenerateJobNo_Click()
If Me.NewRecord = True Then
Me.txtJobNumber = Nz(DMax("JobNumber", "tblJobDetails"), 0) + 1
End If
End Sub

do i need more code for this?

[June7]

The format property setting works?

Try using apostrophe delimiters.

Me.txtJobNumber = "'" & Nz(DMax("JobNumber", "tblJobDetails"), 0) + 1 & "'"

[ShaunG]

The format property setting works?

Try using apostrophe delimiters.

Me.txtJobNumber = "'" & Nz(DMax("JobNumber", "tblJobDetails"), 0) + 1 & "'"

No it didn’t.... the run time error was with using the format setting.

thanks june7 I’ll let you know how I go

[Micron]

Rather than trying to apply a format to the control, why not just create and assign the needed value since you are using code anyway? Something like

Dim lngNextNum As Long

lngNextNum = Nz(DMax("JobNumber", "tblJobDetails"), 0) + 1
Me.TextJobNumber= "J" & Format(lngNextNum, "0000")

[ShaunG]

Rather than trying to apply a format to the control, why not just create and assign the needed value since you are using code anyway? Something like

Dim lngNextNum As Long

lngNextNum = Nz(DMax("JobNumber", "tblJobDetails"), 0) + 1
Me.TextJobNumber= "J" & Format(lngNextNum, "0000")

Yea Micron i would prefer code as i'm already there with the code just wasn't sure how to write it, thanks that code worked a treat.

[ShaunG]

The format property setting works?

Try using apostrophe delimiters.

Me.txtJobNumber = "'" & Nz(DMax("JobNumber", "tblJobDetails"), 0) + 1 & "'"

cheers June7, Microns code did it the job

[ShaunG]

Rather than trying to apply a format to the control, why not just create and assign the needed value since you are using code anyway? Something like

Dim lngNextNum As Long

lngNextNum = Nz(DMax("JobNumber", "tblJobDetails"), 0) + 1
Me.TextJobNumber= "J" & Format(lngNextNum, "0000")

Micron i was a bit premature... this code works great the very first time it is used but if there is a job number before it with a J say J2364 then the error comes up again.

Private Sub cmdGenerateJobNo_Click()
Dim lngNextNum As Long


lngNextNum = Nz(DMax("JobNumber", "tblJobDetails"), 0) + 1
Me.txtJobNumber = "J" & Format(lngNextNum, "0000")


End Sub

[ssanfu]

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:

    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

[ShaunG]

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:

    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

is the reason im creating a new table to see if the existing one has errors in it?

ill let you know how i go.

[ShaunG]

ssanfl it works to a degree, the only problem is if i cancel the job the job number still increases if that makes sense e.g. if i cancel job j2365 half way through putting info in, when i put the next job in instead of it being j2365 its j2366 and so on. with my original dmax code it works as it should.....

id like to use code, i am thinking just adding a another field called JobReference set the format setting to J and then combine them on the form.

[June7]

If the J is saved in field then must extract it before incrementing number part then concatenate it back.

Or use Steve's approach.

Or don't save the J and set InputMask to display J on forms and an expression to concatenate it on reports.

[ShaunG]

If the J is saved in field then must extract it before incrementing number part then concatenate it back.

Or use Steve's approach.

Or don't save the J and set InputMask to display J on forms and an expression to concatenate it on reports.

id like to save the J if i can but inputmask could be the option, thanks.

[June7]

Consider:

"J" & Format(Nz(Mid(DMax("JobNumber", "tblJobDetails"),2)+1,1),"0000")

Is this a multi-user db? There is risk simultaneous users can generate same ID. You might want to create the ID when record is committed. Do users need to see this ID during data entry?

[ShaunG]

Consider:

"J" & Format(Nz(Mid(DMax("JobNumber", "tblJobDetails"),2)+1,1),"0000")

Is this a multi-user db? There is risk simultaneous users can generate same ID. You might want to create the ID when record is committed. Do users need to see this ID during data entry?

is a multi-user db and yeah does have advantages with seeing ID at data entry.

would that code go in the same as the previous dmax code?

Private Sub cmdGenerateJobNo_Click()

If Me.NewRecord = True Then
Me.txtJobNumber = "J" & Format(Nz(Mid(DMax("JobNumber", "tblJobDetails"),2)+1,1),"0000")

End If
End Sub

[June7]

Yes, but then you need to commit the record immediately so another user will not create the same ID. This means if you cancel the input you will have an incomplete record in table or delete record and have gap in sequence. In my db I could not allow gap - every ID had to be accounted for. I have code in Cancel button that reverses user inputs and saves this 'blank' record. Then when another user starts a record, code first searches for blank record and opens it for user input. If no blank found then new ID and record created. I also have a void procedure so established records that we decide do not need to be further processed will be reported as VOIDED. DB is to report construction lab sample testing data.