How to Convert Autonumber to Text

[Guest]

I have: A form with FieldA as an autonumber.

I want: When a new record is created, FieldB will be populated with a
3-character text equivalent of the autonumber. (e.g., 5 -> 005, 12 -> 012,
136 -> 136).

 

[Marshall Barton]

I have: A form with FieldA as an autonumber.

I want: When a new record is created, FieldB will be populated with a
3-character text equivalent of the autonumber. (e.g., 5 -> 005, 12 -> 012,
136 -> 136).

Why? There is no reason to save the same information twice.
You can display the autonumber field with leading zeros
simply be formatting it.

Note that displaying an autonumber field to users is almost
always a really bad idea.

 

[Guest]

I have: A form with FieldA as an autonumber.

I want: When a new record is created, FieldB will be populated with a
3-character text equivalent of the autonumber. (e.g., 5 -> 005, 12 -> 012,
136 -> 136).

An alternative question is: How do you create a three-character text field
that auto-increments each time a new record is created?

 

[Pieter Wijnen]

Keep it Numeric & Use the Format Property to Display it

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Long
i = Nz(Access.DMax("MyID","MyTable"),0) +1
Me.MyID.Value = i
End Sub

HtH

Pieter