Help with Combo Boxes/requery....etc

[noaccessguru]

Hi All....
Hoping someone can help me out with this, or provide me with some links to some tutorials or something that will help me out....Im not very good with Code, Im kinda just working that out as I go!

I have a Form with 4 Combo Boxes with 4 different fields
1st = Common Name
2nd = Product
3rd = Manufacturer
4th = Supplier
Which pulls the drop down options from my Materials Table.

I have set each field to requery once the user selects the option above it….
I then have a Command Button that runs a query to show me the rest of the details for the Material that has been selected from the Combo boxes (ie – Status, Dietary Information, Country of Origin etc).

Can you tell me how I set it up so that if the user only wants to select the options from the 1st box or the first 2 boxes they can hit the ‘Go’ button and it will still give them ALL the results that match that critera without selecting options from the 3rd & 4th combo box….(there may be multiple records that match the common name and the Product Name)
Also if they only want to select options from the 2nd & 4th Combo boxes (for example if they don’t know the common name)….

Is this something I have to set up in my query or in VBA?
I may have set my criteria fields in my Query wrong??….. I have attached a word document with the SQL from my query and the code for my form.....Sorry, couldnt figure out how to put it in here properly?


One last thing, When they push the ‘Go’ button, I want the results to appear in a Sub Form below.
Once I have created the subform based on that query, how do I link it to the Command Button so that it shows below and doesn’t open the query in datasheet view in a new window?

Im sure this is all really easy, but unfortunately Im not awesome at Access…..yet I have been watching some tutorials etc, but I haven’t found one that shows me how to do this?

And Apologies for the 4 questions in 1......

Any help would be muchly appreciated

Thanks guys

[orange]

Watch these free video tutorials

http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html
http://www.datapigtechnologies.com/f...combobox3.html

[noaccessguru]

Hi Orange,

Thanks for this reply. These are the Tutorials I have been using and did use to help me create my combo boxes (and alot more of my database).
But there isn't one to tell me how to set up the query to bring back the information I require for the selections made in those combo boxes.

My combo boxes work in terms of when you select Common Name, the Product Combo box only allows me to select records that match the Common Name, and then Manufacturer only allows me to select records that match the Common Name and the Product..

Sorry, I guess I probably worded it all wrong....and the real question is what criteria do I need to put in my query so that:

1. The user selects Common Name and then Product and can push the 'Go' button and it will bring back all the other information for the record/s that match what was selected from the combo boxes.

2. The user selects Common Name, then Product and then Supplier and then pushes the 'Go' button and it will bring back all the other information for the record/s that matches what was selected from the combo boxes.

3. If the user only selects Manufacturer and nothing from the Common Name combo box or any other combo box, it will bring me back all the records for that Manufacturer (I will need to set this up for all the combo boxes too)

Hope that makes a little bit more sense?

Do i possibly maybe need to move this to the 'Query' discussion pages section?

Thanks in advance

[orange]

Do you only have 1 query? If so, I think you need a query foe each combo and the where clause of the query for a combo will refer to the value selected from the previous combo.

See the video with 2 combos. from about 40 seconds to 1:20

[noaccessguru]

Yeah at the moment I only have 1 query set up.

I only want to have 1 button though, not a button for each option.

Can't I just do different lines of criteria in my 1 query??
If I have to create a query for each combo box, how do I link them all to only 1 command button?

There has to be an easy way to do this surely?!

[June7]

Check out another video from same source:
http://datapigtechnologies.com/flash...tomfilter.html

[noaccessguru]

Hi June7,

Thanks very much for that, that was pretty helpful and I will actually use that for something else I want to do!! Thank you
But the main things I need it to do is:

I need to be able to do the following:

• – Select just Manufacturer, or just Supplier, (so it will bring me back all the records for that selected manufacturer, Supplier etc) – i.e, Select the Manufacturer ‘Virgin’ and it will bring me back all the records that Virgin is the Manufacturer of. Currently that is just blank, I’m assuming due to the ‘Where’ query that is on combo box as it needs something filled in on the box above it???

(The Query is currently set up to bring me back just the Common Name when it is selected…I tried fiddling with that but I just can’t figure it out)

• – Select Common Name (ie - Flours) and then select Product (ie – Plain Flour) and the query will bring me back all the records for only Plain Flour

• – Select Product (Plain Flour) and then the Manufacturer (ie – Brunson) and it will show me just that record.

Can it do both? Make it update the combo boxes with the Where statement, aswell as be able to select from any of the combo boxes?? I hope that makes sense?

I have never done these before and have pretty much just tried to work it out from tutorials (mainly the datapig ones), so ALL help is appreciated as I cant seem to find anything else.

I am trying to attach a database with dummy data (random examples) in the table…to show you how I had set up the Combo Boxes in VBE and the query etc. but its not allowing me to upload it from work. I might have to try do it from home later.

In the meantime, any help would be much appreciated if you understand wht I am trying to get at!

UPDATE: Ok so I figured out how to make my Combo boxes work so that I can make it update when I select the box above it, or to select straight from that 1 box.
[Forms]![Frm_Search MaterialsCombo].[CmbCmnName] or [Forms]![Frm_Search MaterialsCombo].[CmbCmnName] = ""
it then put that 2nd part into a new row/criteria field.....so that works! YAY (not sure if thats the right way to do it....but it works!!)

But for the Supplier, Not all the fields are filled in in my table, so there is quite a large gap of blank space before the first option appears in the drop down box....you have to scroll down to see that there are options in there. Do you know how to fix this?

Now its just my query for the button to bring me back the relevant results!

[June7]

The RowSource for Supplier combobox would be like:
SELECT DISTINCT Supplier FROM tablename WHERE Not Supplier Is Null;

Is this a cascading combobox? If so then add the other criteria: AND fieldname = cboComboboxname;

[RuralGuy]

I added an upload to post #7 for the OP.

[noaccessguru]

Thanks for that RuralGuy!

June7, RuralGuy uploaded my example database with how I have set up my combo boxes etc.
If you are able to help with how I would set up my query to be able to run basically any of the combinations I listed in that post when you hit 'GO' that would be a great help.

I tried what you last mentioned, but not sure if I am doing something wrong? but I couldnt work it out properly??