updating records

[Skip Bisconer]

I have an order line table that has many order numbers and several lines per
order number. I have to assign all the order lines of each order number to a
specific Driver, route and vehicle. I have been able to create a form that
pulls the information together but it requires me to enter the information
for each line separately which is tedious as I have up to 800 lines daily.

Can some one suggest a way I can just enter an order number and add the
missing information and have it populate all the lines with that order number
on it?

 

[Allen Browne]

Skip, I presume you have an OrderHead table (order number, date client,
address), and OrderDetail table (line items of the order), and so all the
items in an order are delivered to the one address. If so, it makes sense to
connect the delivers to the OrderHead, not to the line items.

You will then have a table that defines a Delivery run. Fields such as:
DeliveryID AutoNumber primary key
DriverID Number who's doing the run
VehicleID Number what vehicle is being used.

Now you want to assign each of the undelivered orders to the delivery run.
One way to do this would be to add 2 more fields to the OrderHead table:
DeliveryID Number
DeliverySeq Number
These 2 fields will be blank until the order has been assigned to a
delivery. Then you create a delivery run, and add the orders to the run by
assigning the new DeliveryID to the blank OrderHead.DeliveryID field. You
then specify the order of the deliveries on that run by entering a sequence
of numbers in the DeliverySeq field.

The interface to do this will involve some code. One approach would be to
have a main form when you create the Delivery table, with a subform bound to
the OrderHead table, and a listbox showing the undelivered OrderHead records
(i.e. where OrderHead.DeliveryID is null.) You then provide a way where the
user selects an undelivered order in the list box, and either double-clicks
it or clicks a command button to add it to the delivery run. The code
executes an Update query to assign the DeliveryID to the
OrderHead.DeliveryID, and assign the next available DeliverySeq number, and
you Requery the list box so that item disappears.

 

[Skip Bisconer]

Thanks Allen, I will start putting this together and see how it works for me.
I always mistakenly come in the back door. This time I should have come
through the front door.