Passing values from one form & sequential numbering

[PricePS]

Having not worked in Access for years and, having casual skills at the best, I’ve managed to build several business useful dBs over the years. Presently I am working on a project that has presented me with a problem that has me baffled. I have two forms: fJobHeader based on a query joining tables JobHeader (key JobNo an AutoNumber) and Customers (key an AutoNumber field – CustNo) (the later table is included simply to populate names associated with the Customer No). The second form fJobLines is based on a table JobLines (key is JobNo & LineNo) and is presented in continuous forms format.


My problem is that I want to start each job line set with line number 1 and add sequential lines thereafter. My intention is to open fJobLines from fJobHeader - if there are no lines associated the first JobLine should be line 1. If there are already JobLines associated a new record would have the JobNo automatically inserted and, the LineNo would be sequential from the last record.
I can make this all work if there are existing JobLine records but run into issues on making it work on new record sets. Additionally, my solution is not very elegant, using 4 unbound text boxes and programmatically assigning values in a sequence of events. While I can make it work, I don’t have faith that it is the best way to get where I am going and will prove unstable for practical use.

Any suggestions?...Anyone??

Thanks,
Paul

[pbaldy]

Generally speaking, this type of thing can be done by adapting the DMax at the bottom here:

http://www.baldyweb.com/CustomAutonumber.htm

[June7]

What is purpose of this LineNo field? A sequential number for each job's line item records could be generated in a report. Textbox on report has a RunningSum property that can accomplish this.

If you must save the value, sequential number is a common topic in forum. Start with review of: https://www.accessforums.net/access/...ers-15787.html

[PricePS]

The user needs a way to refer to/ communicate regarding multiple line items associated with a single job when looking at the form. I could use an autonumber field on the table level but a new job having a starting line number of...1024 wouldn't read right. I am not particularly concerned with some of the issues regarding sequencing, even "uniqueness" of the autonumber...just the appearance of a job starting with a line item other than 1. Does this make sense?


The topics I've seen in the forum don't seem to address the sequential numbering in quite the same set of circumstances I am looking at...then again, being new to the forum I may be missing it.


Thanks for the response!

[June7]

Both links referenced offer code that should be adaptable to your requirements if you want to save the generated line number in table. Otherwise, review http://www.lebans.com/rownumber.htm

[PricePS]

Thanks! I went through the referenced links and found I more or less am approaching my specific set of issues with similar methodology. So I went through the forms in question and found my real issue was another bit of dazzling manic "wanna be" programer code triggered on On Current - moving it to Before Insert seems to have stabilised the issue. My solution would not work in a multiple user environment but not an issue in this particular case.

Again thanks for taking the time to help!