Hi all,

I'd love some advice on how to accomplish the following:

I'm developing a database that tracks the life of each machine made by
a company. A "work order" is entered in the database (in table
WorkOrders) which goes to the production department, requesting that
they make a certain number of machines in a certain way. When a batch
of new machines are made, an incremental range of serial numbers are
assigned to each machine (each being stored in table Hardware). The
work order form has the following fields (among others, irrelevant
here):

Work Order ID (auto-serial)
Work Order #
Beginning serial #
Ending serial #
Quantity of machines

and I have a relationship linked among table WorkOrder and table
Hardware.

There is a consistency here that I hope will allow me to automate the
next process...

The start and end serial numbers are always increased incrementally by
1, and always start with the same one-character alpha letter.

For example, 5 machines might be assigned serial #'s B5001-B5005
(B5001, B5002, B5003, B5004, B5005).

The next step is to enter each unique serial number into the Hardware
table, where each record is one machine with it's unique serial
number, etc.

The database user will enter the range of serial numbers (or,
logically, just the start serial # and the quantity - the end serial
should be filled in automatically - though I'm not sure how to do this
yet) in the table WorkOrders. From here, I'd like to run a script that
automatically enters a new record for each machine in the batch, in
the Hardware table, auto-filling each serial number based on that Work
Order range. This would cut out a lot of data entry, where there can
be work orders for hundreds of machines.

For the above example, there would be 1 WorkOrder record, stating a
range of 5 serial numbers, (B5001-B5005) and a corresponding 5 records
in the Hardware table, one for each new machine (B5001, B5002, B5003,
B5004, B5005).

So my 2 questions are:
1. How to automate the "end serial #" in the work order form,based on
a calculation from the starting serial #, increasing incrementally by
one (with the 1st character being alpha) based on the number entered
in the quantity field.

2. How to automatically create a batch of new records in the Hardware
table, auto-filling each unique serial number based on the range that
is stored in the WorkOrder table.


Thanks for reading...

Any help is greatly appreciated!