Access: Entering multiple subform values with one entry in the form
I've been using Access to create simple databases for a while with great
success, but have run into a problem I can't find an answer to.
We ship individualized serialized units to various end-users, and
occasionally to resellers that stock them for end-users. I must keep track
of which serial numbers end up with each end-users.
The first database I created to handle this recorded company information
in one table using their account number as primary key, order information
in a second table using the order number as the primary key and linked via
the company name, and unit information in a third table with the serial
number as the primary key and linked via the order number.
This worked very well until I had to account for these stock orders with a
reseller. As it was structured, every unit was linked to one company via
the sales order. The issue is that I may ship 20 units on one order to
Company A, who then sells 5 to Company B and 3 to Company C.
I realized I needed to link the company name directly to the units, not
the orders and have fixed that.
My issue now is simplicity in entering information in the form. My
previous database involved the employee in our shipping department merely
entering the sales order, selecting the customer name from a drop down
menu, then scanning the serial numbers in a subform. This was to ensure
simplicity and try to eliminate human error. He had only three things to
input, and most of the input was done by scanning barcodes.
As it is currently structured now, the employees out in shipping would
have to populate the company name for every record in the subform with the
serial number and that complicates things in a way that is unacceptable.
At the point of shipping, the company name will always be the same for
every unit in the subform.
So.
How would I go about creating a form where the company name is entered
once in the form, and automatically populates itself for every record in
the subform? The caveat here is that I must also be able to go back
occasionally and change the company name of individual units in an order
without necessarily affecting the rest of the order. I suppose it starts
out as a one-to-many relationship that then must be able to change.
I hope that makes sense.
I have looked for answers using various approaches with auto-fill and
relationships and not preserving data integrity, but I feel the answer is
just beyond my reach.
The only solution I can think of is to create another field in the unit
table for the end-user, and perhaps write a formula that sets this default
value as the company name from the order that shipped it. This seems
unnecessarily complicated and redundant, there has to be a better way.
No comments:
Post a Comment