automated order numbering

[Jae Hood]

Hi,
My order id's look like the following 12345-1
the first 5 digits are assigned by the salesperson, the trailing digit is
the container id for that order.
For example, if one order consists of 8 containers, the sequencing would be
12345-1, 12345-2, 12345-3...etc.
Is there anyway to make access copy 1 record(ie. 12345-1) and create the
rest of the order ids depending on how many containers the order consists of?
So the salesperson would enter the order, and one of the fields he would
fill in would be Containers, if he puts 4 in that field, the database would
take his original 12345-1 order and create -2 -3 and -4 with all of the same
information. So it would use -1 as a template to make -2 -3 and -4.

Is there an easy way to do this?

Thank you!

 

[Brian]

There may be an easier way, but I would do a For...Next loop on the container
count.

After the first (template) record is created, make sure its Order ID and
base value (the part preceding the dash) appears in a control on the form
(let's call it OrderBase) so that you can reference it in the append query.
Add another invisible control called OrderCurrent.

Now, where ContCount is the name of the textbox where the user enters the #
of containers:

For X = 2 to ContCount
OrderCurrent = OrderBase & "-" & str(X)
DoCmd.OpenQuery "CreateNewOrder"
Next

The query will append from the Orders table to the Orders table. Set the
criteria of OrderID to [Forms]![ThisForm]![OrderID] but do not append that
field to OrderID. Instead, make a field [Forms]![ThisForm]![OrderCurrent]
that is appended to Order ID.