Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How to programmatically create an AutoNumber field and set its "New Values" property to Random

View products that this article applies to.


This article demonstrates how to programmatically create an AutoNumber field in a Microsoft Access table and set this field's New Values property to Random.

↑ Back to the top

More information

To programmatically create an AutoNumber field and set the field's New Values property to Random, follow these steps:
  1. Start Microsoft Access.
  2. On the File menu, click New. In the New File task pane, click Blank Database. Type a path and file name for the database, and then click Create.
  3. On the View menu, point to Database Objects, and then click Modules. Click New.
  4. Add the Microsoft DAO 3.6 Object Library to your database's references.
  5. Type the following sample code in the new module:
    Sub CreateRandomAutonumber()
    ' Create database, tabledef, and field objects.
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim f As DAO.Field
    ' Set the database object to the current database.
    ' Set the tabledef object to a new table named Table1.
    ' Set the f (field) object to a new field in Table1 named MyAutoNumber.
    Set db = CurrentDb
    Set td = db.CreateTableDef("Table1")
    Set f = td.CreateField("MyAutoNumber")
    ' Set the type and auto-increment properties for the Table1 field named 
    ' MyAutoNumber.
    f.Type = dbLong
    f.Attributes = dbAutoIncrField
    ' Append the MyAutoNumber field to Table1.
    td.Fields.Append f
    ' Create a new text field in Table1.
    Set f = td.CreateField("MyTextField")
    ' Set the type property for MyTextField.
    f.Type = dbText
    ' Append the MyTextField field to Table1.
    td.Fields.Append f
    ' Append the Table1 tabledef to the database.
    db.TableDefs.Append td
    ' Set the default value for MyAutoNumber to a random number function.
    td.Fields("MyAutoNumber").DefaultValue = "GenUniqueID()"
    ' Refresh the database window.
    End Sub
The sample code does the following:
  • Sets the field's Type property to dbLong and sets the field's Attributes property to dbAutoIncrField.
  • Appends the new TableDef to the TableDefs collection.
  • Sets the DefaultValue property of the field to GenUniqueID().

↑ Back to the top


For more information about the methods used in this article, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type one of the following topics in the Office Assistant of Answer Wizard
  • createtabledef method
  • createfield method
  • append method
and then click Search to view the topic.

↑ Back to the top

Keywords: KB304418, kbhowto, kbprogramming, kbdatabase, kbdesign

↑ Back to the top

Article Info
Article ID : 304418
Revision : 10
Created on : 1/31/2007
Published on : 1/31/2007
Exists online : False
Views : 290