How can i customize Autonumbers in Access?

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.
 
K

Ken Snell \(MVP\)

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>
 
Q

Qutub Saifuddin

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
 
D

DaveT

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
 
K

Ken Snell \(MVP\)

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top