can i add a prefix to an autonumber text box in access 2003? how?

  • Thread starter Thread starter Guest
  • Start date Start date
Hi discocyst,

You can set this in the Format property of the field but it wont' be in the
data, only in the VIEW of the data. You can create a new field to
concatinate the autonumber with a prefix. Or if you are going to use this
Autonumber as something like an account number, you may not want to use the
autonumber function, as this may not always give you sequential numbers.
Instead you would want to increment and ID field, then concatinate that
field with the prefix via code.

For example, I have a form to add new records, and need to sequence the
Report Numebrs, and add a prefix of "UNREP-" On that forms on On Open event,
I have the following:

Private Sub Form_Open(Cancel As Integer)
Dim cnx As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sq As String
Dim intID As Integer
Dim n As String
Set cnx = CurrentProject.Connection

sq = "SELECT * FROM tblUNREP"
rs.Open sq, cnx, adOpenKeyset, adLockOptimistic
'Verify that records exist, and get max of field ID
If rs.RecordCount = 0 Then
intID = 1
Else
intID = DMax("ID", "tblUNREP") + 1
End If
'Add prefix and format numeric part of RepNum to 3 digits
n = "UNREP-" & Format(intID, "000")
'Populate unbound fields in the form
Me.txtID = intID
Me.txtRepNum = n
'Cleanup
rs.Close
cnx.Close
End Sub


Then the save button populates the table "tblUNREP" via SQL, and give the
user the option to add more records:


Private Sub btnSave_Click()
DoCmd.SetWarnings False
Dim cnx As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim intID As Integer
Dim n As String
Dim r As String
Dim i As Integer
Dim t As String
Dim rsp As String
'Verify Title field is filled in
If IsNull(Me.txtTitle) Or Me.txtTitle = "" Then
MsgBox "Please enter a Title!"
Exit Sub
Else: GoTo Save_Rec
End If

Save_Rec:
r = Me.txtRepNum
i = Me.txtID
t = UCase(Me.txtTitle)

sq = "INSERT INTO tblUNREP (RepNum,ID,Title) " _
& "VALUES ('" & r & "', '" & i & "', '" & t & "')"
DoCmd.RunSQL sq
rsp = MsgBox("Add Another UNREP Equipment?", vbYesNo, "Add UNREP?")
If rsp = vbYes Then
'Reset form to new Repnum etc
sq = "SELECT * FROM tblUNREP"
rs.Open sq, cnx, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then
intID = 1
Else
intID = DMax("ID", "tblUNREP") + 1
End If
n = "UNREP-" & Format(intID, "000")
Me.txtRepNum = n
Me.txtID = intID
Me.txtTitle = ""
Form.Refresh
rs.Close
cnx.Close
Else
DoCmd.OpenForm "frmUNREP"
DoCmd.Close acForm, "frmAddUNREP", acSaveNo
End If
DoCmd.SetWarnings True
End Sub
 
For what purpose?

Autonumbers are just that: numbers, so you can't modify them to include
letters.

If you want the same letter on all of them, you could specify a format for
the number that includes the letter at the beginning. The letter wouldn't
actually be stored, but it would look as though it was. (That, of course,
can cause problems later on if you're trying to join data not realizing
what's actually stored in the field)

A better alternative is to store the letter as a separate field in the
table. You could then create a query that concatenates the two fields for
display purposes, and use the query wherever you would otherwise have used
the table.

Realistically, though, autonumber fields should never have meaning assigned
to them. It's rare, in fact, that anyone even see their value.
 
Back
Top