Append query to duplicate 1 record

[smayshar]

I have a table with a field which is primery key (no duplicate), data type = number. I use this field as the records ID , insted of AutoNumber.
My question is : Can I run Append Query to add one record ? how do increase the number of this field by one ?

 

[formerTexan]

Hi

Are you trying to set the numbers in this field sequencially? If something like the followng will work (don't forget error trapping). Make sure your field's data type is set to double, rather than integer. You can also change to ADO if needed. Also take some time and read some of the threads on the use of primary keys. An incrementing number may not be the best arrangement.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim lngID as long
Set dbs = CurrentDb

strSQL = "SELECT TOP 1 fieldID FROM tblYourTable"

Set rst = dbs.OpenRecordset(strSQL)
lngID = rst!fieldID + 1

'THEN PASS the variable value to your new record

rst.close
dbs.close
Set dbs = Nothing
Set rst = Nothing

 

[smayshar]

thenks for the help
I tried to make it with an append query,with no success,
probebly VBA is the way

 

[PHV]

Have you tried something like 1+DMax("[record ID field]", "[table name]")

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

 

[smayshar]

Where do I put the 1+DMax("[record ID field]", "[table name]") in the Append Query
I can't manage to do it