Sequential Numbering of Freight Bills or Invoices, with Multiple Numbers per Record.

[ProvPC]

Holy smokes, it took a silly long time to try to come up with a subject line to explain what I want to do.

I'm working on an bid process form for a trucking company. The process starts out with a Bid Control Form, in which the relevant load details are entered, margins are calculated and the bid is automatically marked as "Pending".

The bid is then submitted to the parties requesting it, and sits idle until it is accepted or rejected (often months later). The user then marks the bid as "Accepted" or "Rejected", at which time one or more Freight Bills are generated for the bid. I'm looking for a clean way to generate the Freight Bill numbers that allows the user to determine how many freight bills need to be generated.

For example, if the company is transporting a single wide mobile home, then a single freight bill would need to be generated. If it's a triple-wide, three sequential numbers would need to be generated. And if they're relocating a mobile-home dealership from one property to another, they might need 100 freight bills. In any case, all of the freight bills would need to be "tied" to the bid number generated on the original Bid Control Form.

It gets slightly more complex. There are multiple terminals involved. The terminals need to be identified as part of the freight bill number. Currently, the terminals are designated by their state and city (AL-BIRMINGHAM, or ID-BOISE, etc), so the freight bill should incorporate that into the freight bill number. If a terminal in Spokane, Washington creates a bid to ship a triple-wide, the user should be able to generate and print three freight bills with sequential numbers, such as WA-SPOKANE5022, WA-SPOKANE5023 and WA-SPOKANE5024 (The terminal designation is part of the bid control form and table).

Ideally, what I'd like is for the user to mark the bid as "accepted" then be able to type in how many freight bills are required and push a button to generate that many freight bills into a table, where they are tied to the single original bid control number. From there, I should be able to create a method of selecting them for printing when the time arises.



Or, if I'm barking up the wrong tree, and someone has a better idea, I'm all ears!

[June7]

Generating custom unique identifier is a common topic. Here is one discussion https://www.accessforums.net/showthread.php?t=23329

[CJ_London]

One tip, keep your elements of the number in separate fields then combine as required in your query to create the full number.

As far as your incremental numbering is concerned investigate using 'dmax+1'

basically the code is

newbillnumber=dmax("billnumber","myTable")+1

or to create a new record

currentdb.execute "INSERT INTO myTable (terminal, billnumber) VALUES('WA-SPOKANE'," & dmax("billnumber","myTable")+1 & ")"

or for three records

newbillnumber=dmax("billnumber","myTable")
for i=1 to 3
    currentdb.execute "INSERT INTO myTable (terminal, billnumber) VALUES('WA-SPOKANE'," & newbillnumber+i & ")"
next i

you will need to add in the other fields as required.

be aware that if you have multiple users all doing this at the same time it is possible that the numbers will not be consecutive