Generate line no within each Sales Order

Hi,

I would like to generate a line no. before inserting records of different sales order into Access
database.

For example,
if there are 2 items in order no. 1, then I should have the following in the table:

Order no line no item
-----------------------------
Ord1 1 item1
Ord1 2 item2

How to achieve this?


Thanks a lot!

If you have a line number column in your database table just put the line number value in it. Am I missing something?

Yes, I have a line no. column...but I should get the current line no. in which event? the "before insert"??

Because I have set the Order No. and Line No. as primary key, I cannot insert the record before having a proper line no.

I wouldn't put line number as part of a primary key myself. If you do you won't be able to re-arrange the lines if necessary.

You can use SELECT Max(linenumber) AS LineNo FROM table, which will put the highest line number in rs("LineNo"). Add 1