Autonumber help in form

[Kim Webb]

Last January this group helped me come up with an event procedure that
automatically assigns project numbers in my form.

I use this in the BEFOREUPDATE event procedure:

Dim strYear As String
strYear = Format(Date, "yy")
Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Right(ProjectID,3))", _
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) +1, "000")

So project number look like this 06xxx.

We are now at project 06999 so I need the next one to be 061001 and thn
061002, 061003 etc

I took the above and changed it to:

Dim strYear As String
strYear = Format(Date, "yy")
Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Right(ProjectID,4))", _ {changes 3 to 4}
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) +1, "0000") {changed 000 to 0000}

I thin went in to the table and made the next project 061000. When I
go in to the form and enter the data I get this error:

the object does not contain the automation object "projects"


Many thanks for any assistance

 

[Guest]

You will need to update all the ProjectID fields in your table for the new
format. The ones that had only 3 characters for the number have to be
changed to for. For example, 060001 through 06999 have to be changed to
0600001 through 060999.

 

[Kim Webb]

That just won't be possible. Is there another work around?

The project number are number, years from now I'll go back and look for
project 06999 not 060999.

I noticed the formula below I fixed is assigning 067000 as the next
project instead of 061000.

 

[Rick Brandt]

That just won't be possible. Is there another work around?

The project number are number, years from now I'll go back and look
for project 06999 not 060999.

I noticed the formula below I fixed is assigning 067000 as the next
project instead of 061000.

You made a bad decision not to store this in two separate fields and then
combine them for display. Now you are being stung by that bad decision. My
advice is to do the split now and simplify everything.

 

[Guest]

If you have already created 061000, then it should assign the new numbers
okay. If you don't change the old numbers, you may have problems looking up
the old numbers in the future, depending on how you do your search. At some
point in time, I would suspect the differences might become a problem.

 

[Guest]

Rick is correct.

That just won't be possible. Is there another work around?

The project number are number, years from now I'll go back and look for
project 06999 not 060999.

I noticed the formula below I fixed is assigning 067000 as the next
project instead of 061000.

 

[Kim Webb]

I have created 061000, I also have 061001 and 061001

When the EVENTPROCEDURE runs it gives me 067000 as the next project
number:

Dim strYear As String
strYear = Format(Date, "yy")
Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Right(ProjectID,4))", _
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) + 1, "0000")

 

[Kim Webb]

Since everything is linked to the 06xxx system of numbering I don't
know how I could have changed it to be two fields. Everything links to
the project number everwhere in the database and this system has been
in place so 1991 so I did not create it originally.

Thanks.

 

[Douglas J. Steele]

The statement could have been changed to

Me.ProjectID = strYear & _
Format(Nz(DMax("Val(Mid(ProjectID,3))", _
"Projects", "Left(ProjectID,2)='" & strYear & _
"'"), 0) +1, "0000")

and it shouldn't have problems with any possible value.