Auto number a field in Make table query 1

[MrBillSC]

Access 2000

I have a "make table" query that contains a field called "Line_number". The input table for the query does not contain a value for this field. I would like it to number the records sequentially, but can find no way to do this.

If I delete the records in the output table and make the Line_number field in the output table definition "auto number", the "auto number" changes to "number" with the "make table" query.

If I change the make table query to an append query, the auto number field will not reset to 1 each time the table is erased and appended.

I can find no way to make an "auto number" column in a "make table" query nor can I find a way to reset the beginning number of an auto number field in a table to 1 when erasing and appending new records.

If anyone has any ideas, they will be greatly appreciated.

thanks,

MrBill

 

[PHV]

reset the beginning number of an auto number field in a table to 1
ALTER TABLE yourTable ALTER COLUMN yourAutoNumber COUNTER(1,1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MrBillSC]

PH,

Thanks for the response.
I am running my 'delete query' and 'append query' from a screen via a command button. In the code for the command button I now have the following:

DoCmd.OpenQuery "QryClearTblExportToEden"
Alter Table TblExportToEden Alter Column Line_no Counter(1,1)
DoCmd.OpenQuery "QryAppendEdenChargesDetail"

I am getting a "syntax error" message on the 'Alter Table' line. Can you help me out?

Thanks again to anyone that can help.

MrBill

 

[PHV]

DoCmd.RunSQL "Alter Table TblExportToEden Alter Column Line_no Counter(1,1);"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MrBillSC]

Thanks again PH. It worked like a charm!

MrBill