Form for Query Criteria

yoshimarine · External Since: Oct 27, 2009 Posts: 4

I have a form that contains three separate option frames, that contain radio
buttons. The first two Filing Period and Reporting Year multiple buttons can
be selected. The third, Filing Body only one can be selected. All of these
will connect to one query that will be connected to one table. Simple right?
My only problem is that I don't know how to handle the multiple and varying
selections in the first two frames and how they would fall into the query and
how that code would look like in the vba code in form.

Frame one there are only two options. Frame two has 24. Frame three has 51.

Thanks for all your help in advance!

Jeanette Cunningham · External Since: Jan 22, 2009 Posts: 39

Hi yoshimarine,
you can use the method shown in the sample database at
http://www.allenbrowne.com/ser-62.html

Allen's sample uses combo boxes and text boxes instead of option groups.
You will be able to use the same way to build the where clause as Allen
shows in his sample database.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"yoshimarine" <yoshimarine RemoveThis @discussions.microsoft.com> wrote in message
news:098DE9EB-FFC3-48DE-8F56-6F90EEC7731B@microsoft.com...
>I have a form that contains three separate option frames, that contain
>radio
> buttons. The first two Filing Period and Reporting Year multiple buttons
> can
> be selected. The third, Filing Body only one can be selected. All of
> these
> will connect to one query that will be connected to one table. Simple
> right?
> My only problem is that I don't know how to handle the multiple and
> varying
> selections in the first two frames and how they would fall into the query
> and
> how that code would look like in the vba code in form.
>
> Frame one there are only two options. Frame two has 24. Frame three has
> 51.
>
> Thanks for all your help in advance!

yoshimarine · External Since: Oct 27, 2009 Posts: 4

Hello Jeanette, appreciate your quick response. I'm actually on my work
network right now and can't download that sample database. I'm actually
suprised I am able to post instead of just view on this site. I just want to
be clear that I will be able to select multiple items in the first two
"menus" or currently frames.

Right now the first frame called Filing Period has Fall and Spring. The
data has a field that has either Fall or Spring. One reason this is
important is because over the years the states (Governing Body frame) may
change from one filing season to another. The user should be able to select
either Fall or Spring or both of them. The second frame, Reporting Year has
24 year from 1995 to 2018. Obviously, not all that data is actually in the
database. Currently, only from 98 to spring 09, but who know? Just prudent
planning. Here the user should again be able to pick as many options as they
want to see. so as few as one to as many as 24.

Does the the sample database concept work the same with the option button as
it does with combo boxes and list boxes. Again the first two of my option
groups already can have multiple seletions, the third cannot. So I am not
limited to that. As I believe that is the advantage of a list box has over a
combo box (I haven't used many list boxes - I'm a combo man myself). I like
the look of having all of the "options" laid out on the form instead of
"hiddin in a list or drop down menu.

Again Thanks So much In Advance.
"Jeanette Cunningham" wrote:

> Hi yoshimarine,
> you can use the method shown in the sample database at
> http://www.allenbrowne.com/ser-62.html
>
> Allen's sample uses combo boxes and text boxes instead of option groups.
> You will be able to use the same way to build the where clause as Allen
> shows in his sample database.
>
>
> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>
>
> "yoshimarine" <yoshimarine DeleteThis @discussions.microsoft.com> wrote in message
> news:098DE9EB-FFC3-48DE-8F56-6F90EEC7731B@microsoft.com...
> >I have a form that contains three separate option frames, that contain
> >radio
> > buttons. The first two Filing Period and Reporting Year multiple buttons
> > can
> > be selected. The third, Filing Body only one can be selected. All of
> > these
> > will connect to one query that will be connected to one table. Simple
> > right?
> > My only problem is that I don't know how to handle the multiple and
> > varying
> > selections in the first two frames and how they would fall into the query
> > and
> > how that code would look like in the vba code in form.
> >
> > Frame one there are only two options. Frame two has 24. Frame three has
> > 51.
> >
> > Thanks for all your help in advance!
>
>
> .
>

John W. Vinson · External Since: Jan 29, 2004 Posts: 2505

On Wed, 28 Oct 2009 06:54:01 -0700, yoshimarine
<yoshimarine.RemoveThis@discussions.microsoft.com> wrote:

> I just want to
>be clear that I will be able to select multiple items in the first two
>"menus" or currently frames.

In that case an Option Group is *NOT* the appropriate tool. An option group
control has multiple buttons (or checkboxes or radio buttons) but you can only
select one of them. The Option Group *control* has only one value and cannot
be used for multiselect.

It sounds like you really need a one to many relationship to another table in
which you can add multiple records.

--

John W. Vinson [MVP]

yoshimarine · External Since: Oct 27, 2009 Posts: 4

Thanks for the reply John,
I'm not sure what you mean by the adding another table. My data is already
in one table. All I am trying to do is pull the data up in a search through
a form. I know how to do it using a form with unbound combo boxes through
queries from the tables, however, if I have in this case multiple years that
I want to look at, at the same time how would I do that. The "Filing Year"
is the name of the field in the table and is the title on the form. If I
want to look at lets say years 1998, 1999, 2000, and 2001 how would I enter
that into the form and get that into the query? that is what I am getting
at. Sorry if I haven't been clear in my problem, or I haven't understood
everyone's explaination. I understand that the option group will only allow
one radio button or check box to be selected at once, but I have my form set
to allow the first two sets multiple options can be selected. If this is not
the way, what way would you recommend?

Thanks Again

"John W. Vinson" wrote:

> On Wed, 28 Oct 2009 06:54:01 -0700, yoshimarine
> <yoshimarine.RemoveThis@discussions.microsoft.com> wrote:
>
> > I just want to
> >be clear that I will be able to select multiple items in the first two
> >"menus" or currently frames.
>
> In that case an Option Group is *NOT* the appropriate tool. An option group
> control has multiple buttons (or checkboxes or radio buttons) but you can only
> select one of them. The Option Group *control* has only one value and cannot
> be used for multiselect.
>
> It sounds like you really need a one to many relationship to another table in
> which you can add multiple records.
>
> --
>
> John W. Vinson [MVP]
> .
>

John W. Vinson · External Since: Jan 29, 2004 Posts: 2505

On Wed, 28 Oct 2009 11:03:01 -0700, yoshimarine
<yoshimarine DeleteThis @discussions.microsoft.com> wrote:

>Thanks for the reply John,
>I'm not sure what you mean by the adding another table. My data is already
>in one table. All I am trying to do is pull the data up in a search through
>a form. I know how to do it using a form with unbound combo boxes through
>queries from the tables, however, if I have in this case multiple years that
>I want to look at, at the same time how would I do that. The "Filing Year"
>is the name of the field in the table and is the title on the form. If I
>want to look at lets say years 1998, 1999, 2000, and 2001 how would I enter
>that into the form and get that into the query? that is what I am getting
>at. Sorry if I haven't been clear in my problem, or I haven't understood
>everyone's explaination. I understand that the option group will only allow
>one radio button or check box to be selected at once, but I have my form set
>to allow the first two sets multiple options can be selected. If this is not
>the way, what way would you recommend?

Ok... so you're using an unbound form to specify search critera?

For a range of not too many years, I'd suggest an unbound multiselect Listbox,
displaying (say) the last ten years, or whatever range would be appropriate.
You'll need VBA code in the "Run Search" button's click event to loop through
the listbox's ItemsSelected collection and build a SQL string, using a syntax
like

WHERE [Filing Year] IN (1998, 1999, 2000, 2001)

Post back if this isn't straightforward.
--

John W. Vinson [MVP]

yoshimarine · External Since: Oct 27, 2009 Posts: 4

John,

I haven't done much with Sql so I don't know if that is any different than
VBA coding, so you may have to walk me through that. But I think this is
what I am looking for.

Thank You,

"John W. Vinson" wrote:

> On Wed, 28 Oct 2009 11:03:01 -0700, yoshimarine
> <yoshimarine.TakeThisOut@discussions.microsoft.com> wrote:
>
> >Thanks for the reply John,
> >I'm not sure what you mean by the adding another table. My data is already
> >in one table. All I am trying to do is pull the data up in a search through
> >a form. I know how to do it using a form with unbound combo boxes through
> >queries from the tables, however, if I have in this case multiple years that
> >I want to look at, at the same time how would I do that. The "Filing Year"
> >is the name of the field in the table and is the title on the form. If I
> >want to look at lets say years 1998, 1999, 2000, and 2001 how would I enter
> >that into the form and get that into the query? that is what I am getting
> >at. Sorry if I haven't been clear in my problem, or I haven't understood
> >everyone's explaination. I understand that the option group will only allow
> >one radio button or check box to be selected at once, but I have my form set
> >to allow the first two sets multiple options can be selected. If this is not
> >the way, what way would you recommend?
>
> Ok... so you're using an unbound form to specify search critera?
>
> For a range of not too many years, I'd suggest an unbound multiselect Listbox,
> displaying (say) the last ten years, or whatever range would be appropriate.
> You'll need VBA code in the "Run Search" button's click event to loop through
> the listbox's ItemsSelected collection and build a SQL string, using a syntax
> like
>
> WHERE [Filing Year] IN (1998, 1999, 2000, 2001)
>
> Post back if this isn't straightforward.
> --
>
> John W. Vinson [MVP]
> .
>

KenSheridan via AccessMon · External Since: May 16, 2009 Posts: 68

You can do it in code by opening a bound form or report filtered to the
selections from three list boxes (which I'd certainly recommend in preference
to option buttons) by means of the WhereCondition argument of the OpenForm or
OpenReport method:

1. First create a table States with column Sate and fill this with the state
names. This will provide the RowSource for one of the list boxes

2. Add a list box lstYears to the form and set its MultiSelect property to
Simple or Extended as desired.

3. Add another list box lstSeasons to the form and set its MultiSelect
property to Simple or Extended as desired.

4. Add another list box lstStates to the form but this time leave its
MultiSelect property as 'None'. Leave its RowSourceType property as
"Table/Query" and its RowSource property to:

SELECT State FROM States ORDER BY State;

5. In the Form's Open event procedure fill the two multi-select list boxes
with:

Dim ctrl As Control
Dim intYear As Integer

Set ctrl = Me.lstYears
ctrl.RowSourceType = "Value List"

For intYear = 1995 To 2018
ctrl.AddItem intYear
Next intYear

Set ctrl = Me.lstSeasons
ctrl.RowSourceType = "Value List"
ctrl.RowSource = "Spring;Fall"

6. Add a button to the form and put code in its Click event procedure to
open a filtered form or report:

Dim varItem As Variant
Dim strValList As String
Dim strCriteria As String
Dim ctrl As Control

' process reporting years list box
Set ctrl = Me.lstYears
If ctrl.ItemsSelected.Count > 0 Then
strCriteria = " And "
For Each varItem In ctrl.ItemsSelected
strValList = strValList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strValList = Mid(strValList, 2)

strCriteria = strCriteria & _
"[Reporting Year] In(" & strValList & ")"
End If

' process reporting seasons list box
Set ctrl = Me.lstSeasons
If ctrl.ItemsSelected.Count > 0 Then
strValList = ""
strCriteria = strCriteria & " And "
For Each varItem In ctrl.ItemsSelected
strValList = strValList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strValList = Mid(strValList, 2)

strCriteria = strCriteria & _
"[Filing Period] In(" & strValList & ")"

End If

' process filing body list box
Set ctrl = Me.lstStates
If Not IsNull(ctrl) Then
strValList = ""
strCriteria = strCriteria & _
" And Filing Body = """ & ctrl & """"
End If

' remove leading " And "
strCriteria = Mid(strCriteria, 6)

' open form filtered to selections
' (use OpenReport method if opening a report)
If strCriteria <> "" Then
DoCmd.OpenForm "YourForm", _
WhereCondition:=strCriteria
End If

7. Add another button to clear all selections, with this in its Click event
procedure:

Dim ctrl As Control
Dim n As Integer

' clear years list box
Set ctrl = Me.lstYears
For n = 0 To ctrl.ListCount - 1
ctrl.Selected(n) = False
Next n

' clear seasons list box
Set ctrl = Me.lstSeasons
For n = 0 To ctrl.ListCount - 1
ctrl.Selected(n) = False
Next n

' set states list box to Null
Set ctrl = Me.lstStates
ctrl = Null

The user can now select multiple items (or no items) from the first two list
boxes and one state from the last

I've assumed in the above that the Reporting Year column is a number data
type and the Reporting Year and Reporting Season columns are text data type.

Ken Sheridan
Stafford, England

yoshimarine wrote:
>John,
>
>I haven't done much with Sql so I don't know if that is any different than
>VBA coding, so you may have to walk me through that. But I think this is
>what I am looking for.
>
>Thank You,
>
>> >Thanks for the reply John,
>> >I'm not sure what you mean by the adding another table. My data is already
>[quoted text clipped - 22 lines]
>>
>> Post back if this isn't straightforward.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200910/1