Help with building forms

[DariusD]

So far I am now able to pull data from product_list into the order_list form, however this only works in the columnar view, which seems to be the default form of the oderlist form, but the orderlist subform on the Main Order form, shows in a datasheet view. I cannot create a drop down list in the dataview form... However what happens is that when I enter data in the actual orderlist form itself.. I can then shut down and reopen the order form and see the info that was posted into the orderlist form.. Obviously this is not the most proficient way of doing it.. So I am still trying to figure out how to get info into the orderlist from via the data sheet view... I learned the code to fill in necessary fields,, but again only when its in the columnar form... example attached below

[June7]

Yes, Datasheet view can very much have dropdown comboboxes. Just won't show the dropdown button until in the box.

If you want to provide db for analysis, follow instructions at bottom of my post.

[DariusD]

I followed instruction hope you can access the file of the db.

[June7]

The combobox is setup properly but it is hidden on the Datasheet view.

Right click column header row of the subform and select Unhide Fields.

Check the cboProdID then Close. Move the subform scrollbar right until you see the combobox.

You can left click and release on that header to select that column then left click and hold and drag it left.


A principle of relational database is to not duplicate data. With that in mind, should not have Product_Type, Product_Title fields in OrderItems table. That info is in the Product_List table. For this exercise, probably shouldn't have Product_Price either.


Did you try setting the Totals row on the subform?

[DariusD]

Ok, so number 1, You said I should not include Price in the Orderitems table. So If I don't how do I show the price of the product in the order form, Also Need to be able to show sales, so price should be included. I have not seen order forms, or sales forms that do not include price of the product. Also you said I should not include Title on the OrderItem as well, but again I have not seen sales forms, or order forms, did not include the discription, or name of what you are buying. So how should I have this set up then? Unless your saying its not necessary in the table, but should be included in the report. that I would have to create? Please help Because I am not clear on this. If its an order should it show the price.. and the name of the product....?

[June7]

Revew post 13 describing one way to show the related product information on form and also how to display all information on a report.

[DariusD]

Ok Thanks for the tip on the datasheet that worked perfectly... Now I just need clearer understanding of the point you were making concerning the title, Price, and type. The way I was thinking was that the sales clerk would be on the phone, and the customer would say I want to buy a cd, called Neo, so the sales clerk, would get customer information, and then would go to the orderitem form, and select Neo, list in the Cd section. the price would drop into the form along with the title, and then they would insert the quantity.. Then the subtotal field would show the Price X Quantity.. At the bottom of the form would be the total of all item subtotals in the order.

If this is not right please feel free to tell me how you think it should work, and how I can improve it...
Thank you..

[orange]

Consider the Price in the Product table as the currentPrice of that Product.
Consider a field eg AgreedPrice in the OrderItems. That AgreedPrice could be the same value as the Price in the Products table, or it could be a DiscountedPrice because that Customer has bought so many items over the year; or it could be a Clearance Sale Price, Or it could be a nonStandard price for any other reason. The point is you do record a Price in the OrderItems to show what the Selling Price was to that Customer on that Order on that Date. You can include a Notes field if you want for details about the Price.

But, if you leave Price in the Products table, and do a lookup for all OrderItems for Price in the Products Table, you will get the value in the field currently. So what if you want to raise the price of a Product - what happens to all previous Orders??? What if you want to give Loyalty Discounts to longterm Customers...

Just a few things to consider.

[June7]

Just noticed OrderItems table has Product_ID set as primary key in OrderItems table. That is wrong and causes issues. This table does not have a primary key field. If you want one, need to add an autonumber field called OrderItemID.

Yes, the setup I suggest will accommodate that scenario. Unfortunately the Totals row isn't allowing Sum of calculated Sub_Total column on form but it does in a query - odd. Didn't remember that being an issue on form. So if you want to show column total on form, will need different setup.

You have =0 as DefaultValue for Product_Quantity in OrderItems table - remove the = sign.

Some relationships were set up incorrectly.

Here is a functioning form. Keep in mind the product price won't update until exit the combobox and the Total won't calculate until move to another record.