Appending Records Using DMAX

[Jeff G]

All -

This should be fairly easy...I'm running and Append Query to add records to
a table. One of the columns is a "control" field (numeric). When I run the
Append Query, I want to increment the "control" field by one (using DMAX).
When I do, it assigns the same number to all records appended.

How do I fix it so that each record has a unique "control" number?

Thanks.

Jeff

 

[Michel Walsh]

Yes, that is how Jet works in this circumstance (and that, even if your DMax
depends on the value of a field of the actual record).

You can either add one record at a time (slow), either use a driver table
(say a table Iotas, one field, iota, the primary key, with values from 0 to,
say 999) :


SELECT a.id, DMax("whatever", "somewhere") + iotas.iota + 1
FROM somewhere , iotas
WHERE iota < DCount("*", "somewhere", "id=" & someID )
AND id= someID



as example select "N" records with DMAX + ( 1, 2, ... , N) for the
computed expression.




Vanderghast, Access MVP

 

[Michel Walsh]

Last time I checked this trick, which works in many cases, it fails for that
specific case, probably because the DMax in the VBA function operates
outside some internal transaction occurring while the append is performed.
There is such a transaction since at the end, you are offer the choice to
undo (RollBack) all the modifications, when an error occur, as example.



Vanderghast, Access MVP

 

[Jeff G]

I don't want to use the use the AutoNumber for 2 reasons:

1. I want to be able to control the numbers (10 digits).
2. I don't want gaps if a record is deleted.

What I need to be able to do is this...

Append multiple records and have the VoucherNumber field (10digits)
increment by 1.

Record x gets added to another table with the VoucherNumber of 1000000000
Record y gets added to the table with the VoucherNumber of 1000000001
Record z gets added to the table with the VoucherNumber of 1000000002
etc.

The number of records to be added will vary.

My assumption would be that in order to get sequential numbers with no gaps
is that as part of the appending, use the DMAX function to get the next
highest VoucherNumber.

It doesn't have to be done in a query...it should be able to be done via VBA
using a loop if I'm not mistaken.

 

[Bob Barrows]

All -

This should be fairly easy...I'm running and Append Query to add
records to a table. One of the columns is a "control" field
(numeric). When I run the Append Query, I want to increment the
"control" field by one (using DMAX). When I do, it assigns the same
number to all records appended.

How do I fix it so that each record has a unique "control" number?

Errr ... use an Autonumber field ... ?