Option Button in Advanced Search Form/Query (1 Viewer)

EricaP · Apr 15, 2013

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 · Apr 15, 2013

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 · Apr 15, 2013

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 · Apr 15, 2013

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

pr2-eugin · Apr 15, 2013

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

EricaP · Apr 15, 2013

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 · Apr 15, 2013

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

pr2-eugin · Apr 15, 2013

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 · Apr 15, 2013

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

EricaP · Apr 15, 2013

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 · Apr 15, 2013

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

Code:

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

EricaP · Apr 15, 2013

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 · Apr 15, 2013

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

Option Button in Advanced Search Form/Query (1 Viewer)

EricaP

Registered User.

jdraw

Super Moderator

EricaP

Registered User.

EricaP

Registered User.

Attachments

pr2-eugin

Super Moderator

EricaP

Registered User.

EricaP

Registered User.

pr2-eugin

Super Moderator

EricaP

Registered User.

EricaP

Registered User.

pr2-eugin

Super Moderator

EricaP

Registered User.

EricaP

Registered User.

Similar threads

Users who are viewing this thread