Create Autonumber Field with VBA For MS Access 2000, 2002, 2003, 2007Actual VBA Code and Sample at the bottom of the page. Follow the Autonumber Link to create one in Design View (manually).
This example came about from a need to create an Autonumber field in a MS Access Database. The record count was somewhere around 200k-300k. I went to the Tables design view and created an Autonumber field. That was the smooth part. To create different fields in VBA see the Create Field in VBA example.
I then proceeded to close and save the table. It was then that MS Access gave me the dreaded "Not Enough Space on Temporary Disk" error.
I really needed the Autonumber field so I manually created a table with similar fields, Set my Autonumber field and then Appended my data to it. To much work of course but a valid work around none the less. I then researched and made a faster reusable method for adding the Autonumber field via VBA inside a MS Access Form.
I have made the download for this VBA code available here In a sample MS Access database. The VBA uses both a Form and a Module. The VBA code targets one empty table. Click the picture below to see a larger version.
Usage is free of course. Actual Usage: Simply Drop the module and Form in any MS access database with a Table named "1A". You can right click tables and rename them. Click the forms Menu and then double click the AUTONUMBER_FIELD Form. You will be presented with a grey form with a Button named Execute. Click the button and ignore the "Not Enough Space on Temporary Disk" error as you create your custom Autonumber.
If the fields the form targets has already been played with the Form is designed to throw an error. These errors are just to let you know something is amiss. Then it will complete the process as the above picture illustrates.
The Acual VBA Code for the Create Autonumber Field, you should be able to copy/paste right into a module:
Function CreateAutoNumberField( _
ByVal strTableName As String , _
ByVal strFieldName As String ) _
'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
'Set references by Clicking Tools and Then References in the Code View window
'Creates an Autonumber field
' strTableName : Name of table in which to create the field
' strFieldName : Name of the new field
'Returns True on success, false otherwise
'USAGE: CreateAutoNumberField "TABLENAME", "FIELDNAME"
On Error GoTo errhandler
Dim Db As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Set Db = Application.CurrentDb
Set tdf = Db.TableDefs(strTableName)
' First create a field with datatype = Long Integer
Set fld = tdf.CreateField(strFieldName, dbLong)
' Appending dbAutoIncrField to Attributes
' tells Jet that it's an Autonumber field
.Attributes = .Attributes Or dbAutoIncrField
CreateAutoNumberField = True
Set fld = Nothing
Set tdf = Nothing
Set Db = Nothing
MsgBox "Autonumber Complete"
CreateAutoNumberField = False
MsgBox "Error " & .Number & vbCrLf & .Description, _
vbOKOnly Or vbCritical, "CreateAutonumberField"
For the above and a few other functions download the Sample.
You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.
For further MS Access tutorials go here: MS Access tutorials
To Create an Autonumber field manually go here.
|Did you find what you were looking for?
What would you suggest?