Passing a variable from a form to a query (where criteria)?

[Mikael Lindqvist]

Hi,

I've only managed to find bits and parts of how to pass variables between
forms and queries - but I really need someone to write down a simple example
of how to do it - and then I'll modify it from there.

WHAT I TRY TO ACHIEVE

1) In one of my queries I want to filter one of the fields with "or
<variable1> or <variable2> or >variable3>

These 3 variables should be declared in a form (preferably with a
radio-button, where I declare "proper" values to variable1...3 if set to TRUE
and a "bogus" value to varaible1...3 if set to "FALSE".

I have declared variable1 to variable 3 as variables:

Option Compare Database
Public variable1 As Byte
Public variable2 As Byte
Public variable3 As Byte
End Sub

If someone could explain:

a) How do I get radiobutton1 to write a value to variable1 and
b) how do I call variable1 in my query?

May thanks in advance!

Kindly,
Mikael
Sweden

 

[Klatuu]

You are making it harder than it really is.
First, a query cannot reference a memory variable. It can, however,
reference a form control or a public function.

Your radio buttons should be in an Option Group control. When a radio
button, toggle button, or check box is used in an option group, it does not
return its own value. It has, instead and Option Value. You get the value
by referrencing the Option Group control which will returnt the value of the
selected button. Option values have to be numeric integers, so this will
work for your situation.

Add an Option Group to your form.
Add 3 radio buttons to it
Give each a value from 1 to 3.

Add the criteria to you query to reference the option group on the form:

Forms!MyForm!MyOptionGroup

That's all there is to it.

 

[Mikael Lindqvist]

Hi and thanx a ton for a quick response.

Your answer works brilliantly if the value passed to my query is restricted
to one alternative (of a several choices).

However, in my case I need checkboxes that aren't restricted to a single
choice - there could be multiple checkboxes that are clicked.

Now, I need to pass all the checkboxes that are checked to my query.

If I have 4 checkboxes and two of them are "checked" (say number 2 and 3)
and these 2 boxes have "value" 20 and 35 respectively I would like to pass on
this to the query so it reads "WHERE 20 OR 35".

But how?

One way is to have a where statement with 4 "Or", but maybe there's a more
neat solution to this?

Kindly,
Mikael

 

[Douglas J. Steele]

Well, "WHERE 20 OR 35" definitely won't work, as the correct syntax would be
"WHERE FieldName = 20 OR FieldName = 35", although an alternative would be
"WHERE FieldName IN (20, 35)"

You'll either need to generate that in VBA and dynamically alter your query,
or use a Where clause along the lines of

WHERE (FieldName = 10 AND Forms!FormName!Checkbox1 = True)
OR (FieldName = 20 AND Forms!FormName!Checkbox2 = True)
OR (FieldName = 35 AND Forms!FormName!Checkbox3 = True)
OR (FieldName = 40 AND Forms!FormName!Checkbox4 = True)

 

[Klatuu]

It would not be a normal situation to have multiple check boxes referring to
one table field. The only way to do such a thing would be to make the check
boxes all unbond controls, use a bound textbox to contain the derived value
and use a power of two approach to deriving a specific value.