Option Button in Advanced Search Form/Query

[EricaP]

Hi,

I have been trying to get the following to work with no luck:

I have created a multi-field search form, and one of the fields on it is an Option Button. This option button I would like to link to a table where the data type in the field is a YES/NO option.

So basically, I would like users to be able to click the option button on the ADVANCED SEARCH FORM and return all events from the table where the YES/NO data type = YES, and where if it's left blank then the user gets all returns, either YES or NO

I know there's probably a very simple way to do this, but I can't seem to figure it out...

Any help would be greatly appreciated.

Erica

 

[jdraw]

By Option Button you mean button, not Option Group or some other control/concept?

So you search one table. If the user clicks the "option Button" -- a constraint that says "limit output to those with EventYesNo = YES is applied to the search.

How do you invoke the search? What triggers the search to occur if the user doesn't click the "option button"?

I think you can post pictures, before the 10 post limit, if you zip your file.

see this for details

 

[EricaP]

Hi there, sorry I can't do the whole zip and upload thing from here so I will kiind of sketch it out for you. I have an advanced search form with 3 command buttons at the bottom (search, clear all, and exit) and then a variety of entries the user can make. So, it looks like this:

Name: (text box)
Title: (combo box)
subtitle (cascading combo box from combo box "Title")
keyword search: (text box)
Date: From (date picker) to (date picker)

Speaker's Decision (Option button)

Search (command button) Clear All (Command button) Exit (command button)

So I have all the text boxes and combo boxes working properly. You can enter either one or two or all of the criteria and come back with the appropriate data. The only one I can't get to work is the Speaker's Decision option.

Does this clarify?

 

[EricaP]

ok here is a snip I took of my form - maybe this will help...

 

[pr2-eugin]

Why not use a CheckBox instead of an Option button? What is the Query behind the button??

 

[EricaP]

Ok I can change it to a check box, but I still don't know how to make the query work for the check box. I basically used the LIKE function in the Query to get all my other results, I have copied and pasted the SLQ view below. I know it's probably fairly simplistic, but it works well enough...



SELECT tblClerks.ClerkLastName, tblEvents.HeadingID, tblEvents.SubheadingID, tblEvents.EventDescription, tblEvents.EventDate, tblEvents.[Speaker's Decision]
FROM tblClerks INNER JOIN tblEvents ON tblClerks.ClerkID = tblEvents.ClerkID
WHERE (((tblClerks.ClerkLastName) Like [forms]![frmAdvancedSearch].[ClerkName] & "*") AND ((tblEvents.HeadingID) Like "*" & [forms]![frmAdvancedSearch].[Title] & "*") AND ((tblEvents.SubheadingID) Like "*" & [forms]![frmAdvancedSearch].[subtitle] & "*") AND ((tblEvents.EventDescription) Like "*" & [forms]![frmAdvancedSearch].[Keyword] & "*") AND ((tblEvents.EventDate) Between [forms]![frmAdvancedSearch]![Text31] And [forms]![frmAdvancedSearch]![DateRange1]));

 

[EricaP]

Sorry if it's not very pretty, I am just kind of teaching myself as I go...

 

[pr2-eugin]

Right.. So now the second question..

* If it is Ticked - Pull all Yes..
* If not ticked - Pull all No or Pull Yes and No??

Crude way to do would be create two separate Query one to list only the Choice (Yes/No) another would return all..

 

[EricaP]

If it is ticked, pull a yes
If it is not ticked, pull a yes and a no

 

[EricaP]

But if I create 2 seperate queries then how do I tie them in to the query that I already have associated with the form? Sorry I'm a real newbie...

 

[pr2-eugin]

On click all you have to do is open the right Query..

Private Sub theButtonName_Click()
    If Me.OptionalCheckBoxName Then
        DoCmd.OpenQuery "queryToPullOnlyYes_Name"
    Else
        DoCmd.OpenQuery "queryToPullAllData"
    End If
End Sub

 

[EricaP]

Thank you! It turns out all I needed to make it work with the rest of the search form was the Me.OptionalCheckBoxName with a little tweaking in the criteria section of the query.

 

[EricaP]

No no, you were right. I needed the full code and 2 queries. Works like a charm now though