Increment Number in Table/Column Value

[rodrunner79]

The fact:
Running an Access 2007 database front-end and SQL backend where tables are linked via DSNLESS ODBC.

The problem:
I have to generate a order line number in a query for each PO/order line in one query and derive the starting line number from another table. For example, I have a table named POHeader. And the records looks like this:

PONumber/LastLineNumber
PO123/293
PO567/50
PO411/23

PO123 indicates that 293 lines have been ordered, while PO567 indicates 50 lines have been ordered. Users can add items to the POs anytime.

The query I have that produces the lines need to call a function where I pass the PO number and in that function then it looks up the LastLineNumber for that PO and increments it by 1 for every line that uses the PO.

My query results may return a combination of those 3 (or more) POs. Example, I may have 100 line items in the query and those 100 lines can span multiple POs.

Also, after the function generates the last auto-number for the last line on the PO, I want it to update the POHeader with that last line number so that it can be called later and incremented.

Can't means won't try.

 

[Blorf]

Is the "LastLineNumber" just a count of lines in the PODetail table for that PO? If so, then a Dcount would do to trick. If not, please add more info.

Thank you,
Charles Smith

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[randy700]

How about the MAX function?


Randy