Unanswered: How to filter customer records with combo boxes in separate forms?
Hello everyone, I am a new member just join dBforums. I am quite new to database environment so my questions may be a bit long please bear with me.
I am trying to create a database using Microsoft Access 2007 for my company with respect to customer records. I have created several tables such as Customer (Main), Sales Personnel, Industry Area, Nature of Business, Country and State. Base in the Customer table I created a form to add and display new customer records, with few combo boxes lookup to the other tables mentioned above in order to speedup data entry. Then I created a customer query and split form containing certain data (all customers) from the Customer table.
My initial idea is to create a new form with several combo boxes which will be use to filter and display customer records, I named it as "CustomerFilterForm". Before that I have already created a form call "Company Main Page" with several control buttons, for example a control button to open form that use to key in customer data and a control button to open the form which will be use to filter customer records, i.e. the "CustomerFilterForm". In this form I add 5 combo boxes, each lookup to Sales Personnel table, Industry Area table, Nature of Business table, Country Table and State Table, so that I select from the lists what are the customer records I want to filter out.
I have 2 problems here:
1. I wish to create some sort of control button on the CustomerFilterForm itself, which after I select the value for some of all the 5 combo boxes, allow me to click and open a new form which content the customer records I want, base on the customer query and split form I created earlier.
For example, I select Sales Person A in the Sales Personnel combo box, then select Malaysia in the Country combo box, and leave other 3 combo boxes with the value "All" (this is another question, how to add the value "All" to the combo box to represent all the customer records with this specific field?), then when I click a button (I named it "Run Filter") at the bottom of the form, will close the CustomerFilterForm and then extract all the customer records from customer query that fulfill the above criteria? Can this be done with the existing customer query split form?
2. I need all the 5 combo boxes inside the CustomerFilterForm to be dependent on each other, which mean selecting one value in one of the combo box will affect the value list in the other combo boxes.
For example, when I select Sales Person A in the Sales Personnel combo box, other those customers belong to Sales Person A will appear in the list of Customer combo box.