Automation Number Sequence

[dan.cawthorne]

Hi, I Need Some Help,

Ive Been Using this code below for some time in a database i created
and it works well and doesnt need changing. and it generate Q000108
then then next project would be Q000208 and so on

this code is on the open event on my new project wizard form.

If Me.NewRecord Then
Me!ProjectQNo = "Q" & _
Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
"Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0")) +
1, _
"0000") & Format(Date, "yy")
End If
End Sub

So What I Did was i copied for the database and tweaked it, because
another department in the company want the same database, but in their
number sequence they want "SQ" instead of "Q"

so i changed the code to

If Me.NewRecord Then
Me!ProjectQNo = "SQ" & _
Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
"Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0")) +
1, _
"0000") & Format(Date, "yy")
End If
End Sub

and of course that the database is totally empty and when i open the
form with the above code its does display SQ000108 but then whats
happens is when i try to Create as second project entry the form will
not load up and im presented with Run time error the following is "run
time error 13 - Type Missmatch" if i click end the form loads up but
the ProjectQNo field is shown blank but if i click debug the code is
highlighted in yellow. what i cant understand is that i have not
changed any field names with in tables,

Can someone help in what is happening?

Thanks

Dan

 

[bcap]

You've made the resulting "number" longer by 1 character, so you need to
adjust your call to the Mid function accordingly:

If Me.NewRecord Then
Me!ProjectQNo = "SQ" & _
Format(CLng(Nz(DMax("Mid(ProjectQNo,3,4)", "tbl_Projects", _
"Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0")) +
1, _
"0000") & Format(Date, "yy")
End If
End Sub

 

[dan.cawthorne]

You've made the resulting "number" longer by 1 character, so you need to
adjust your call to the Mid function accordingly:

If Me.NewRecord Then
    Me!ProjectQNo = "SQ" & _
        Format(CLng(Nz(DMax("Mid(ProjectQNo,3,4)", "tbl_Projects", _
        "Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0")) +
 1, _
        "0000") & Format(Date, "yy")
 End If
 End Sub

Thank you Bcap, i did think it was somethng like that but couldnt find
where in the code to look,

once again cheers

Dan