Record Number in Query

  • Thread starter Jackson via AccessMonster.com
  • Start date
J

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.
 
O

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
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top