Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 304418 - Last Review: January 31, 2007 - Revision: 5.5
How to programmatically create an AutoNumber field and set its "New Values" property to Random
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).
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.
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("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.
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().
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.
APPLIES TO
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
| kbdesign kbdatabase kbprogramming kbhowto KB304418 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate