Auto increment Not Functioning Correctly

[Dan]

Hi,

I Have a little code written on aform load

Which basicly looks up ProjectQNo Column, then adds the next number along in that column new record at moment the table starts from E1685 and Finishes at E2504

For some unknown reason when i open the form it thinks the next number should be E2412 - but this has already been used.

the forms code is

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

any body got any ideas? or is there a simpler code? i could use?

Thanks

Dam Cawthorne

 

[Access Developer]

Instead of giving us code that doesn't work, could you describe verbally
what you have, and what you want? Examples are not sufficient as a
definition (e.g., "starts from E1685 and finishes at E2504") ... I might
assume what you want is "the character 'E' prepended to a
monotonically-increasing integer", but obviously from the function you show,
it is not that simple.

What is clear is that the value is not "atomic" in normalization language --
it is composed of multiple component parts, and such values are most often
'harder to work with'. It's usually simpler to store each of the component
parts separately and construct the multi-part value when needed for display
to humans. Often such a value is a "heritage" definition -- something that
was used with manual procedures, never changed when the procedures were
automated, and nobody ever realized that life could be made simpler by
storing the component parts separately. If not a "heritage" defintion, then
it would be a "heritage" idea, from the mind of someone whose thinking is
"locked in the past" (the human reading it should be able to determine
multiple items of information from a single code).

 

[Dan]

Instead of giving us code that doesn't work, could you describe verbally
what you have, and what you want?  Examples are not sufficient as a
definition (e.g., "starts from E1685 and finishes at E2504") ... I might
assume what you want is "the character 'E' prepended to a
monotonically-increasing integer", but obviously from the function you show,
it is not that simple.

What is clear is that the value is not "atomic" in normalization language--  
it is composed of multiple component parts, and such values are most often
'harder to work with'.  It's usually simpler to store each of the component
parts separately and construct the multi-part value when needed for display
to humans.  Often such a value is a "heritage" definition -- something that
was used with manual procedures, never changed when the procedures were
automated, and nobody ever realized that life could be made simpler by
storing the component parts separately.  If not a "heritage" defintion,then
it would be a "heritage" idea, from the mind of someone whose thinking is
"locked in the past" (the human reading it should be able to determine
multiple items of information from a single code).

--
 Larry Linson, Microsoft Office Access MVP
 Co-author: "Microsoft Access Small Business Solutions", published by Wiley
 Access newsgroup support is alive and well in USENET
comp.databases.ms-access

Larry,

Thank you for your reply and explanation,

Basicly the code was designed originally to provide the following
format "Q 0000 YY" - I totally understand your logic using 3 Columns
to separate each component, the problem was I had already built the
database, forms etc and it would of been too pain full to go back at
insert 2
additional columns.

the way the code works is on a input form only, is it looks at the
ProjectQNo Column in tbl_Projects, Determines what the highest Q0000YY
number is? and then inserts the next number into the new record.

I Have recently changed the format of the ProjectQNo to "E0000" In an
Ideal world i should of created a column "Prefix" which Stores E then
ProjectQNo for the number increment, Hover-ever using the access Auto
feature does not give me the flexibility to change the number if its
to be changed.

Quick Solution for me is to store E0000 in one column. which is
automatic generated via a form.

Long term,

I would like to be able to create a table_prefix which stores the
format of the code so in the future if the ProjectQNo format is
changed it would be simple to change.

Regards

Dan