Record Number in Query

[Jackson via AccessMonster.com]

Hi,

In my database, my main table's primary key is a composite key of an integer
and a letter. The order entry form assigns Dmax +1 to create the order key
for most records.

However, we import some orders from another system into a seperate table,
records are then appended to the main table. If it's a single record, I can
simply again make the ID field the Dmax + 1. However, if there are two
records being appended, it tries to make both integers the same number. I
can't use autonumber for the field for other reasons.

Is there a way I can make the append query ID field Dmax("ID", "tblOrder") +
(record number)

That way it would sequentially increment by one for each record. I just don't
know how to define the record number.

 

[OfficeDev18 via AccessMonster.com]

Hi, Jackson,

SQL (in this case, your append query) removes some of the control from the
programmer. You won't be able to do this just with the query alone.

The best way to do what you want is to lose the query altogether, and import
the table from the source using VBA code, going from record to record and
processing them one at a time. This, of course, gives you maximum control, as
you can make any changes you want while the record is in transition from
source table to target.

Hope this helps,

Sam

 

[jackwoodhead via AccessMonster.com]

As a workaround, in the table I import to, I added an Autonumber field to
increment by 1. Then when I append, I make the ID Dmax("ID","tblOrders")+(
(tblImport!Autonumber)-Dmin("Autonumber","tblImport","Booked=No")+1)

That way it effectively increments onto tblOrders!ID by one each time,
because I update the import table to booked=yes after appending.

Just thought I'd share...

Cheers.