Orders to Delivery Notes to Invoices - any shortcuts?

[jt196]

OK so my setup at the moment is that I have three tables to manage the first two of the above.

T_Orders

T_DeliveryNotes

T_Transactions

An order goes on T_Orders, and its items go on T_Transactions (with a figure against Quantity_ordered). A delivery note is raised and the items go on the same table with a figure against Quantity_delivered (saving a little time with a combo box taking from Query_OutstandingOrderItems).

I'm also thinking about importing the invoice system from sage and adding a third layer to things.

Now my question is: is there any way I can avoid repeating entry of items - especially on large orders. Someone puts through an order with 20 different products on, and in my current system I have to enter those 20 different products 3 times in order to produce orders, delivery notes and invoices.

Can anyone recommend me a system to speed this process up. ie some kind of macro for me to just shortcut process an order all the way through from Order to Invoice stage. (sometimes we'll get an order through on the phone and just pack it up and send it off right away along with the invoice)

Thanks in advance!

 

[PNGBill]

T_Orders and T-DeliveryNotes only hold the Del or OrdID and some other data but the real table is T_Transactions.

You get a request from a customer for 20 items and in a form you have entered these.

A command button could take you to a Delivery Docket Form that is populated by the records from T_Transactions you just entered.
These 20 records will have CustID, OrderID and null value for DelID and InvID all in common.

The form you open uses as it's data source, a query that selects the fields you want for the records and as you create the command button, you will be given an option to link the fields which would include the orderID. The query will return all records when you create it but the wizard will setup the filter for you.

On the form displayed, you may want to add a field to not include a record in your Invoice or Delivery Docket.

When all looks ok, you click another button to update the T_Transactions so the new DeliveryID or InvoiceID is entered into the field.
Then your Relevant reports will generate the document you want.

My skill level would have an Append Query to add a record to the T_Del Table and another query, just like the query above that returned the 20 records but check for the flag to not include an item and get the relevant DeliveryID and this query would be the update query but VBA can do all this for you.

Trust this assists

 

[jt196]

Bill - again, I really you owe you a few beers when I finally get to PNG...
Thought I'd cracked this program but looks like I need to sit down and digest some of that info. I'll refrain from writing the proper reply for a couple of days once I've had some time to think about it all and have a play around. Cheers.

 

[PNGBill]

Good luck. I would make my main Forms for each view I would like ie New Delivery Docket Form, View pending delivery dockets, Orders Pending, Orders Delivered, Invoices Charged and the list goes on...

Then, When you have a main form, you can usually add a command button to get to another form and show just the data you want to see ie for the customer or product etc that is on the form you are looking at.

Even Menus can be used to open forms in add or edit mode.

But, you often need the base form built first.

Ask plenty of single issue questions as you go along. They often have a quick response and then you off again on your main task.

 

[Simon_MT]

An order or delivery is part of a invoice process.

I would have one table which monitors the progess of the Order. O Order, D Delivery, I Invoice and C Credit.

Create Reports for Orders Confirmation, Delivery Note and Invoices. I treat the delivery Note as an Invoice without values. Although this is a little simplistic it is a way of viewing the process.

Orders can't be changed once delivered or invoiced.

Simon

 

[jt196]

Hmm thanks for advice along the way - actually during redesign I've managed to change the delivery note form (should have been working with a dummy database, but dummy me, I wasn't) and it has mucked things up for me a little.

Before, the delivery notes form had a subform that entered an item on this transactions table, with Delivery Note number and an Order Number. Now, it doesn't seem to be entering the order number onto the subform table.

The order number gets entered into the main delivery notes table but not now on the subform. I can't quite remember how I managed to get it working in the first place, but I seem to remember that it might have been something to do with the delivery note number and order number being both key fields on the DN table. It might have been something different though...

Again, this is about me not having a full understanding of the relationships between forms and tables, but every little problem like this is getting me closer to Accessing enlightenment...

How can I get the subform to enter the order number onto the transactions table if I'm entering that in the main form?

 

[PNGBill]

Try and always make a copy of your database and keep a few of these at different stages so you could import an old version of your form and see how that worked.

TableOrders will have Primary Key, OrderID
TableDel will have Primary key, DelibveryID

But TableTransaction will have Primary Key TransID and the OderID and DelID will just be ordinary fields in the Transaction Table.

You could recreate the subform using the wizard.

 

[PNGBill]

Just to be clear on what I mean. The subform should exist.

You just recreate where it sits in your mainform.

Delete the existing part on your main form and then use the wizard to re enter the subform and it will ask you about matching fields.

 

[PNGBill]

See access help on Form SubForm:

Subforms: What they are and how they work@import url(/Office.css);Subforms: What they are and how they work

A subform is a form within a form. The primary form is called the main form, and the form within the form is called the subform. A form/subform combination is often referred to as a hierarchical form, a master/detail form, or a parent/child form.
Subforms are especially effective when you want to show data from tables or queries with a one-to-many relationship. For example, you could create a form with a subform to show data from a Categories table and a Products table. The data in the Categories table is the "one" side of the relationship. The data in the Products table is the "many" side of the relationship — each category can have more than one product.

The main form and subform in this type of form are linked so that the subform displays only records that are related to the current record in the main form. For example, when the main form displays the Beverages category, the subform displays only the products in the Beverages category.
When you use a form with a subform to enter new records, Microsoft Access saves the current record in the main form when you enter the subform. This ensures that the records in the "many" table will have a record in the "one" table to relate to. It also automatically saves each record as you add it to the subform.
A subform can be displayed as a datasheet, as in the preceding illustration, or it can be displayed as a single or continuous form. A main form can be displayed only as a single form.
A main form can have any number of subforms if you place each subform on the main form. You can also nest up to ten levels of subforms. This means you can have a subform within a main form, and you can have another subform within that subform, and so on. For example, you could have a main form that displays customers, a subform that displays orders, and another subform that displays order details.
More information

See examples of different types of subforms

 

[PNGBill]

And this access help on linking forms and subforms

All of my records show up in my subform -- they don't change when I move from record to record in the main form.@import url(/Office.css);All of my records show up in my subform — they don't change when I move from record to record in the main form.

You need to link your main form and subform using the LinkChildFields and LinkMasterFields properties. For information on linking a main form and a subform, click .
If you've already set the LinkChildFields and LinkMasterFields properties and the subform still doesn't work, make sure that you aren't using fields that have been renamed or deleted. Also make sure that you've spelled the field names correctly and that you're using the actual field names, not the control names specified in the Name property.
Note Under certain circumstances, Microsoft Access will automatically link a main form and a subform when you use a wizard to create the subform. For more information, click .

 

[jt196]

Yeah turns out it was a LinkChildFields/LinkMasterFields issue. I remembered that I had a backup somewhere and went over the forms with a fine toothcomb (when you're learning sometimes you forget the things that you'd spent ages getting right with a bit of trial and error)

Anyway, the subform was linked by OrderID and DNID so BOTH those figures went on the transactions form. Silly me - I'll be more careful in the future...