Subforms problem

[wildexodus]

I have a form for which i want to be able to input order details into the database. On the main form the actual order details will be input (Order date, Order number, etc). However, each order can have lots of products e.g.

Order no. 256
1. 1 pair curtains
2. 2 Tiebacks

I need a subform to display the separate products for each order. However i cannot get it to work. I need to have combo boxes in the form instead of text boxes.

Also, i need the combo boxes on the subform to be cascading, in that the one preceding the next determines what is displayed in the next. I know the code for the cascading thing, i just need to get the combo boxes on the subform and working.

 

[wildexodus]

here is my sys if that helps

 

[neileg]

The problem is in your db design, not the forms.

Your tblOrder should have a child table that holds the individual lines on the order. I assume tblProduct is a file of all the available products, and the entries in the order lines table will be populated from here. The customer and shop ID should not be in the product table but only in the order table.

Until you get this sorted, your forms will never work.

 

[wildexodus]

the tblproduct is to hold each of the separate products for each order. Thanx, I will have another look at it and see if i can change it.

 

[neileg]

Ah, so the products table is performing the job of the order lines table I suggested. This will mean that the entries here are simple user input, not a lookup.

In this case you still don't need the customer or shop IDs in there.

I'll take another look at your db as soon as I have a minute.

 

[neileg]

OK had another look.

1) Your customer ID needs to be on your order form. I suggest you base this form on a query and bring in the customer details.

2) You don't need the order price in tblOrder. You should calculate this from the sum of the product lines

3) You don't need the total price in tblProduct. This should be calculated from qty x price

4) If you link tblPricelist to tblProduct via the priceID, when you change your prices, the database will update all your old records. You should store the price for that product for each order when you create the order. Do not leave it linked.

5) You are holding the shopID against the customer. What happens if the customer buys from more than one shop? You should link the shop to the order, not the customer.

Hope this helps

 

[wildexodus]

i think i understand all that. Thankyou very much for taking the time to help me. I'll try what you said and see if i can get it to work now.

 

[wildexodus]

Okay, did what you said, deleted loads of the relationships etc. Ive got the customer details being entered on one form and then ive got a linked form to take the order details. Im gonna put the subform for the order lines on the linked order form. The customer details are goin into the table fine but when i then click on the button to take me to the linked order form so i can input a new order, it doesnt accept the orders i input. It says a linked record must already be in tblcustomer. The forms i am talking about are 'frmCustomer' and 'frmOrder'.

 

[wildexodus]

sorry wrong database, that ones got the wrong code. This is the right one.

 

[wildexodus]

The order form seems to be trying to look up an order rather than inputting a new one. How can i get it so that it inputs?

 

[neileg]

If you set the data entry form to yes, this will force new entry, not existing ones.

If I were you, I would make the order form a subform of a customer form. This way the links will be handled for you and the screen layout would be cleaner, too.