10666

Counter field in MS Access, how to generate?

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

Answer1:

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

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

Answer2:

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.

Answer3:

One solution, that works for forms only!

<ol> <li>create a GetId() function that calculates your counter (using DMax generally)</li> <li>Use the Before insert event in your form to set the value of the field using GetId()</li> </ol>

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.

Recommend

  • Create table dynamically using Javascript to display a database SELECT query output
  • Utilize ADO.NET within Classic ASP script
  • Select records from a table where all other records with same foreign key have a certain value
  • MySQL group by number series [closed]
  • ADO Command Parameter Not Passing to Stored Procedure or Stored Procedure 'Ignoring' Param
  • getting the range of a number column(min/max) if there are missing numbers in between
  • How to call SQL Server Stored Procedure from within MS Access VBA
  • How can I determine which routines MATLAB uses to solve a sparse matrix?
  • Need code translation from VB to C#
  • Using Laravel 5.4 pusher
  • How to 'create temp table as select' in Slick?
  • Eliminate partial duplicate rows from result set
  • pyodbc doesn't report sql server error
  • Z3: Convert between FP and BitVector?
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • Oledb connection string for excel files
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Nant, Vault & Windows Integrated Authentication
  • PHP - How to update data to MySQL when click a radio button
  • Javascript + PHP Encryption with pidCrypt
  • Convert array of 8 bytes to signed long in C++
  • Websockets service method fails during R startup
  • AT Commands to Send SMS not working in Windows 8.1
  • Arrays break string types in Julia
  • Comma separated Values
  • How to delete a row from a dynamic generate table using jquery?
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Error creating VM instance in Google Compute Engine
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Hits per day in Google Big Query
  • how does django model after text[] in postgresql [duplicate]
  • embed rChart in Markdown
  • How to stop GridView from loading again when I press back button?
  • apache spark aggregate function using min value
  • Sorting a 2D array using the second column C++
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app