Forms and subforms

[wildexodus]

I have 2 forms. One to input customer details with a subform to input orders for each customer. The second shows order details at the top and has a subform to input each of the individual products for a particular order.

At the moment the Order form is allowing me to input orders, which i do not want it to do. I just want to be able to view the orders, and input products into the subform.

Also, as there are so many different orders i need to be able to type in the customer surname and have the possible order numbers listed in a combo box on the order form. Then, when the user selects an order number from the combo, the subform will display all the products for that order.

Does anybody out there know how to do this?

 

[Jack Cowley]

Your order form probably has the Data Entry property set to Yes. Change it to No.

You can use a combo box for the Customers and a combo box for Orders and 'synchronize' the combo boxes. This article will show you how to do that. As far as showing the products in the subform set the Master Link to the Orders combo box and the Child Link to the OrderID.

Jack

 

[wildexodus]

I have gone through the article and put 2 combos on the form and they are working fine. The child field of the sub form is linked to orderID like you said, already. It wont allow me to change the master field to cboOrderNumber. It says it is not in the list.

 

[wildexodus]

.

 

[Jack Cowley]

Just type in combo box name in the MasterLink field. That should do it.

If you still have a problem just let us know.

Jack

 

[wildexodus]

Great, thankyou that works now. Ive finally nearly finished this form. All the entry fields in the subform are currently text boxes. Ive changed, at table level one of the fields to make it a combo box to display all the possible products, so the user can just select one. The combos arent showing up on the actual subform though.

 

[wildexodus]

This is the error message.

 

[Jack Cowley]

I would remove the lookup from the table and create the combo box on the form using the Wizard. It is much for flexible, in my opinion, to do it this way. Once you do that see if you still have the error. It is getting hard to know what is going on now without seeing what you are doing....

hth,
Jack

 

[wildexodus]

sorry, ive been trying 2 post my system but its just a bit 2 big. Ive made it a bit smaller now by getting rid of all the forms i wasnt using. The one i am having problems with is frmorder4.

I've tried what you said, and it did help because i didnt know you could just put a combo box in a subform. So I've tried replacing one of the text boxes with a combo box (product name) and it all works still. The only problem is that when you load the form an error message comes up, and also whenever you pass the mouse over the combo box. I have no idea whats going on there. Ive posted the db with the changes.

 

[Jack Cowley]

You need to add the OrderID field to your subform (actually the SQL statement that is the Record Source for the form). Instead of using an SQL statement for the Record Source of your subform use a saved query with all the fields that you need or use the table.

hth,
Jack

 

[wildexodus]

Thanx 4 that. Took a few tries but seems to be working now.

 

[wildexodus]

Spoke 2 soon! One tiny problem now. In the pricelist table there are about 20 different types of curtains, 15 bedsheets etc. At the moment the row source for the combo is:

SELECT [tblPricelist].[PriceID], [tblPricelist].[Product] FROM tblPricelist;

Ive tried changing it to

SELECT DISTINCT [tblPricelist].[PriceID], [tblPricelist].[Product] FROM tblPricelist;

but because its looking up the PriceID which is the primary key, each one is distinct. How do i get it to make the second column of the combo distinct?

 

[Jack Cowley]

Which form is the combo box in question on and what is the name of the combo box? Is the combo box supposed to be filtered by another combo box?

Jack

 

[wildexodus]

Its on qrysf subform1 on frmorder4, and its called ProductName. This particular combo isnt filtered off another, but once ive got this one working im gonna set up a second to filter off the ProductName combo, and then one to filter off that one etc.

 

[Jack Cowley]

In this case the Combo box is a 'lookup' so you want to show all the items so the user can select the type of curtain, type and lining that the customer is ordering. You need to add the Type and Lining fields to the combo box so the user can select the correct item.

Jack

 

[wildexodus]

If i do that is it then possible to have it automatically fill in the type text box and the linings text box using the selection made in that combo?

 

[Jack Cowley]

Yes. You will need to base the form on a query and show the fields from that table but save the PriceID to the Orders table....

I strongly suggest you look at the Northwind database and use it as a guide as you have your relationships set up incorrectly. Take a look at Northwinds and especially the OrderDetails table and its relationships. You really need to have your structure set up correctly before you spend any more time on your forms.

hth,
Jack

 

[wildexodus]

Thanx, i can see now that the northwind db is exactly how i need mine to be. This should speed things along now. Thankyou for all your help.

 

[Jack Cowley]

You are welcome and good luck with your project....

Jack