How can i customize Autonumbers in Access?

  • Thread starter Thread starter Qutub Saifuddin
  • Start date Start date
Q

Qutub Saifuddin

I want to customize an autonumber, so that the autonumber generates a
standard set of charecters before the numbering. For example:

My CustomerID Field generates autonumbers from 1. However I would like it to
generate in such a way that the following occuers:

CUS1
CUS2
CUS3 etc.

I was told that i would need visual basic programing to do this. I would
appreciate some help in this.
 
You can't customize an autonumber field.

Do not store the "CUSx" value in a single field. Instead, use two fields --
the autonumber one for the "x" part, and one for the "CUS" part. Then
concatenate the two fields with a hyphen when you want to display the
result:

SELECT FieldCUS & Fieldx AS CustomerNumber
FROM TableName;

--

Ken Snell
<MS ACCESS MVP>
 
Dear Ken,

Thank you for your reply, but doesn't it act more as a Prefix. It does not
neccessarily need to store, but as a user can't i put an expression on the
forms and reports so that the whole "CusXX" can be seen?

I was researching and found an expression which would allow me to do this, i
tried it out, but it wasn't working with me.

Thank you once again
 
As Ken notes, autonumber generates numbers (long integer).

In your case, for forms and reports you can use a text box and set its
control source to:

="CUST" & CStr(Format([CustomerNumber],"0000"))

The user sees CUST0001, CUST0002, etc

Set the text box to Enabled = False, Locked = True
 
I'm not understanding your followup question, I'm sorry. Can you show us the
expression that you found, and show us some more specific examples of what
you want the user to type in / use as the data/prefix, etc?
 
Back
Top