Append Query Problem

  • Thread starter Thread starter Dave Evans
  • Start date Start date
D

Dave Evans

OK let me see if I can explain this properly.

I have a number of rows that I want to append to a table.

In one of the columns of the table I would like to append a sequential
number (the next highest number).

I have tried to use the following to append to Table1

DMax("[REF]","Table1") + 1 i.e. find the highest value of [REF] and add 1

but this will append one row only and say the next is a duplicate [REF]

can anybody help me out here. I do not want to change the field to
AutoNumber

Thanks in advance
 
The problem is that the expression DMax("[REF]","Table1")
+ 1 is only evaluated *once* so all the rows to be added
will have the same value for this Field.

Suggest you use Recordset in VBA code where you can add
Records one-by-one sequentially so that you can increment
this Field value.

HTH
Van T. Dinh
MVP (Access)
 
I'm a bit of a novice with VBA!
Can somebody give me some clues it how to start?

Van T. Dinh said:
The problem is that the expression DMax("[REF]","Table1")
+ 1 is only evaluated *once* so all the rows to be added
will have the same value for this Field.

Suggest you use Recordset in VBA code where you can add
Records one-by-one sequentially so that you can increment
this Field value.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
OK let me see if I can explain this properly.

I have a number of rows that I want to append to a table.

In one of the columns of the table I would like to append a sequential
number (the next highest number).

I have tried to use the following to append to Table1

DMax("[REF]","Table1") + 1 i.e. find the highest value of [REF] and add 1

but this will append one row only and say the next is a duplicate [REF]

can anybody help me out here. I do not want to change the field to
AutoNumber

Thanks in advance



.
Click to expand...
Click to expand...
 
Check Access VB Help on the AddNew Method of the (DAO) Recordset object.
There is code sample on how to use AddNew Help topic.

Most Access VBA book will have more info. on the above.
 
Back
Top