Custom Autonumber Help

[dan.cawthorne]

Hi All,

Some one once created a very simple put useful auto number generator
the following code was used, and did what i needed.

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 actual looked at the Highest number in the ProjectQNo Field with in
the tbl_Projects and then worked out the next number to issue eg
Q374008 .

I Need a Similar code that would produce a number e.g C3750 but for it
not to reset the beginning of the year. can this me done?

 

[Dale Fye]

Try:

If Me.NewRecord Then
Me!ProjectQNo = "Q" _
& Format(CLng(Nz(DMax(Mid(ProjectQNo,2,4),
"tbl_Projects"), 0)) + 1, "0000")
endif


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Mike Painter]

Try:

If Me.NewRecord Then
Me!ProjectQNo = "Q" _
& Format(CLng(Nz(DMax(Mid(ProjectQNo,2,4),
"tbl_Projects"), 0)) + 1, "0000")
endif

Note that this breaks at 9999 and the next number will be 1000
(Mid(ProjectQNo,2) gets everything from the second position on.

 

[Dale Fye]

Mike,

Good observation, but not entirely true. It doesn't break at 9999, it just
rolls over to 1000, which probably is not his intention, although the OP
failed to indicate whether he wanted a longer ProjectQNumber what he wanted
to do when it reached 9999.

Personally, and I probably should have put this in my original response, I
strongly recommend against concatenating values into a single field. If the
Q is a constant, and never changes, it should not even be stored. And in
the original instances, he should just have a # field (this could either be
auto-number of manually generated), and a year field.

If he wants to present this as some sort of ProjectQNumber, then he can
concatenate them in the query that he uses for his recordsource.

Dale

 

[Mike Painter]

I meant Break as in broken and should have used your term
I just realized that at this point there will never be a 1001, just a string
of 1000's.

 

[Dale Fye]

Mike,

I see what you mean.

For some reason, I thought that Format(10001, "0000") was going to give me
"0001", but it doesn't, it just keeps growing. But when you concatenate this
to the Q and the year, you will get "Q1000008".

And since I used DMAX(Mid(ProjectQNum, 2, 4) ..., I'm always going to get
the "9999" as the maximum, once that value is reached.

That is why I always prefer to use multiple fields for this type of thing,
and concatenate them in a query, if I absolutely need it them mashed together.

--
Dale

email address is invalid
Please reply to newsgroup only.

 

[dan.cawthorne]

Mike,

I see what you mean.  

For some reason, I thought that Format(10001, "0000") was going to give me
"0001", but it doesn't, it just keeps growing.  But when you concatenate this
to the Q and the year, you will get "Q1000008".

And since I used DMAX(Mid(ProjectQNum, 2, 4) ..., I'm always going to get
the "9999" as the maximum, once that value is reached.

That is why I always prefer to use multiple fields for this type of thing,
and concatenate them in a query, if I absolutely need it them mashed together.

--
Dale

email address is invalid
Please reply to newsgroup only.

Hey Thanks for replys sorry not been back in touch with your
responses, i hope all is well and had a happy new year.

What I'm actual asking for is that i want the dmax to look up the
highest number in the "ContractNo" field in the "tblContacts" Table
then Give a Number of Say C7932

 

[dan.cawthorne]

Hey Thanks for replys sorry not been back in touch with your
responses, i hope all is well and had a happy new year.

What I'm actual asking for is that i want the dmax to look up the
highest number in the "ContractNo" field in the "tblContacts" Table
then Give a Number of Say C7932

also i forgot to mention ive tried the code and nothing is happening,
i assume im doing something wrong!

I Have an unbound form with a unbound drop down list which is shows
projects that meet certain criteria , ie is Project , Active, Won,
then when a project is selected on a bound subform, the selected
project is appeared, with a Blank ContractNo Field, a ProjectQNo
Field and ProjectTitle Field and Export Tick Box

what should happen is when i select the project from the drop down
list when the relates project is appeared in the subform the
ContractNo field should have the new number in by looking in the
contractNo Field in the Contract Table. the contract table is where
all previous contacts have been exported.

the following coded is what i tried. on for subform current event.and
on load and on open.

If Me.NewRecord Then
Me!ContractNo = "C" _
& Format(CLng(Nz(DMax(Mid(ContractNo,2,4), "tblContracts"), 0)) +
1, "0000")
End if

 

[dan.cawthorne]

also i forgot to mention ive tried the code and nothing is happening,
i assume im doing something wrong!

I Have an unbound form with a unbound drop down list which is shows
projects that meet certain criteria , ie is Project , Active, Won,
then when a project is selected on a bound subform, the selected
project is appeared, with a Blank ContractNo Field,  a ProjectQNo
Field and ProjectTitle Field and Export Tick Box

what should happen is when i select the project from the drop down
list when the relates project is appeared in the subform the
ContractNo field should have the new number in  by looking in the
contractNo Field in the Contract Table. the contract table is where
all previous contacts have been exported.

the following coded is what i tried. on for subform current event.and
on load and on open.

If Me.NewRecord Then
     Me!ContractNo = "C" _
     & Format(CLng(Nz(DMax(Mid(ContractNo,2,4), "tblContracts"), 0)) +
1, "0000")
 End if

Just Been thinking, the reason it may not work is that its not an
actual new record, more of a means of selecting an existing record to
export, but i wont to export with a new Cnumber.

 

[dan.cawthorne]

Just Been thinking, the reason it may not work is that its not an
actual new record, more of a means of selecting an existing record to
export, but i wont to export with a new Cnumber.

Dale,

Ive Tweaked your code and i ahave it kinda working, but its starting
from the begining

this code is but on the subform and adds the Contract Number to the
field,

Private Sub Form_Current()
If Me.CurrentRecord Then
Me.ContractNo = "C" _
& Format(CLng(Nz(DMax(Mid(ContractNo, 2, 4), "tblContracts"), 0)) + 1,
"0000")
End If
End Sub

the Highest Number in the tblContracts is C3748 so surly it should
give me C3749, but its giving me C0001 then C0002 etc

and also i read that when it gets to C9999 the next one will be C0001
I Need it to do to C10000 then C10001 can that be achieved,

 

[Dale Fye]

Dan,

Take a look at the code I posted today in the Programming (modulesdaovba)
newsgroup, in response to the thread "Autonumber based on year".


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.