Desperate: How to activate query criteria for multi-combo box via form

[Access1981]

Hi,

I am trying to make a form where the user can check/uncheck query criteria via several check boxes. The idea is that the user can start with many criteria and then deselect criteria if the search does not return enough results.

I have been setting up several queries and thought I would combine them in a "Master Query", since I thought it may be easier to deal with each criterion and the respective switch this way.

Lets say we run a hairdresser.

I have a field in the form that allows me to select clients. This is also used in the query. Works fine. Now to the hard part.

Example 1:

Each customer has a budget to spend on haircuts.
Each hairdresser offers haircuts from $x to $y.
The query should return all hairdressers that are appropriate for the budget of the selected customer.
There should be a yes/no button on the form to ignore or use this criterion.

Example 2 (this completely threw me off):

Each customer has a set of preferred services from a table (e.g. cutting,washing, coloring).
Each hairdresser offers certain services also based on this table (e.g. cutting,washing, coloring, drying).
The customer and hairdresser table use the services table and a multi combo box to select the services.
The query should return only hairdressers that offer some or none of the services wanted by the client.
Again, there should be a services yes/no button on the form to either ignore or include this criterion.

To clarify, the hard part for me is the query. I am fine with setting up the tables and the form. Just not sure how to implement something like "IF (ServicesCheckBox = -1, 'then use service criterion', 'ignore service criterion')".

Please help! Would much appreciate it! Let me know if I can supply additional information to make it clearer.

Thanks in advance,

Al

 

[jdraw]

Welcome to the forum.
That's a lot of info for first post on any forum. When you have a form with a "simple to use interface" , it usually means it has been well thought out, well designed and well implemented. This isn't something that happens on a first project magically.

Picking criteria from checkboxes on a form and using them to build a query is typically done using a programming language "behind the scenes". The programming language with Access is vba (Visual Basic for Applications). The query is built in the syntax of SQL (Structured Query Language) which is used by Access and most relational data base systems.

I'm going to suggest some reading and free tutorials by Martin Green. His site has done an excellent job on showing readers how to use vba and SQL together (as well as many other concepts/tips).

Good luck with your project.

 

[Access1981]

Thank you for the reply and the "welcome".

I am indeed new to access, but experienced with Excel and (some) VBA, so naturally I was looking into code that would "insert and join" certain tables in the query. No success though.

Generally, the problem (enabling to activate query criteria via form) seems so easy that I would suspect there to be an easy solution and that I am just setting it up stupidly.

Anyways, I am fine with VBA and SQL, so don't hesitate to post code in either. I think I would be able to figure it out...

 

[jdraw]

Check out this link for the approach. He is using text boxes, you want specific checkboxes, so some logic will change.

 

[Access1981]

Thank you for the link. Very helpful!

Current plan on what I read:

1) Make a query for each criterion (e.g. budget of customer vs. price services, services wanted by customer vs. services offered by hairdresser)

2) Write a macro that uses IF( , ,) statements to write the SQL code that will JOIN all the individual outcomes in a summary query (e.g. '&IF(ButtonPrice = -1, "JOIN ... ...", "")&')

This will take me a bit. I will report back on success. Please let me know if I am on the wrong track...

Thanks again,

Al

 

[jdraw]

I don't use macros, I use vba.
Do yourself a favor. Write down in plain English what you want the criteria to be.

eg. Under Customer Budget

If selection is <30 dollars ---then
limit the service to X or Y or Z

If > 60 dollars then
expand services to X2 or Y5 or Z4 or Z5..

I'm not sure of the terminology but you started by saying checkboxes, and then you moved to comboboxes. You should research Cascading comboboxes (which I think is what you need).

See these free tutorials (access 2000, but concept is key)
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

 

[Access1981]

Thanks for the reply and sorry for the confusion. I meant VBA, but am used to use the term macro interchangeably (which I am sure is incorrect).

The reason why I was talking about combo boxes is that my main tables (hairdressers and customers) both use combo boxes. E.g.:

HairdresserServicesOfferedComboBox contains the options: "Cutting, drying, washing, coloring"

CustomerServicesNeededComboBox also contains the options: "Cutting, drying, washing, coloring"

Lets assume for customer 1, in the CustomerServicesNeededComboBox "cutting, washing" is selected. In the query, I would like to be able to find all the hairdressers, where at least "cutting, washing" is selected in the HairdresserServicesOfferedComboBox.

I have solved this part with a join query using the tables "Hairdressers", "Customers" and "PossibleServices" (from which both combo boxes get their values).

This seems to work fine. Note however that it still ignores customer name.

Now, the service criterion is just one out of maybe 7 criteria that are listed in the form. The form has a yes/no check box next to every criterion. In addition, the form has a drop-down list which shows all the customer names.

What I was planning to do is to make individual queries for each of the 7 criteria and then join them in a query. This summary query should take into account the customer name and whether the check boxes for the criteria checked. If the check box for the service criterion is not checked, this criterion should be ignored in the summery query.

The plan currently is to populate the SQL code of the summary query by using VBA. In VBA, I would write a huge concatenate SQL code that uses IF statements to refer to the criterion check boxes, and either adds SQL code that joins the outcome of e.g. the services query described above or alternatively just enters "" if the services criterion check box is not checked.

Hope this is somewhat clearer. Again, if there is a better way, please let me know.

Thanks again for the help, the advice and the links were very useful.

Kind regards,

Al

 

[Access1981]

Just in case somebody cares for the solution:

- I built several queries, each checking one criterion
- I wrote VBA code that builds the SQL code, by using a lot of IF(x,a,b) that write the string depending on which switches in the form are selected.
- An important help was to write the SQL code using [x1]=[x1] AND [x2]=[x2] instead of using JOIN.

Hope this helps. Let me know if you want to see something specific.