Help with building forms

[DariusD]

Hello, I am new to access. I am trying to figure out how to create a order form. I have an order table, and a product table, and a customer table.


Basically i want to be to create an order form that would collect the sales persons name, the customer information, and then a combo box list that will allow the sales person to select several products from the product list. I want to be able to save this order. So that I can query orders by customer, sales person, date, Order number. I have no idea how to get all of this to link together.

Initially I created all the tables I listed above. I then created a relationship between the order table and customer table, where I have the customer number as the foreign key on the order table, I also have a relationship between the order table and the product table, where I have the product Id as the foreign key in the order table. Is this a good strategy?

Important note, I do not know VB. so hoping to do this without writing code.

I appreciate your help.
Thanks

[orange]

It's a great start,get your tables set up first. But you have to make sure your tables and relationships support your "business" before getting too deeply into Forms etc.

You may get some ideas from these data models
http://www.databaseanswers.org/data_...ders/index.htm
http://www.databaseanswers.org/data_...ucts/index.htm

and if you want to see the videos to show how to determine such a model, watch these free video tutorials

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation. The example relates Customers, Orders and Items.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

[DariusD]

Ok this is great.. but can I also get input on forms please.. I watched plenty of videos on you tube.. in every case when they drag and drop the combo box a wizard pops up.. but not when I do it.. and I am using access 2010.. each time.. it just shows unbound..

[orange]

watch the youtube videos by 559CD, there are 12 in a set all related to 2010

you can probably start with this one https://www.youtube.com/watch?v=5pBDhwfM1Tc
but I recommend you use these videos to help you with your form.
Good luck.

[DariusD]

watch the youtube videos by 559CD, there are 12 in a set all related to 2010

you can probably start with this one https://www.youtube.com/watch?v=5pBDhwfM1Tc
but I recommend you use these videos to help you with your form.
Good luck.

OK so i edited my table based on the videos.. Now i need to know how to do the following.
create an order form.. that allows more than one item to be selected..
and then I need to be able to total it,
and create a form/report showing the order...

[orange]

Can you post a jpg of your tables and relationships?
Do you have tables for Orders and OrderItems? If not, go back to
http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form.

Once you have your tables and relationships set up like in the Langer videos, you
should do some research on Form and Subform with MSAccess.

Order and Orderitems have a 1 to many relationship. If your tables are set up appropriately, Access
should try to build the form/subform automatically.

[DariusD]

Thanks..
Here is a jpg. of my relationship at this point..

Any suggestions.

[orange]

When you post a jpg of relationships, try to expand the table to show all fields. Your product_List table is mostly hidden. Same for Shipment, Status and Inspection.
What's the difference between Customer and Employee? Both are joined to Orders.
I see a lot of Vendor_ fields in the Shipment table--Is it a separate Entity?

Why would Cust_Num and Emp_Num be in the Inspection table? I don't see any reason for Cust_LName to be in Inspection if Cust_Num is there???

So far, so good. Can you tell us more in plain English WHAT exactly the database is for? I don't recall Shipments and Inspections in previous posts.

Good luck.

Any comments on the videos or links mentioned earlier?

[DariusD]

This is the form I built. So my next question is, the sub form.. I need to make it a drop down list. Now from what I seen through various videos.I'm guess what I need to do is create a query from the Product List table.. then go into design view on the sub form.. and change the text box for list name, and price to combo box, and use the query I just created as the source.. Is that correct?

[DariusD]

Ok sorry Here is more detail.. You can ignore the inspection table..that was part of another project. Basically I need to be able to do the following..

Create a working database for a business such that at least five employees can enter sales from four products sold (video, CDs, books, and DVDs). employees shoul dalso be able to track sales by product type or by employee.

Customer is the person ordering the product. employee table would include the sales reps etc..






Once I learn more about how to use this forum..I will be more than glad to give you cuddles and credits for your help... This forum turned out to go way past my expectations.. I will recommend this forum to all I know..

[orange]

Did you watch the videos? He's real good with the OrderItems and price and quantity.

Good luck with your project.

[DariusD]

Ok I did watch his videos, but now I need help with form..I didn't see anything in his videos for forms, etc... If there are please send me a link...Thanks.

[June7]

Product Number should be a combobox to select product. This can be a multi-column combobox to show ProductNumber, Title, Price all in the drop down list. Review: http://datapigtechnologies.com/flash...combobox3.html

If you want to also show the Title and Price in textboxes, one way is for textbox ControlSource to be an expression that references the respective column of the combobox. Column Index begins with 0 so if price is in third column the index is 2:
=comboxname.Column(2)

If you want to save the Price into the OrderItems table, will require code. There is no need to save the Price unless you expect the price of products to change in the future. As is, the product price can be retrieved in query that joins the tables. Do that for a Report to show all related data.

[DariusD]

Ok, this should be easier then it seems. This is a project I am helping my son with, who is in the eigth grade. He has to submit this for a program he is trying to get into for high school. I have to figure this out so I can then help him... This is what I have for my form so far..

What I need now is to create a way to to select from a list of products and insert it into the Product list, and should be able to select more than one item per order.. Once I have the list or products for the order.. I need to total it..


So should see something like this..

Order id Product Id, Product title, quantity, price, sub total

8 23 redhook 1 35 35.00
8 15 Call of duty 1 59 59.00
8 10 Neo 2 16.99 32.98
total 156.98
All of this info is in the product list table.. except for order Id, and quantity..

here are the relationships and tables...

[June7]

The form/subform you show is to create Order and related OrderItems records.

Creating records in the Product_List table needs to be a completely independent form.

Enter the product records into Product_List table before trying to create Order and OrderItems records.

Records in the Product_List table are available for selection in combobox on OrderItems subform as described in my previous post. Change the Product_ID control to a combobox and set properties:
ControlSource: Product_ID
BoundColumn: 1
RowSource: SELECT Product_ID, Product_Description, Product_Price FROM Product_LIST ORDER BY Product_Description;
ColumnCount: 3
ColumnWidths: 0";1";0.25"

Entering products into Product_List table 'on-the-fly' while entering Orders will require code. This article describes that http://support.microsoft.com/kb/197526

One way to display the total of OrderItems is by use of the Totals button. With form open in FormView (not DesignView) click on the subform so it has focus then click Totals button (I don't know where it is on Access 2000 toolbar). This will open a Totals row at bottom of the subform. Select Sum below the Sub_Total column.

Product_Title field should not be in OrderItems table. Saving Product_Price into OrderItems is optional but will require code to do so.