Code Stopped Working Help

[dan.cawthorne]

Hi All,

A Few Months ago i had some help and creating some code that
automatically generated a number for each project which was recieved,

its been working fine untill We hit the Year 2008,

The Code Made up a Value for example
Q586707
Q586807
Q586907 < Last Number for 2007

The Code Then Was Suppose to Start the first number off as

Q000108

which it did, and no when i open the wizard i created it keeps
generating that number its not moving up to Q000208

and you see the number is made up of 2 parts Q0000 08

Heres the code and its on the forms Current Event Procedure

Private Sub Form_Current()

If Me.NewRecord Then
If Format((Date), "yy") <> Right( _
DMax("ProjectQNo", "tbl_Projects"), 2) Then
Me!ProjectQNo = "Q0001" & Format((Date), "yy")
Else
Me![ProjectQNo] = "Q" & _
Mid(DMax("ProjectQNo", "tbl_Projects"), 2, 4) + 1 & _
Format((Date), "yy")
End If
End If

End Sub

Whats wrong with the code?

 

[Ken Snell \(MVP\)]

Your code's logic is faulty. This code step is the problem:

If Format((Date), "yy") <> Right( _
DMax("ProjectQNo", "tbl_Projects"), 2) Then
Me!ProjectQNo = "Q0001" & Format((Date), "yy")

You can see the problem if you open a query on tbl_Projects and sort on the
ProjectQNo field. You'll see that the maximum value for that field is the
last "value" for 2007, not the newest value for 2008.

The problem is exacerbated by the fact that you're storing three separate
data values in a single field: "Q", "0000", and "yy". You should use three
separate fields to store these atomic data items, and then concatenate them
together for display purposes. Then you could more easily find the maximum
value for a single year and get the next value. Then your code would look
like this:

Private Sub Form_Current()
If Me.NewRecord Then
Me!Prefix_ProjectQNo = "Q"
Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
"tbl_Projects", "Year_ProjectQNo=" & _
CLng(Format(Date),"yy"))), 0) + 1
Me!Year_ProjectQNo = Year(Date)
Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
End If
End Sub



In the meantime, I recommend that you change your code to this (see how
complicated the parsing gets when you store three separate values in a
single field?):

Private Sub Form_Current()
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

 

[John W. Vinson]

Hi All,

A Few Months ago i had some help and creating some code that
automatically generated a number for each project which was recieved,

its been working fine untill We hit the Year 2008,

The Code Made up a Value for example
Q586707
Q586807
Q586907 < Last Number for 2007

The Code Then Was Suppose to Start the first number off as

Q000108

which it did, and no when i open the wizard i created it keeps
generating that number its not moving up to Q000208

and you see the number is made up of 2 parts Q0000 08

Heres the code and its on the forms Current Event Procedure

Private Sub Form_Current()

If Me.NewRecord Then
If Format((Date), "yy") <> Right( _
DMax("ProjectQNo", "tbl_Projects"), 2) Then
Me!ProjectQNo = "Q0001" & Format((Date), "yy")
Else
Me![ProjectQNo] = "Q" & _
Mid(DMax("ProjectQNo", "tbl_Projects"), 2, 4) + 1 & _
Format((Date), "yy")
End If
End If

End Sub

Whats wrong with the code?

What's wrong is that it's looking at the maximum Q number. The text string
"Q586907" is in fact greater than the string "Q000108" - *YOU* know that the
08 at the end should dominate the preceding five characters, but your code
doesn't!

This kind of "intelligent key" is generally A Bad Idea, for this very reason.
You need some complicated code, and some very inefficient searches, to find
the increment. Could you consider instead JUST using a ProjectDate or
ProjectYear field, and an Integer number that you increment? YOu can
concatenatate the pieces for display purposes, and you'll have just a simple
lookup to find the largest existing number.

John W. Vinson [MVP]

 

[dan.cawthorne]

Hi All,

A Few Months ago i had some help and creating some code that
automatically generated a number for each project which was recieved,

its been working fine untill We hit the Year 2008,

The Code Made up a Value for example
Q586707
Q586807
Q586907 < Last Number for 2007

The Code Then Was Suppose to Start the first number off as

which it did, and no when i open the wizard i created it keeps
generating that number its not moving up to Q000208

and you see the number is made up of 2 parts Q0000 08

Heres the code and its on the forms Current Event Procedure

Private Sub Form_Current()

If Me.NewRecord Then
If Format((Date), "yy") <> Right( _
DMax("ProjectQNo", "tbl_Projects"), 2) Then
Me!ProjectQNo = "Q0001" & Format((Date), "yy")
Else
Me![ProjectQNo] = "Q" & _
Mid(DMax("ProjectQNo", "tbl_Projects"), 2, 4) + 1 & _
Format((Date), "yy")
End If
End If

End Sub

Whats wrong with the code?

What's wrong is that it's looking at the maximum Q number. The text string
"Q586907" is in fact greater than the string "Q000108" - *YOU* know that the
08 at the end should dominate the preceding five characters, but your code
doesn't!

This kind of "intelligent key" is generally A Bad Idea, for this very reason.
You need some complicated code, and some very inefficient searches, to find
the increment. Could you consider instead JUST using a ProjectDate or
ProjectYear field, and an Integer number that you increment? YOu can
concatenatate the pieces for display purposes, and you'll have just a simple
lookup to find the largest existing number.

John W. Vinson [MVP]

Thank You Very Much Ken and John for the explanation its makes sense,
to me now,

and also Ken Thanks for the

Private Sub Form_Current()
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

It Seems to have corrected the problem, but as before all 2008
projects are now been automatically been put to the front of all the
2007 Records.
which you would expect it to.

So the solution would be to split the field into 3 field [Qfield]
[NumberField] and [YearField]

Which i could easily have the 3 field on the New Project Wizard all
next to each other. The Qfield always be a Q and the Year will change
on the year.

So How Would I for the form to increment the Number only, and then
always put the year and Q in the other field.

My Concerns are with the design on my database, Ive Made a great
mistake in having me Primary Key as the [ProjectQno] field
which there fore i have relationships set up from a drawing table, and
clients table and also i have Query Searched on the ProjectQNo

and at current theres about 296 Records in the Projects Table.

Would be Safe to Use the code Ken gave me,

or should i consider changing it?

Regards

Dan

Ps The Increment of Q/0000/08 has the be that way has the company have
had it that way for last 10 Years and not willing to change there ways.

 

[Ken Snell \(MVP\)]

Reply inline...
--

Ken Snell
<MS ACCESS MVP>

Thank You Very Much Ken and John for the explanation its makes sense,
to me now,

and also Ken Thanks for the

Private Sub Form_Current()
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

It Seems to have corrected the problem, but as before all 2008
projects are now been automatically been put to the front of all the
2007 Records.
which you would expect it to.

So the solution would be to split the field into 3 field [Qfield]
[NumberField] and [YearField]

Which i could easily have the 3 field on the New Project Wizard all
next to each other. The Qfield always be a Q and the Year will change
on the year.

So How Would I for the form to increment the Number only, and then
always put the year and Q in the other field.

I provided sample code for this solution in my first reply to your post.
Here is the code example again:

Private Sub Form_Current()
If Me.NewRecord Then
Me!Prefix_ProjectQNo = "Q"
Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
"tbl_Projects", "Year_ProjectQNo=" & _
CLng(Format(Date),"yy"))), 0) + 1
Me!Year_ProjectQNo = Year(Date)
Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
End If
End Sub

The above assumes that you use a Number data type (Field Size is Long
Integer) for the 2-digit year value.

My Concerns are with the design on my database, Ive Made a great
mistake in having me Primary Key as the [ProjectQno] field
which there fore i have relationships set up from a drawing table, and
clients table and also i have Query Searched on the ProjectQNo

and at current theres about 296 Records in the Projects Table.

Would be Safe to Use the code Ken gave me,

or should i consider changing it?

Regards

Dan

Ps The Increment of Q/0000/08 has the be that way has the company have
had it that way for last 10 Years and not willing to change there ways.

 

[dan.cawthorne]

Reply inline...
--

Ken Snell
<MS ACCESS MVP>

Thank You Very Much Ken and John for the explanation its makes sense,
to me now,

and also Ken Thanks for the

Private Sub Form_Current()
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

It Seems to have corrected the problem, but as before all 2008
projects are now been automatically been put to the front of all the
2007 Records.
which you would expect it to.

So the solution would be to split the field into 3 field [Qfield]
[NumberField] and [YearField]

Which i could easily have the 3 field on the New Project Wizard all
next to each other. The Qfield always be a Q and the Year will change
on the year.

So How Would I for the form to increment the Number only, and then
always put the year and Q in the other field.

I provided sample code for this solution in my first reply to your post.
Here is the code example again:

Private Sub Form_Current()
If Me.NewRecord Then
Me!Prefix_ProjectQNo = "Q"
Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
"tbl_Projects", "Year_ProjectQNo=" & _
CLng(Format(Date),"yy"))), 0) + 1
Me!Year_ProjectQNo = Year(Date)
Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
End If
End Sub

The above assumes that you use a Number data type (Field Size is Long
Integer) for the 2-digit year value.

Assume I had to create following fields

[Prefix_ProjectQNo]
[Number_ProjectQNo]
[Year_ProjectQNo]

in the tblprojects

the problem is i getting complie errors in the sample code you gave me

following lines that are giving me compile errors are as

Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo",_
"tbl_Projects", "Year_ProjectQNo=" &_
CLng(Format(Date),"yy"))), 0) + 1

and

Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy"

 

[Ken Snell \(MVP\)]

For the first code block, you need a space in front of the trailing _
character (the line continuation character); you don't have that in your
code:

Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
"tbl_Projects", "Year_ProjectQNo=" & _
CLng(Format(Date),"yy"))), 0) + 1


In the second code block, there is a ) missing from the end of the second
line, and a ( missing after the Date:

Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date(),"yy")

--

Ken Snell
<MS ACCESS MVP>

Reply inline...
--

Ken Snell
<MS ACCESS MVP>

Thank You Very Much Ken and John for the explanation its makes sense,
to me now,

and also Ken Thanks for the

Private Sub Form_Current()
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

It Seems to have corrected the problem, but as before all 2008
projects are now been automatically been put to the front of all the
2007 Records.
which you would expect it to.

So the solution would be to split the field into 3 field [Qfield]
[NumberField] and [YearField]

Which i could easily have the 3 field on the New Project Wizard all
next to each other. The Qfield always be a Q and the Year will change
on the year.

So How Would I for the form to increment the Number only, and then
always put the year and Q in the other field.

I provided sample code for this solution in my first reply to your post.
Here is the code example again:

Private Sub Form_Current()
If Me.NewRecord Then
Me!Prefix_ProjectQNo = "Q"
Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
"tbl_Projects", "Year_ProjectQNo=" & _
CLng(Format(Date),"yy"))), 0) + 1
Me!Year_ProjectQNo = Year(Date)
Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
End If
End Sub

The above assumes that you use a Number data type (Field Size is Long
Integer) for the 2-digit year value.

Assume I had to create following fields

[Prefix_ProjectQNo]
[Number_ProjectQNo]
[Year_ProjectQNo]

in the tblprojects

the problem is i getting complie errors in the sample code you gave me

following lines that are giving me compile errors are as

Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo",_
"tbl_Projects", "Year_ProjectQNo=" &_
CLng(Format(Date),"yy"))), 0) + 1

and

Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy"

 

[dan.cawthorne]

For the first code block, you need a space in front of the trailing _
character (the line continuation character); you don't have that in your
code:

Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
"tbl_Projects", "Year_ProjectQNo=" & _
CLng(Format(Date),"yy"))), 0) + 1

In the second code block, there is a ) missing from the end of the second
line, and a ( missing after the Date:

Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date(),"yy")

--

Ken Snell
<MS ACCESS MVP>

Reply inline...
--
Ken Snell
<MS ACCESS MVP>

Thank You Very Much Ken and John for the explanation its makes sense,
to me now,
and also Ken Thanks for the
Private Sub Form_Current()
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
It Seems to have corrected the problem, but as before all 2008
projects are now been automatically been put to the front of all the
2007 Records.
which you would expect it to.
So the solution would be to split the field into 3 field [Qfield]
[NumberField] and [YearField]
Which i could easily have the 3 field on the New Project Wizard all
next to each other. The Qfield always be a Q and the Year will change
on the year.
So How Would I for the form to increment the Number only, and then
always put the year and Q in the other field.
I provided sample code for this solution in my first reply to your post.
Here is the code example again:
Private Sub Form_Current()
If Me.NewRecord Then
Me!Prefix_ProjectQNo = "Q"
Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
"tbl_Projects", "Year_ProjectQNo=" & _
CLng(Format(Date),"yy"))), 0) + 1
Me!Year_ProjectQNo = Year(Date)
Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
End If
End Sub
The above assumes that you use a Number data type (Field Size is Long
Integer) for the 2-digit year value.

Assume I had to create following fields

[Prefix_ProjectQNo]
[Number_ProjectQNo]
[Year_ProjectQNo]

in the tblprojects

the problem is i getting complie errors in the sample code you gave me

following lines that are giving me compile errors are as

Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo",_
"tbl_Projects", "Year_ProjectQNo=" &_
CLng(Format(Date),"yy"))), 0) + 1

Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy"

Sorry to be a real pain,

Ive got the rid of the code block on the second part of the code,

But still cant get ride of the code block on the first part, I Noticed
you placed a space between the , and _ from this Me!Number_ProjectQNo
= Nz(DMax("Number_ProjectQNo",_ to Me!Number_ProjectQNo =
Nz(DMax("Number_ProjectQNo", _

but still does now work,

second question once ive sorted that issue, is im trying to sort my
records so all the 2008 projects appear at the end of the 2007 but
this not happening, Now that have a field called "Year_ProjectQNo" i
thought i could sort this field then sort by Number_ProjectQNo but i
think i missing something.

also to save my self a lot of hassel in changing the design on my
database ive decided to keep me field "projectQNo"

so once a Q has been generated in the field [Prefix_ProjectQNo] and
number in [Number_ProjectQNo] the the year in
[Year_ProjectQNo] I want it to generate the Q001008 in the field. How
is this done.

Regards

Dan.

 

[Ken Snell \(MVP\)]

Post the full code in your procedure. Let's see what you have right now.

--

Ken Snell
<MS ACCESS MVP>

For the first code block, you need a space in front of the trailing _
character (the line continuation character); you don't have that in your
code:

Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
"tbl_Projects", "Year_ProjectQNo=" & _
CLng(Format(Date),"yy"))), 0) + 1

In the second code block, there is a ) missing from the end of the second
line, and a ( missing after the Date:

Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date(),"yy")

--

Ken Snell
<MS ACCESS MVP>

On 5 Jan, 17:31, "Ken Snell \(MVP\)"
Reply inline...
--

Ken Snell
<MS ACCESS MVP>

Thank You Very Much Ken and John for the explanation its makes
sense,
to me now,

and also Ken Thanks for the

Private Sub Form_Current()
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

It Seems to have corrected the problem, but as before all 2008
projects are now been automatically been put to the front of all the
2007 Records.
which you would expect it to.

So the solution would be to split the field into 3 field [Qfield]
[NumberField] and [YearField]

Which i could easily have the 3 field on the New Project Wizard all
next to each other. The Qfield always be a Q and the Year will
change
on the year.

So How Would I for the form to increment the Number only, and then
always put the year and Q in the other field.

I provided sample code for this solution in my first reply to your
post.
Here is the code example again:

Private Sub Form_Current()
If Me.NewRecord Then
Me!Prefix_ProjectQNo = "Q"
Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
"tbl_Projects", "Year_ProjectQNo=" & _
CLng(Format(Date),"yy"))), 0) + 1
Me!Year_ProjectQNo = Year(Date)
Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
End If
End Sub

The above assumes that you use a Number data type (Field Size is Long
Integer) for the 2-digit year value.

Assume I had to create following fields

[Prefix_ProjectQNo]
[Number_ProjectQNo]
[Year_ProjectQNo]

in the tblprojects

the problem is i getting complie errors in the sample code you gave me

following lines that are giving me compile errors are as

Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo",_
"tbl_Projects", "Year_ProjectQNo=" &_
CLng(Format(Date),"yy"))), 0) + 1

Me!ProjectQNo = Me!Prefix_ProjectQNo & _
Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy"

Sorry to be a real pain,

Ive got the rid of the code block on the second part of the code,

But still cant get ride of the code block on the first part, I Noticed
you placed a space between the , and _ from this Me!Number_ProjectQNo
= Nz(DMax("Number_ProjectQNo",_ to Me!Number_ProjectQNo =
Nz(DMax("Number_ProjectQNo", _

but still does now work,

second question once ive sorted that issue, is im trying to sort my
records so all the 2008 projects appear at the end of the 2007 but
this not happening, Now that have a field called "Year_ProjectQNo" i
thought i could sort this field then sort by Number_ProjectQNo but i
think i missing something.

also to save my self a lot of hassel in changing the design on my
database ive decided to keep me field "projectQNo"

so once a Q has been generated in the field [Prefix_ProjectQNo] and
number in [Number_ProjectQNo] the the year in
[Year_ProjectQNo] I want it to generate the Q001008 in the field. How
is this done.

Regards

Dan.