Order Processing

[Robison]

What process do I use when I want to enter one customer with 12 different items to purchase? i.e. John Doe want to order 1. shirts quantity 8 size s, 8 size med. 2. Pants quantity 10 size m, 10 size xl. I am new with access and I am building the tables and forms associated with them to help my son automate his small business. Thanks Robison

 

[CJ_London]

I would suggest you look at the northwind sample database to get ideas.

In simple terms you will need a number of tables:

Customers
customerID
name
billing address
despatch address
contact details

Products
ProductID
Product Name
Product Size
Product Price

InvoiceHeader
InvoiceID
InvoiceNo
customerID
Date

InvoiceLine
InvoiceLineID
InvoiceID
ProductID
Product Quantlty
Product Price*
TaxRate*
TaxAmount*

*Some will argue these do not have to be stored because they can either be looked up elsewhere or calculated, however if you don't store the information and for example subsequently change the price in the product table, your invoice value will change (and it shouldn't)

However why not simply buy a cheap accounting system with an invoicing option - this link provides a discssion on a number of options

http://www.ukbusinessforums.co.uk/forums/showthread.php?t=204299

 

[Robison]

I may not have expressed my issue properly...we order from and catalog after customer has selected the type of item all we do is screen print or embroidery on the item...therefore we do not have a stock product. Robison
Thanks for your help.

 

[CJ_London]

therefore we do not have a stock product

Maybe not physically but there is still some 'standard's so maybe you need a couple of simple table lists instead and use a combo box to select them:

ProductType
Pants
PoloShirt
TShirt
SweatShirt

Sizes
Small
Medium
Large
XLarge

Prices
1Colour £2.00
2Colour £3.50
3Colour £5.00

 

[Robison]

I have created the above entry forms but the question is how do I make Access produce a new line item for a second item ordered. I thought that when I got to the end of my fields that Access would generate a new line for next item ordered. Mine does not seem to do this. Would appreciate any assistance anyone can offer. Thanks S

 

[CJ_London]

You need to make sure the form for your line items is

a) a continuous form or datasheet
b) Data entry is set to No
c) Allow additions is set to Yes

If all of the above is applied and you still cannot add a new line, I will need to see your form to advise further

 

[Robison]

YEA....my B was set to NO...THANK YOU THANK YOU THANK YOU. Can you explain the use of continuous form? I am designing this database for multiple items per order. Would the continuous form be more useful. S

 

[CJ_London]

The choice of using continuous form over datasheet (they are your only choices!) is really dependant on what you are looking for the user to be able to see and do.

Continuous forms are more 'fixed' in that the user cannot change column widths and add or hide columns available from the recordsource. You have much more control over formatting - different fonts/colours for different controls for example. They can also be used to display data from one record on more than one line and you can size the control heights - in a datasheet, all controls are the same height.

For example say you have a notes table, with userID, a date and a memo field. In a datasheet these would be displayed left to right and you would not necessarily be able to see all of the memo field. In a countinous form you could show the username and date at the top and the memofield, much taller, below.

Also if you are using list boxes, in a datasheet these act like combo boxes.

On the other hand when using a datasheet, it can be very useful for the user to be able to change the width of a column to see more of the field contents. On wide displays for them to move columns next to each other and to freeze columns so they are always on the left. Or perhaps to unhide certain fields which are not required day to day but occasionally need to be referred to.

So the choice is yours!

 

[Robison]

Jump the gun on my first response....you suggested using continuous form but I have a subform and access says you can use continuous with a subform. My order detail field are considerable and I have split them into two forms. Any suggestions? I have attached my database for you to look at. Thanks again for any assistance you can offer. SSSSS

 

[CJ_London]

Are you saying that you complete a record in frmOrders then go to subfrmApparelDecoration to complete the line details?

There is quite alot that is not right

I've modified subfrmApparelDecoration to be a continous form to get you going but you will see that the 'header' is repeated in frmOrders so needs to be removed from subfrmApparelDecoration. I haven't done so because that might upset the way you want things to work - for example the save record button is probably not required.

You also have three different record styles for each product type, apparel, trophies and engraving - will this be three separate orders?

If you like I can redesign for you but I'm about to go out so can come back to you later tonight or tomorrow. Please let me know

 

[Robison]

thanks for the help...yes there are three different types of orders...yes and know on the separate orders. I have tried to include the form so that trophies and engraving are handled separately but on the same form. Customer can purchase a plaque and have it engraved or bring there own item...that is one transaction. The next would be that they are requesting 50 trophies all different sizes and types. This is all one other. My son deals with sports and school events. Kind of complicated for someone like me who is just beginning to learn Access. I sure appreciate your assistance. He doesn't want anything complicated but I want to give him something I can support. THANKS

Forgot to answer your first question. Yes I go first to frmCustomer then to frmOrders then to frmNewApparelOrder with subformApparelDecoration. You can also go from frmOrders to fromEngravingTrohpies. Not sure how to tie the trophy and apparel order together if customer orders both, I was hoping to use the same orderID number but not sure how to do that. S

 

[Robison]

I did modify me subform but it won't let me use the continuous form on the main form it says that it has a subform and can't be used. I studied yours and you have done it but not sure why i can't. Also my form does not look like your example. Bummer.
ssssssssss

 

[CJ_London]

It sounds like you are trying to change your main form to a continous form, which you can't do if the form contains a subform - however the subform can be a continuous form.

 

[Robison]

I am having trouble describing what my problem is. On my form frmOrders I want to be able to order more than one item per order...I want access to create a second line of control fields and then a third etc...It is probably something I am looking right at but can't see. One orderID but many items in the order....Please help.:banghead:

Much thanks to everyone who has answered in the past. I realize that the devil is in the detail with this program. Extremely frustrating at time. Thanks Everyone..sssss

 

[CJ_London]

Hi,

I've done a bit of development for you - see attached. There is a new form called Orders which uses 3 new subforms - I've basically left yours alone.

I think it does what you want, although the layout needs tidying a bit.

I noticed you were using the dreaded lookups in the tables so I have removed these - they are probably the reason you are having problems.

I have also added an image control to the ApparelSF form - now, if you double click on the filename field it will open up the file browser for you to select a picture file. Once selected, the image will show in the image control.

There also seems to be a corruption in the ApparelSF form. I don't have time to fix, but since I would expect you to modify your db to use what I have done, providing you don't just copy the forms across this should not be a problem.

Anyway, have a look and see if this is what you are trying to do

 

[Robison]

WOW thanks for all your work. But I still have one unresolved question. I think maybe I am loosing it but I still can't see how I enter multiple items per order....Once you start order on the order form and want to order another item when does the second group of fields present themselves???? s

 

[CJ_London]

just click on the new apparel or new trophy button and enter the details. To review, you click on an item in the list to the right of the buttons which is a summary of the order

So with regards multiple items this should work

 

[Robison]

Again I must not be stating my question correctly. I just placed an order for shirts apparel and filled out all the fields and I fill out all the field but I want to do it again with another new item say pants. Same order many items....Access only process the given fields once what trigers access to present another set of fields for a multiple item entry. I have looked at my joins between Order table and Detail tables and they are now one to many with #2 chosen as the type. Is this correct?

 

[CJ_London]

I just placed an order for shirts apparel and filled out all the fields and I fill out all the field but I want to do it again with another new item say pants. Same order many items....

Sorry I'm not understanding you.

Lets say you have an order for shirts, 10 x S 3 x L 2 x XL and pants 4 x M, 2 x L, 3 x XL and 1 x trophy. You would do the following:

click on Add Apparel, enter the details for the shirts then click on Add apparel again and add details for pants then click on Add trophy and add detail for trophy.

As each part of the order is added, the summary to the top right of the form is updated.

To edit a line that has been added, just click on the appropriate line of the order summary.

What am I missing?

The Order summary subform can be extended to include additional fields such as values, I wasn't providing you with a complete solution, just trying to show you a way.

I originally suggested you should use a continuous form, but that was before I understood that an order consists of different types of order items. In that situation the continuous form would not work as described - it is now the order summary which is the continuous form - actually a datasheet in this situation.

Perhaps you can give me an example of the type of order you receive - it will help my understanding.

 

[Robison]

In this situation would these all have the same Order Number?