Complex Parameter Question

[Paul7905]

I have a form on which contains 3 combo boxes, 3 list boxes and 2 option groups.

each of the controls are tied to product information or specific attributes of the product we sell.

Presently, i have this form working so that each control is capable of establishing the critera necessary to run a query which returns the item(s) which correlate to the SINGLE criteria selected by the user in one of these controls.

now i want to enhance the process so that the user can select multiple criteria.

The number of parameter combinations that the user will be able to select are enormous and i don't want to create a parameter query for every single parameter combination possibility and so am looking for some sort of technique to handle this, perhaps in VB code? (i am able to ascertain what selections have been made in code but don't know how to use this information to construct a query)

Question, is is possible to create a parameter query containing a parameter for every field that could be selected by the user and then, somehow, when i determine which parameters were actually selected, somehow "disable" the ones that were not and only pass the query the parameters that were ?

any help very much appreciated

Paul
PS, thanks so much to everyone on the forums that have given me so much wonderful assistance on this project.

 

[PaulF]

create a query with all of the fields you need. Set the Criteria for the 8 fields you can select using the controls to

Like [Forms]![FormName].[ControlName] & "*"

This way when nothing is selected by the control, it will use the wildcard and Return all. Only thing required should be for the form to be open when you run the query.

PaulF

 

[Paul7905]

Paul

Thanks for your suggestion !

Response begs another question however ~

What should the value of the controls on the form be if the user selects nothing (or selects something and then unselects it?

i.e., I would like the user to be able to select / unselect the parameters on the form and then invoke the query (which ends up being the control-source of the data for a list box on the form) ~

Presently, each control, when something is selected in it, sets a numeric value which corresponds to a value in the underlying table that is queried. Clicking on the control invokes the Parameter query that is set up for just that specific criteria.

I have modified the controls so that they allow the user to select multiple criterea from one or more of the controls without invoking any queries and have added a control button that will fire off the query when they have made the selections they want.

my question has to do with what the values of the "un-selected" controls should be set to when the query is run so that the query uses the "wild-card" parameter for those fields. Should i initialize them to "null" at form load time then "null" if the user "un-selects" ??

I guess what i am asking is, if i set the query fields to use the "Like[forms]![formname].[controlname]&"*", what should be in the [controlname] if the user did select it or if the user un-selects it after having selected it (user changes their mind or wishes to run a different combination of parameters).

thanks !!

 

[Paul7905]

correction to my post:

last sentence should read:




what should be in the [controlname] if the user did "NOT" select one or more of the controls "AT ALL" or if the user un-selects "ONE OR MORE OF THEM" after having selected it (user changes their mind or wishes to run a different combination of parameters).

 

[PaulF]

in order for the wildcard to bring back all records, the value for the control needs to be null. So setting the default to null or resetting the control to null when deselected is correct. This use of Forms!FormName.ControlName works well for TextBoxes, ComboBoxes, and Single Selected ListBoxes, but not for Option Groups and Multi-Selected ListBoxes.

Now that I've thought about this again, I've noticed that you need to attach some code to the option buttons in order to do this. You might consider using a Public Function to store the value of a Public Variable in, then using the Public Function as the Criteria for the Fields. I've done this in the past with success.

In a New Module add

Public strOption1 as String
Public strOption2 as String


Public Function ReturnOption1()
ReturnOption1 = strOption1
End Function

Public Function ReturnOption2()
ReturnOption2 = strOption2
End Function

Then Use for the Criteria for the Field which is set by OptionGroup1

ReturnOption1

and for the Field which is set by OptionGroup2

ReturnOption2


And attach code to the command button which starts opens the query to set the values for strOption1 and strOption2 prior to sending the OpenQuery command.

Hope this makes sense, If not, post again, and we'll work on it some more.

PaulF

 

[Paul7905]

Paul,

Been working on this all day and have been able to get it to work for most of the criteria.

the list boxes and combo boxes use records with a Key and a description. I initialize these to null and when selected, store the "key" (which the Query uses) in a field on the form. the query references these fields as you suggested and it works great.

Now what i am having trouble with, as you surmised i would, is the option groups.

The table that i query, for the fields represented in the option groups, have columns defined as Yes/No in the table.

Originally, when an option in the group was selected, I called a query that merely had the query "criteria" field in the related field set to True. This returned all the records in the table that appear as "Yes" when i look at the table (this worked fine)

now that i am trying to use the option groups, since the control option-group value has nothing to do with my table or the query, i tried defining one uniquely named text box on the form for each option box control and plopping a value in it when the control on the option group was clicked. Cannot get this to work. the Query comes up with a window asking for the value of (*) no matter what value i place in the corresponding text box on the form.

i tried putting "True", -1, Yes in it and query refuses to recognize it as a valid value ~

I tried your idea of defining a public string and initializing it with true or -1 or yes and putting this public variable in the query as "Like[publicvarname]&[*] and still get the same result (query asks me for the value of *

I wonder if it is because the fields in the table are defined as Yes/No ?? works great for the other fields that are defined in the table as "number" or "Text" ~

any ideas on this ??

thanks so much for all your efforts and thought on this ! i love the technique ~

Paul

 

[Paul7905]

Figured out the problem. needed to define the text box as Yes/No and then set it like Me!Textbox.value = True. Then the Query would recognize it just fine.

Now i am having another problem. The parameter query, with the Forms![FormName].[FieldName]&"*" parameter in it, when the "FieldName" field has the number one (1) in it, returns all records in which the field being queried has a one (1) in it. For example, it returns fields with the numbers 11, 12, 13, 14, 15 as well as the field with only the number one (1) in it.

I made up a test query that only has Forms![FormName.[FieldName] in it and it only returns records with the number one (1) in it.

Very strange. Seems that if the query has the &"*" in the parameter and the argumeent field only has a single digit in it, the query returns all records containing the argument digit even if there are two digits involved.

so, when the field on my form [FieldName] contains the number 1, and the table field has numbers 1 through 15 in it, and the parameter in the query is Forms![FormName].[FieldName]&"*" the query returns 1,11,12,13,14,15.

But when i run a query with the parameter Forms![FormName].[FieldName], the query returns only records with the number one (1) in the field.

any ideas at all on this abberation ??

thanks
Paul

 

[PaulF]

that's what the wildcard is designed to do. Same would occur if you entered "Smith*", it would return Smith, Smiths, Smithville, etc.... anything that started with Smith. Likewise *Smith* would return blacksmith, Smith, Smithville, etc.....

So, another way to approach this is to not use the Like ... * and use two fields in the query for each control. The first field would be the normal field name and use the Like statement for the Criteria without the wildcard

Like [Forms]![FormName].[ControlName]

The second would be and Expression field for the control

Exp1: [Forms]![FormName].[ControlName]

and we'd use Is Null for the Criteria. This would appear on a different Criteria (OR) row then the Like statement.

PaulF

 

[Paul7905]

geeze, think i'll have to mull that one over ~ not clear to me what you are saying Paul ;-(

 

[Paul7905]

Paul,

I looked over your last suggestion and figured out how it works. Problem is, with 14 seperate fields (14 sets of criteria), that applying a "Like" criteria and a Is Null criteria for every possible combination seems overwhelming. I tried to come up with a "method" for filling in the rows in the query for the combinations that would be possible and quickly became a little dizzy from it all.

Maybe i need to go back to the values stored in the tables and re-engineer the data so that a "Like" with the &"*" won't return records that really don't completely match the criteria i intended ~

It's too bad there is not a command that you could put into a query that performs the sort of selection process i need to do on multiple fields ~ (where the "like" meant if i don't get any matches on this field but i do get matches on other field(s) i'll go ahead and return the records i did get matches on and forget i did not get any on one or more of the possibilities ! geeze

thanks so much for showing me these methods, i'll get it to word somehow ;-)

Paul