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