AutoNumber Question

[Rick5150]

Is is possible to have a formatted autonumber field that can be edited?

I am tracking jackets, and the format of the current design is LLLL-NNNN where L is the prefix letters and N is the auto number.

However, if the style is simply a slight variation of the original pattern, we designate this by adding a lower case suffix.

So if the original pattern was ABCD-0123, the 1st variation of the design would be ABCD-0123a, and the second would be ABCD-0123b, etc.

I am afraid I will have to enter this all manually as there is not a way to overwrite an auto field.

Any suggestions?

[RuralGuy]

Maybe this link will help: http://www.baldyweb.com/CustomAutonumber.htm

[Rick5150]

Too confusing for me, sorry. Let's stay with a standard 8-digit number.

Table is named "Customers"
Field is named "Customer #"

There are some lengthy expressions, but I cannot understand them. I did try this one, but it does not work:

Private Sub Form_BeforeInsert(Cancel As Integer)
Customer_# = Nz(DMax("Customer_#", "Customers")) + 1
End Sub

[RuralGuy]

You could eliminate some of your problems by not using any special characters in names: http://support.microsoft.com/?id=826763

[Rick5150]

Thank you. I have since removed all special characters and separated all blank spaces with underscores.

[RuralGuy]

Did you get your DMax() to work?

[Rick5150]

No. I had no luck whatsoever getting the numbering to work.

[RuralGuy]

What do you now have for this:
Customer_# = Nz(DMax("Customer_#", "Customers")) + 1

[Rick5150]

Customer_No = Nz(DMax("Customer_No", "Customers")) + 1

[RuralGuy]

Have you single stepped or put a diagnostic MsgBox in it to see what is returned?