Microsoft KB Archive/304418

= How to programmatically create an AutoNumber field and set its &quot;New Values&quot; property to Random =

Article ID: 304418

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q304418



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
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.



MORE INFORMATION
To programmatically create an AutoNumber field and set the field's New Values property to Random, follow these steps:  Start Microsoft Access. 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. On the View menu, point to Database Objects, and then click Modules. Click New. Add the Microsoft DAO 3.6 Object Library to your database's references.  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(&quot;Table1&quot;) Set f = td.CreateField(&quot;MyAutoNumber&quot;)

' 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(&quot;MyTextField&quot;)

' 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(&quot;MyAutoNumber&quot;).DefaultValue = &quot;GenUniqueID&quot;

' Refresh the database window. Application.RefreshDatabaseWindow

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.

<div class="references_section">