Query to make a table with Autonumbered records how?

[Penrodcigar]

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?

 

[TheAceMan1]

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

[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!
Also FAQ181-2886 Worthy Reading!

 

[Penrodcigar]

Thanks AceMan,

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