Calculate next 5 numbers from table field

[Domino]

I have a small problem.

I created a small append query that works very well if there is only one
row. But it has a problem if there is more then one row.

To explain... I have a field in the query that gets the MAX value from a
table field (the same table it appends to) and adds 1 to it.

So if the MAX value is 10 then it calculates 11.

But if there are 5 rows it calculates value 11 for every row so I get 5
duplicate values.

Is there anything I can do to fix this ? (except AutoNumber)

 

[Allen Browne]

You will need to append the records one at a time, e.g. 5 append queries, or
OpenRecordset and AddNew (with Update.)

 

[John W. Vinson]

I have a small problem.

I created a small append query that works very well if there is only one
row. But it has a problem if there is more then one row.

To explain... I have a field in the query that gets the MAX value from a
table field (the same table it appends to) and adds 1 to it.

So if the MAX value is 10 then it calculates 11.

But if there are 5 rows it calculates value 11 for every row so I get 5
duplicate values.

Is there anything I can do to fix this ? (except AutoNumber)

In addition to Allen's suggestions, you can make use of a handy little
auxiliary table. I use a table named Num with one primary key long integer
field N, manually filled with values 0 through 10000. Your Max query can be
based on Num with a criterion to select only values of N from 1 through the
number of records you want to add, and insert N+your max value into the target
table.

John W. Vinson [MVP]