Combo Problem

[PaulJK]

I have a list of business areas (table: "Business Area") and within each business area there are a set of products (table: "Products")The product table lists all products and the Business Area they fall within. A third table lists the product details (table: "Product Details"). I have separate forms which relate to each business area and show the details for any number of products a customer has within that business area. These forms are linked to a customer form so only that customers products are shown.

For example on the Form for Business Area "A", there could be a number of products you could scroll through and look at the individual details for each product for that customer.

The problem I face is that when the field ProductType (in the "Product Details" table) is selected I want on the form only those products which relate to that business area to be listed for selection from a drop down box.
At present I have a simple combo on the ProductType field linked to the Products table which shows all products across all Business Area's. I have tried to experiment with some queries which could then be used as the combo, but at present I confess I am stumped. I do not want to go down the route of having many ProductType fields for each Business Area in the Product Details table with their own combo boxes as this could make reporting more difficult - or would this be correct? It sounds simple but I cannot get anything to work. Could anyone provide some guidance or tips on how I could achieve my aim. Thanks in advance.

 

[Emohawk]

You need to build an expression for the Combo box that adds the Business area field to a Where statement.

I.e.
Select DistinctRow *
From Products
Where [Business Area] = Forms![MyForm]!BusinessFld

You'll also need to have a Requery command for the field where you choose the business area. In the On Change event of the business field place this line of code to refresh the details of the combo box.

BusinessComboBox.Requery

Hope this is what you are looking for.

 

[PaulJK]

Thanks for your reply. Unfortunately, I am still having some problems. I am a new Access user so apologise, as I may not have interpreted your suggestion correctly or explained my issue fully.

For the field where there is currently the combo selection, I have put the expression in the 'row source'. On the form I added the code in the 'on change' area. When the field is selected I am asked to enter the product area and then the product type. I was hoping for just the product types for that product area to be listed only.

My tables:
BUSINESS AREA - BusinessArea ID - BusinessArea
PRODUCTS - ProductsID - BusinessAreaID - ProductName – ProductDescription
PRODUCT DETAILS – RroductDetailsID – CustomerID – ProductName - & various other fields

Any further assistance you could provide would be very much appreciated.

 

[Pat Hartman]

Emohawk almost has the answer. The requery needs to go in the AfterUpdate event of the Property combo.

Me.PropertyCombo.Requery

You don't want to do the requery in the onChange event because the Change event fires for every character that is typed, the AfterUpdate event only fires once. Granted if you use the down arrow to select an entry from the combo, the change event will only fire once, but most people start typing and then tab when the correct entry shows up. so if they typed 3 characters, the Change event would have run three times.

What is happening is - you choose a business area, and that causes a requery of the property combo which is based on a query that uses criteria from the business area combo. This limits the rows in the property combo to those who's business area matches the business area just chosen by the business area combo.

 

[captnk]

Many apologies for raising this item again.

This covers my problem,and I understand the requery concept,my problem is I simply cant physically apply the instructions/

I have 2 field in my combo box: [date] [location]

My situation Is My combo box is set up to run Query1,upon double ckick on a date in the combo box.
That query works fine with: forms!Tsplash!combo46 (where T splash is the name of my form)
Thats Fine...it fines all Locations on that date.

From that point on I dont really understand how to apply the requery to pull just the designated location and not all.

I am obviously missing the point somewhere in the comments,by Pat Hartman,and Emohawk,so any help in clarifying the next step would be appreciated.
Regards