Counter field in MS Access, how to generate?

Question

How can I generate counter field like this 0001A, 0002A... becouse in standart it is 0,1,2,3,4.... how to change this?

Answer 1

The simplest solution would be to use a standard autonumber field (long integer). Let Access maintain those values for you. Then anytime you need those values in your "0001A" format, use the Format() function to add the leading zeros, and concatenate an "A".

This is trivially easy. If your autonumber field is named ID, you could do that transformation with this query:

SELECT Format(ID, "0000") & "A" AS formatted_ID
FROM YourTable;

Similarly you can apply the same expression to the control source property of a text box on a form or report.

Answer 2

Adding to @HansUp's excellent answer, you could hide the IDENTITY column and at the same time expose the formatted column using a SQL VIEW: you could then revoke privileges on the table so that users work with the VIEW and do not 'see' the table e.g. demo:

copy+paste into any VBA module, no references nor Access UI/object model required, creates a new mdb in the temp folder e.g. use Excel:

Sub YourView2()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")

  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    With .ActiveConnection

      Dim Sql As String

      Sql = _
      "CREATE TABLE YourTable ( " & _
      "ID INTEGER IDENTITY(1, 1) NOT NULL UNIQUE, " & _
      "data_col VARCHAR(20) NOT NULL);"
      .Execute Sql

      Sql = _
      "CREATE VIEW YourView AS " & _
      "SELECT FORMAT$(ID, '0000') & 'A' AS formatted_ID, " & vbCr & _
      "       data_col " & vbCr & _
      "  FROM YourTable;"
      .Execute Sql

      Sql = _
      "INSERT INTO YourView (data_col) VALUES ('one');"
      .Execute Sql      
      Sql = _
      "INSERT INTO YourView (data_col) VALUES ('day');"
      .Execute Sql      
      Sql = _
      "INSERT INTO YourView (data_col) VALUES ('when');"
      .Execute Sql      

      Sql = "SELECT * FROM YourView;"

      Dim rs
      Set rs = .Execute(Sql)
      MsgBox rs.GetString

    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

---

I think this one would be even better if you include DDL GRANT/REVOKE samples to manage the privileges

Here's the updated code to do just that:

Sub YourView2()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  Kill Environ$("temp") & "\DropMeToo.mdw"
  On Error GoTo 0

  ' Create workgroup and db
  Dim cat As ADOX.Catalog
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Jet OLEDB:Engine Type=4;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMeToo.mdw;" & _
        "Jet OLEDB:Create System Database=-1"
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Jet OLEDB:Engine Type=4;" & _
      "Data Source=" & _
      Environ$("temp") & "\DropMe.mdb;" & _
      "Jet OLEDB:System Database=" & _
      Environ$("temp") & "\DropMeToo.mdw;"

    ' Add table with data and user with privileges
    With .ActiveConnection

      Dim Sql As String

      Sql = _
      "CREATE TABLE YourTable ( " & _
      "ID INTEGER IDENTITY(1, 1) NOT NULL UNIQUE, " & _
      "data_col VARCHAR(20) NOT NULL);"
      .Execute Sql

      Sql = _
      "CREATE VIEW YourView AS " & _
      "SELECT FORMAT$(ID, '0000') & 'A' AS formatted_ID, " & vbCr & _
      "       data_col " & vbCr & _
      "  FROM YourTable WITH OWNERACCESS OPTION;"
      .Execute Sql

      .Execute "CREATE USER onedaywhen pwd Chri5tma5;"
      .Execute "GRANT ALL PRIVILEGES ON YourView TO onedaywhen;"
      .Execute "REVOKE ALL PRIVILEGES ON YourTable FROM onedaywhen;"

    End With
  End With

  ' Test user can connect
  Dim con As ADODB.Connection
  Set con = New ADODB.Connection
  With con
    .ConnectionString = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Jet OLEDB:Engine Type=4;" & _
      "Data Source=" & _
      Environ$("temp") & "\DropMe.mdb;" & _
      "Jet OLEDB:System Database=" & _
      Environ$("temp") & "\DropMeToo.mdw;" & _
      "User ID=onedaywhen;Password=pwd;"
    .Open

    On Error Resume Next

    ' Attempt to insert to table (no privileges)
    Sql = _
    "INSERT INTO YourTable (data_col) VALUES ('one');"
    .Execute Sql
    If Err.Number <> 0 Then
      MsgBox _
          Err.Number & ": " & _
          Err.Description & _
          " (" & Err.Source & ")"
    End If
    On Error GoTo 0

    Dim rs

    On Error Resume Next

    ' Attempt to read table (no privileges)
    Sql = _
    "SELECT * FROM YourTable;"
    Set rs = .Execute(Sql)
    If Err.Number <> 0 Then
      MsgBox _
          Err.Number & ": " & _
          Err.Description & _
          " (" & Err.Source & ")"
    End If
    On Error GoTo 0

    ' From here, work only with VIEW
    Sql = _
    "INSERT INTO YourView (data_col) VALUES ('one');"
    .Execute Sql

    Sql = _
    "INSERT INTO YourView (data_col) VALUES ('day');"
    .Execute Sql

    Sql = _
    "INSERT INTO YourView (data_col) VALUES ('when');"
    .Execute Sql

    Sql = "SELECT * FROM YourView;"

    Set rs = .Execute(Sql)
    MsgBox rs.GetString

    Set con = Nothing
  End With
End Sub

Answer 3

One solution, that works for forms only!

1. create a GetId() function that calculates your counter (using DMax generally)
2. Use the Before insert event in your form to set the value of the field using GetId()

Drawback: in a multiuser environment, if another User2 starts addind a record after User1, but saves it before User1 saves his, there will be a duplicate problem. You will need to use the FormError event to regenerate the ID and resume the save process.