Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query to make a table with Autonumbered records how?

Status
Not open for further replies.

Penrodcigar

Technical User
Aug 23, 2006
19
US
How can I write a make a table query that will add an autonumbered field to the new table ?

DoCmd.RunSQL "SELECT Inventory.Face, Inventory.Price, Inventory.Quantity INTO Autonumber FROM Inventory WHERE (((Inventory.Face)="xxx"))"

This DoCmd will produce a table called Autonumber with records based on criteria "XXX", but how do I get it to add a field to the table with a unique autonumber for each record ie 1,2,3,4 etc?
 
How are ya Penrodcigar . . .

I don't think you can do that with a make table query. TableDefs or DDL Alter Table should handle that. Following is an example using tabledefs ...

Code:
[blue]   Dim tdf As DAO.TableDef, fld As DAO.Field, idx As DAO.Index
   
   Set tdf = CurrentDb.CreateTableDef("[purple][b]tblTest[/b][/purple]")
   Set fld = tdf.CreateField("[purple][b]testID[/b][/purple]", dbLong)
   
   fld.Attributes = dbAutoIncrField
   tdf.Fields.Append fld
   
   Set idx = tdf.CreateIndex("[purple][b]tblTest[/b][/purple]_[purple][b]testID[/b][/purple]")
   idx.Primary = True
   idx.Unique = True
   
   Set fld = idx.CreateField("[purple][b]testID[/b][/purple]")
   idx.Fields.Append fld
   tdf.Indexes.Append idx
   CurrentDb.TableDefs.Append tdf
   
   Set fld = Nothing
   Set idx = Nothing
   Set tdf = Nothing
   
   Application.RefreshDatabaseWindow[/blue]

I don't remember how to use DDL Alter Table so I'll have to look that up. I do remember its simple.

Your Thoughts ...

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
Thanks AceMan,

I am self taught (Access for Dummies) and wasn't aware of either command you mention. But will try them!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top