Query not combining and filtering values based on a form

[mayestom]

I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I’m basically using the form as parameter’s for the query.
The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.
I've attached screen shots of the form and the design view of the query (sorry the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").

Any help in the right direction would be very much appreciate it.
Thank you.

Using Windows 7,
Access 2010

Bonus problem for anyone willing to help with: Is there a way to select multiple values from the drop downs?

 

[GinaWhipp]

Hmm, have a look at...
http://allenbrowne.com/ser-62.html

At the bottom it talks about doing it via a query though my preference would be VBA.

 

[mayestom]

Thanks for the reply GinaWhipp.
I have seen that article before. The problem is that he/she does not recommend using a query (not sure of any other way of getting the results) and it also does not tell me how to do it. It lists the SQL statement but I’m not sure what to do with that, as I don’t know SQL.

 

[GinaWhipp]

That would a HE, MR. Allen Browne. I love calling him that his tips are the best!

Now, on to your problem... there is a sample file you can download AND we are here to walk you thru getting it set up in your database. Don't worry SQL is easy once you start to get into it.

 

[mayestom]

Ok, I downloaded the sample database from the Allen Browne web site.

 

[GinaWhipp]

Have you looked at it? Are you crazy yet?

You have three Combo Boxes and three buttons. Let's start with the Combo Boxes, is the first row of each Combo Box TEXT or NUMERIC?

 

[mayestom]

I have looked at it the sample db. Not sure what to do with it? The form in it is pretty much what I'm looking for because you can pick and choose which fields you want to search on (but his form does not have drop downs which I need to have in my form), but I think I might be a little to deep with the path I'm going down to change at this point. So....

All three combo boxes on my form are text type.

 

[GinaWhipp]

You're not in too deep... I'm going to get you thru this and you'll be surprised how easy it will be... so deep breathes!

I need the name of the fields in the Table (or Query) those three Combo Boxes are attached to and the name of the Combo Boxes, i.e.

If the Combo Box is called cboTest, the field it is pulling values from would be TestID. Understand?

 

[mayestom]

I need the name of the fields in the Table (or Query) those three Combo Boxes are attached to and the name of the Combo Boxes, i.e.

1st combo box name: Combo1 Row Source:SELECT Project.Project_Name FROM Project;
2nd combo box name:Combo3 Row Source:SELECT [Supplier].[Supplier_ID], [Supplier].[Supplier_Name] FROM Supplier ORDER BY [Supplier_Name];
3rd combo box name:Combo7 Row Source:SELECT [Location/Affiliate].[Location/Affiliate_Name] FROM [Location/Affiliate] ORDER BY [Location/Affiliate_Name];

 

[GinaWhipp]

Perfect... now, please bare with me, I really do have a day job that keeps getting in the way but I am not abandoning you!

 

[mayestom]

I totally understand. I appreciate the time you are spending on this to help me.

 

[GinaWhipp]

Please copy ALL the code you have behind your Form here or if you are using Embedded Macros please remove them and let me know. If all of that is greek to you, can you upload your file here? (I don't need any information just the file, so you can empty it.)

 

[mayestom]

Ok I have attached a copy of my db. The form I'm talking about is called DropDown form. The query it uses is called DropDown qry test. The table it is querying is called Equipment.

 

[GinaWhipp]

Okay, I'll be looking at in a few minutes... (or I hope it will only be a few minutes)

 

[GinaWhipp]

Do you want to run the query or show the results in the same Form?

 

[mayestom]

Boy, what a luxury!
I'm thinking maybe the form because with the query they would need to close the query window in order to run another query. And if it were in the form they could keep running different queries without having to close and reopen the form. Am I thinking correctly?

But honestly if one way is easier for you then the other, do the ease one. I appreciate all the time you have spent on this and don't want you to spend anymore then you would want to.

 

[GinaWhipp]

Good, I like showing it in the Form also (and your thinking is correct).

Hmm, well, here to help so no worries about my time... I was just waiting till I heard back so I knew which way to do it.

 

[GinaWhipp]

Okay, so I have made it work but there are some things you need to deal with...

1. Reserved Words

Table: Equipment spec
Field: Type

Using words reserved for Access as field names will cause problems for you because it confuses Access. You need to change the name of that field. (I have already checked all the other names). For a complete list of Reserved Words see...
http://allenbrowne.com/AppIssueBadWord.html

2. Field Names

You have included spaces, underscores and slashes in your field names (and table names). This really makes coding a problem as the ones with spaces and slashes will require bracketing. I'm lazy and considering the amount of code I have to type I want to save all the key strokes I can. You need to adapt a Naming Convention, here's mine...
http://www.access-diva.com/d1.html

I am not suggesting you adapt mine just providing a *guide*.

3. I do not recommend using any field that contain names as a Primary Key, i.e. your Location/Affililate table. Use a Autonumber as your Primary Key. Suppose you have to Locations with the same name? What are you going to do then? I strongly recommend you put a Primary Key that is an Autonumber in all your tables. Afterall, that number is for Access to maintain database integrity so no one needs to see it anyway.

4. Spotted some Controls on Forms with the generic names assigned to them, i.e. Text101, Label2. Let's make sure we give those some intelligent names. Will be easier down the road for you and for anyone that comes in behind you or wants to help you.

In the zip file there is your Form DropDown form and qryDropDownTest. The balance of the code is now behind the Form. Enjoy!

 

[mayestom]

Gina,
Thank you does not even begin to express how much I appreciate what you have done for me. I can't believe it, this is incredible! This is exactly, exactly what I was thinking of. Just didn't know how to do it.
Couple questions:
1) You need to explain to me how you did this. Seriously. It's the whole eat fish for a day or learn to fish. If I don't understand how this is done, I'm going to be coming back on here in a couple of weeks or months and asking the same question for a different db.
2) Is the form still using the DropDown qry test query (is that the event procedure On Click for the Set Filter button?)? If so, can I add the Madeup code column back in to the query results form?
3) Can I have the results be none editable? So basically just to see the results not be able to make changes to the records? (I tried changing the Allow Additions or Allow Deletions or Allow Edits to no in the property sheet but that just made the drop downs not work)
4) How can I add labels above the results columns to reflect what the column is? Yeah, I thought I could just move the query results down and add labels but that didn't work out so well

As for your concerns of the db as a whole:
"1. Reserved Words

Table: Equipment spec
Field: Type

Using words reserved for Access as field names will cause problems for you because it confuses Access. You need to change the name of that field. (I have already checked all the other names)."
If I change the name now won't that mess up everything that's dependent on that table/field? The forms, the queries...

"2. Field Names

You have included spaces, underscores and slashes in your field names (and table names). This really makes coding a problem as the ones with spaces and slashes will require bracketing. I'm lazy and considering the amount of code I have to type I want to save all the key strokes I can. You need to adapt a Naming Convention, here's mine...
http://www.access-diva.com/d1.html"
I understand. I'm still obviously new at designing db's and this is one of my first few db's I designed I will start to implement this with my next db.

"3. I do not recommend using any field that contain names as a Primary Key, i.e. your Location/Affililate table. Use a Autonumber as your Primary Key. Suppose you have to Locations with the same name? What are you going to do then? I strongly recommend you put a Primary Key that is an Autonumber in all your tables. Afterall, that number is for Access to maintain database integrity so no one needs to see it anyway."
Understood. Can I add an Autonumber to my tables now? Won't that effect all the forms and all the data that's already in the tables?

"4. Spotted some Controls on Forms with the generic names assigned to them, i.e. Text101, Label2. Let's make sure we give those some intelligent names. Will be easier down the road for you and for anyone that comes in behind you or wants to help you."
Understood. Again, new at this and still learning the correct way of doing things from generous and supportive people such as yourself and this forum.
Thank you for looking over the db as a whole. Your suggestions will help so much with this db and all my future ones. I know it's probably not the prettiest db you have looked at but I'll get better.
I can't believe you did all of this within a couple of hours! You're a tremendous asset to this site. Thank you, thank you, thank you.

 

[GinaWhipp]

You're most welcome! (And thanks for the kind words! )

Hmm, well it's 11:30 pm here and this answer is going to take a minute. I will get to it by tomorrow sometime...