Solved Intermediary Steps in User Data Entry to Gather Data to Use to Create New Table Records

[LGDGlen]

Please excuse the title of this thread, i'm not entirely sure it makes much sense but i was trying to convey the purpose of the question.

Below describes the process i'm trying create based around current processes and the table and form designs i have so far. The current process is used with a (very large) spreadsheet being shared between a number of users and the purpose of this database is to migrate the users away from the spreadsheet to allow a more stable platform to be used along with improving data integrity and data quality.

Consignment Details

We receive an email from a supplier they tell us that they have some product they want us to sell for them and detail what it is. The product is detailed as a number of lines of information that are all linked by some static data. The products are fruit. A consignment can be made up of 1 or more products and a product is made up of units, cases and pallets of specific fruit which has a variety, country of origin and unit size.

The consignment table is as follows:

The products table links to the consignments using the consignment ID - cons_id - and is defined as:

Once we have the details of the consignment and the products we then find customers to take part or all of the products and these are defined as deliveries.

Deliveries

A delivery goes to a customer which has an amount of static data and can contain 1 or more products which can be a mixture of the consignment products.

The tables for this and how they are linked are:

So we contact regular customers who we work with and detail what products we have and they can specify how much of the consignment they require this is when a supplier provides us with product without a specific customer already lined up. Other instances of the process mean that we already know we are going to supply specific products to customers so contact them to confirm. Either way once confirmed we will created a delivery record and add the products they are going to receive to the delivery.

Data Entry

A user will enter the consignment information in using the following form:

This gives all the static information we get from the supplier. Once entered they can drill into the Consignments to attach products that make up the consignment:

Product information is added to the consignment on the products tab with each product being selectable. The products can also be entered using a form, this form (brought up by clicking the Add Product button) allows the user to add new products not already listed and do other contextually appropriate maintenance as required:

Deliveries are documented on the deliveries tab:

And to each delivery products are attached based around the consignment products and drilling into a delivery shows:

Assistance Required

What i need some assistance with is how to do the following.

Currently i have added a large amount of test data to see how everything fits together and had some of the users have a play around entering some. The process is much more laborious than the spreadsheet that the users are currently running things from and the main issue is creating deliveries.

Firstly i have made it so that unless a user selects 1 or multiple consignment product lines they are not able to add a delivery record. The picture below shows before and after selecting products attached to a consignment and the button being greyed out and then being clickable:

Now what i need to happen is when the Add Delivery button is pressed on the form-Consignment

1. the product(s) that is/are selected are displayed and the number of cases/pallets that are STILL available is displayed along with a data entry box that allows the user to enter an amount to use.
2. when the user specifies what amount of the selected products they want to use they click next to move on to the delivery data entry page
3. once the user has entered the delivery data and clicked save delivered product records based on their selections are created in the FACT-DeliveryProducts table with the new delivery ID.
4. when new delivered product records are created the consignment product available amount is reduced. For this part i may need to add original number of pallets and cases and used pallets and cases fields

I hope this all makes sense and is detailed enough to describe the process we go through and what i want to achieve. I think my biggest understanding hurdle is how to achieve step 1, how do i go about creating a form which can have any number of products that have been selected by the client, display some static information about the selection and allow the user to specify from those products the amounts they want to use in a delivery. I guess once i get the information from the user it would be a matter of just knowing how to pass that around correctly for steps 2 to 4 but if i can understand the first step that would be a good start

Thanks in advance

 

[Pat Hartman]

1-3. Usually this would be done by the form that defines the components of a delivery. The mainform has the customer information along with any date data, etc. In the subform, you would use a combo to select an item and then manually enter the quantity. If you want receipt on different days, it is far simpler to create separate orders.
4. Inventory should be done via transactions. Some times you want to decrease inventory immediately, sometimes not until you actually ship. Depends on your business rules. So, the form for 1-3 might also create the inventory transaction but it might be created by your ship process if that is how your business works. Some companies have another category called Ordered so you know what is in stock but allocated to a customer, just not shipped. You must also have a process that creates the transaction that puts the items back in inventory if the order is cancelled before it ships. Online companies encumber the item when you put it in your shopping cart. Therefore if inventory is low and you don't check out immediately, some other customer may get the item rather than you.

 

[LGDGlen]

just to update:

resolved this myself. probably not the most elegant of ways of doing it but at this time without much guidance it works and gets me to where i need to be so elegance be damned.

The process works as follows:

• User opens a consignment record and is presented with available products to create deliveries from
• User can not create a new delivery till they select at least 1 product
• User selects 1 or more products they wish to deliver to a client and click "Add Delivery"
• Button sends selected consignment product record IDs along with other parameters to the form-Delivery as an argument
• form-Delivery on open checks arguments and then obtains details of consignment records and updates an unbound text field with product information. This means that NO new delivery product records are created at this point
• User enters delivery data and saves delivery.
  • On Save the consignment product records are obtained and used to generate delivery product records
• Form is refreshed if user hits Save, or Save and Closed if user selects that option

there is alot of VBA and passing of info around and a number of hidden unbound fields storing data about what needs to be created once the delivery record is saved. I didn't want anything created until a delivery record is created as the products need the delivery record ID as Foreign Key to link them together

Hope that makes sense, anyway it seems to work ok so i'm gonna move on to other things now

 

[Pat Hartman]

resolved this myself. probably not the most elegant of ways of doing it but at this time without much guidance it works and gets me to where i need to be so elegance be damned.

If you don't understand a suggestion, you can always ask for clarification. Your "solution" is putting the cart before the horse and that is why you are having trouble implementing it and what I was trying to explain but whatever. If you want to write a lot of code, you can do things YOUR way rather than the Access way but don't blame Access. I've been writing code since 1968 and I've written my million lines of code and so don't need the practice. That is why I like Access so much.

PS, all those prefixes defeat the usefulness of intellisense and ensure that you see no actual column name when you open a query or table in DS view. If you think you need to know what table a field came from (you don't), then at least use a suffix so it doesn't get in your way.

 

[LGDGlen]

@Pat Hartman thank you for your response and input it is appreciated

 

[Pat Hartman]

You're welcome.