Append,Incrementing number problem

[headintheclouds]

Hi All

Hope you can help, I have been looking on the forum but with no luck. I am trying to design an append query that adds any number of records to another table but need one of the fields to increment by one for each record, so for example;

I append 10 records and the other field increments from 060,061,062 etc for each record

Thanks

HITC

 

[MStef]

Try to do this with DMAX function (Dmax + 1).

 

[headintheclouds]

I am using Dmax + 1 at the moment but if I append say 10 records in one go it won't work it just appears as a blank field.

 

[gemma-the-husky]

you could add an autonumber to your table, and use that - it should work the same.

However, when you say your field is 060, 061, 062 etc, is your field TEXT or NUMERIC. Numbers would not contain the leading zero, as you report. On the other hand you can't add 1 to a text field.

 

[headintheclouds]

I would use autonumber but I don't want the user to abandon/cancel an append query and the sequence going out of alignment.

With regards to the field this is numeric, I have a unbound textbox on the form that picks up the number and adds a leading zero.

Below is what I have picked from the web, this nearly works it increments the number by one but to all records at once e.g.
If I append 10 records it gives them the number 60 to all (which is the next number in line but does not increment for each of the 10 records)


(Nz(DMax("CARRef","tblCorrectiveActionLog"),0)+1)

 

[MStef]

Look at "DemoDMaxA2000.mdb".
Look at Table1, Query2AE. Adapt this on your task.

 

[headintheclouds]

Still only increments by one on all records at once so they still all have 60 in the field. The above works if I append each record one at a time

 

[MStef]

Try to implement this in step before query, (from where you
take data for query).

 

[headintheclouds]

What do you mean?