Report based on check box and list box...

I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).

What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.

What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.

So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.

Thanks for your help!

Re: Report based on check box and list box...

On Jan 11, 11:08=A0am, Andrew Meador wrote:
> =A0 =A0I have a form (Change Card List by Status) with a check box
> (cboNOT) and a list box (lstStatus). There is an Open Report button
> that opens a report (Report - Change Card List) which uses a query
> (SQL -Change Card List).
>
> =A0 =A0What I want to do is have the form open the report where a filter
> is set to use the values from the check box AND the value selected
> from the list box to generate the report.
>
> =A0 =A0What I can't figure out is how to use the check box's value in this=

> filter. If the check box is not checked the the filter would only be
> based on the list box selection, but if the check box is selected, the
> filter will select all records where NOT list box selection =3D
> table.field.
>
> =A0 =A0So basically it seems like the filter needs to be changed based on
> the check box. I think maybe a text string in code in a click event on
> the Open Report button could set this filter string, but from that
> point I don't know how to get the form to open the report (it in not
> currently done by code) and how to set the filter of the report to the
> form string.
>
> =A0 =A0Thanks for your help!

Dynamically build your query when the user clicks the button to
generate the report. I do the exact same thing in one of my databases
where the report form has 8 list boxes, several check boxes, and a
hand full of text boxes to enter various date ranges. Pass the sql
you build to the report via code and you have what you want.

Re: Report based on check box and list box...

On Jan 11, 12:38=A0pm, frogste...@yahoo.com wrote:
> On Jan 11, 11:08=A0am, Andrew Meador wrote:
>
>
>
>
>
> > =A0 =A0I have a form (Change Card List by Status) with a check box
> > (cboNOT) and a list box (lstStatus). There is an Open Report button
> > that opens a report (Report - Change Card List) which uses a query
> > (SQL -Change Card List).
>
> > =A0 =A0What I want to do is have the form open the report where a filter=

> > is set to use the values from the check box AND the value selected
> > from the list box to generate the report.
>
> > =A0 =A0What I can't figure out is how to use the check box's value in th=
is
> > filter. If the check box is not checked the the filter would only be
> > based on the list box selection, but if the check box is selected, the
> > filter will select all records where NOT list box selection =3D
> > table.field.
>
> > =A0 =A0So basically it seems like the filter needs to be changed based o=
n
> > the check box. I think maybe a text string in code in a click event on
> > the Open Report button could set this filter string, but from that
> > point I don't know how to get the form to open the report (it in not
> > currently done by code) and how to set the filter of the report to the
> > form string.
>
> > =A0 =A0Thanks for your help!
>
> Dynamically build your query when the user clicks the button to
> generate the report. =A0I do the exact same thing in one of my databases
> where the report form has 8 list boxes, several check boxes, and a
> hand full of text boxes to enter various date ranges. =A0Pass the sql
> you build to the report via code and you have what you want.- Hide quoted =
text -
>
> - Show quoted text -

Well, my thinking was close. I need to build the filter in code and
then pass it to the report. I assume I could do it in a similar way as
you are talking about as far as passing the whole query to the report.
The problem is I don't know how to do this. If you can give a sample
or refer me to directions on how to do this that would be excellent!

Thanks!

Re: Report based on check box and list box...

On Jan 11, 12:47=A0pm, Andrew Meador wrote:
> On Jan 11, 12:38=A0pm, frogste...@yahoo.com wrote:
>
>
>
>
>
> > On Jan 11, 11:08=A0am, Andrew Meador wrote:
>
> > > =A0 =A0I have a form (Change Card List by Status) with a check box
> > > (cboNOT) and a list box (lstStatus). There is an Open Report button
> > > that opens a report (Report - Change Card List) which uses a query
> > > (SQL -Change Card List).
>
> > > =A0 =A0What I want to do is have the form open the report where a filt=
er
> > > is set to use the values from the check box AND the value selected
> > > from the list box to generate the report.
>
> > > =A0 =A0What I can't figure out is how to use the check box's value in =
this
> > > filter. If the check box is not checked the the filter would only be
> > > based on the list box selection, but if the check box is selected, the=

> > > filter will select all records where NOT list box selection =3D
> > > table.field.
>
> > > =A0 =A0So basically it seems like the filter needs to be changed based=
on
> > > the check box. I think maybe a text string in code in a click event on=

> > > the Open Report button could set this filter string, but from that
> > > point I don't know how to get the form to open the report (it in not
> > > currently done by code) and how to set the filter of the report to the=

> > > form string.
>
> > > =A0 =A0Thanks for your help!
>
> > Dynamically build your query when the user clicks the button to
> > generate the report. =A0I do the exact same thing in one of my databases=

> > where the report form has 8 list boxes, several check boxes, and a
> > hand full of text boxes to enter various date ranges. =A0Pass the sql
> > you build to the report via code and you have what you want.- Hide quote=
d text -
>
> > - Show quoted text -
>
> =A0 =A0Well, my thinking was close. I need to build the filter in code and=

> then pass it to the report. I assume I could do it in a similar way as
> you are talking about as far as passing the whole query to the report.
> The problem is I don't know how to do this. If you can give a sample
> or refer me to directions on how to do this that would be excellent!
>
> Thanks!- Hide quoted text -
>
> - Show quoted text -

many ways to accomplish this. One is to declare a global variable as
a string. In the module where user clicks the button to build the SQL
assign the dynamically built SQL to the global string. Then in the
code for the form set the source to that variable.

Re: Report based on check box and list box...

Andrew Meador wrote:

> I have a form (Change Card List by Status) with a check box
> (cboNOT) and a list box (lstStatus). There is an Open Report button
> that opens a report (Report - Change Card List) which uses a query
> (SQL -Change Card List).
>
> What I want to do is have the form open the report where a filter
> is set to use the values from the check box AND the value selected
> from the list box to generate the report.
>
> What I can't figure out is how to use the check box's value in this
> filter. If the check box is not checked the the filter would only be
> based on the list box selection, but if the check box is selected, the
> filter will select all records where NOT list box selection =
> table.field.
>
> So basically it seems like the filter needs to be changed based on
> the check box. I think maybe a text string in code in a click event on
> the Open Report button could set this filter string, but from that
> point I don't know how to get the form to open the report (it in not
> currently done by code) and how to set the filter of the report to the
> form string.
>
> Thanks for your help!

This might get you started in a direction that'll work for you.

Private Sub CommandReport_Click()
Dim var As Variant
Dim strF As String 'holds report filter

If Me.ListBoxName.ItemsSelected.Count > 0 Then

'some items in listbox are selected. Change ListBoxName
'to your name. Assumes the key is the first column in the
'list box. If you have 4 columns, the first is 0, last is 3.
For Each var In Me.ListBoxName.ItemsSelected
strF = strF & Me.ListBoxName.Column(0, var) & ", "
Next

'remove the ", " and surround keys/values in ()
strF = "(" & Left(strF, Len(strHold) - 2) & ")"

'now see if it is "IN" list or "Exclude" list.
strF = "ID " & IIf(Me.CheckBox, "Not ", "") & _
"IN " & strF & " And "

'result so far is "ID (not) IN (1,2,3) And "

'let's pretend there's a text box code. Use single quotes
If Me.Code > "" Then
strF = strF & "Code = '" & Me.Code & "' And "
End If

'lets pretend the dollar amt must be > value. No quotes
If Me.DollarAmt > 0 Then
strF = strF & "DollarAmt >= " & Me.DollarAmt & " And "
End If

'and we have a date range. Use # around dates
If Not IsNull(Me.FromDate) Then
strF = strF & "DateFld >= #" & Me.FromDate & "# And "
End If
If Not IsNull(Me.ToDate) Then
strF = strF & "DateFld <= #" & Me.ToDate & "# And "
End If

'now remove the trailing "And"
strF = Left(strF, Len(strF) - 5)

'now call the report with filter
DoCmd.OpenReport "TestReport", , , strF

Else
MsgBox "Please select some items from listbox."
End If

Exit Sub


Too Long
http://www.youtube.com/watch?v=hb8uv4lGnno

Re: Report based on check box and list box...

On Jan 11, 9:37=A0pm, Salad wrote:
> Andrew Meador wrote:
> > =A0 =A0I have a form (Change Card List by Status) with a check box
> > (cboNOT) and a list box (lstStatus). There is an Open Report button
> > that opens a report (Report - Change Card List) which uses a query
> > (SQL -Change Card List).
>
> > =A0 =A0What I want to do is have the form open the report where a filter=

> > is set to use the values from the check box AND the value selected
> > from the list box to generate the report.
>
> > =A0 =A0What I can't figure out is how to use the check box's value in th=
is
> > filter. If the check box is not checked the the filter would only be
> > based on the list box selection, but if the check box is selected, the
> > filter will select all records where NOT list box selection =3D
> > table.field.
>
> > =A0 =A0So basically it seems like the filter needs to be changed based o=
n
> > the check box. I think maybe a text string in code in a click event on
> > the Open Report button could set this filter string, but from that
> > point I don't know how to get the form to open the report (it in not
> > currently done by code) and how to set the filter of the report to the
> > form string.
>
> > =A0 =A0Thanks for your help!
>
> This might get you started in a direction that'll work for you.
>
> Private Sub CommandReport_Click()
> =A0 =A0 =A0Dim var As Variant
> =A0 =A0 =A0Dim strF As String =A0'holds report filter
>
> =A0 =A0 =A0If Me.ListBoxName.ItemsSelected.Count > 0 Then
>
> =A0 =A0 =A0 =A0 =A0'some items in listbox are selected. =A0Change ListBoxN=
ame
> =A0 =A0 =A0 =A0 =A0'to your name. =A0Assumes the key is the first column i=
n the
> =A0 =A0 =A0 =A0 =A0'list box. =A0If you have 4 columns, the first is 0, la=
st is 3.
> =A0 =A0 =A0 =A0 =A0For Each var In Me.ListBoxName.ItemsSelected
> =A0 =A0 =A0 =A0 =A0 =A0 =A0strF =3D strF & Me.ListBoxName.Column(0, var) &=
", "
> =A0 =A0 =A0 =A0 =A0Next
>
> =A0 =A0 =A0 =A0 =A0'remove the ", " and surround keys/values in ()
> =A0 =A0 =A0 =A0 =A0strF =3D "(" & Left(strF, Len(strHold) - 2) & ")"
>
> =A0 =A0 =A0 =A0 =A0'now see if it is "IN" list or "Exclude" list.
> =A0 =A0 =A0 =A0 =A0strF =3D "ID " & IIf(Me.CheckBox, "Not ", "") & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0"IN " & strF & " And "
>
> =A0 =A0 =A0 =A0 'result so far is "ID (not) IN (1,2,3) And "
>
> =A0 =A0 =A0 =A0 =A0'let's pretend there's a text box code. =A0Use single q=
uotes
> =A0 =A0 =A0 =A0 =A0If Me.Code > "" Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0strF =3D strF & "Code =3D '" & Me.Code & "' And=
"
> =A0 =A0 =A0 =A0 =A0End If
>
> =A0 =A0 =A0 =A0 =A0'lets pretend the dollar amt must be > value. =A0No quo=
tes
> =A0 =A0 =A0 =A0 =A0If Me.DollarAmt > 0 Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0strF =3D strF & "DollarAmt >=3D " & Me.DollarAm=
t & " And "
> =A0 =A0 =A0 =A0 =A0End If
>
> =A0 =A0 =A0 =A0 =A0'and we have a date range. =A0Use # around dates
> =A0 =A0 =A0 =A0 =A0If Not IsNull(Me.FromDate) Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0strF =3D strF & "DateFld >=3D #" & Me.FromDate =
& "# And "
> =A0 =A0 =A0 =A0 =A0End If
> =A0 =A0 =A0 =A0 =A0If Not IsNull(Me.ToDate) Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0strF =3D strF & "DateFld <=3D #" & Me.ToDate & =
"# And "
> =A0 =A0 =A0 =A0 =A0End If
>
> =A0 =A0 =A0 =A0 =A0'now remove the trailing "And"
> =A0 =A0 =A0 =A0 =A0strF =3D Left(strF, Len(strF) - 5)
>
> =A0 =A0 =A0 =A0 'now call the report with filter
> =A0 =A0 =A0 =A0 =A0DoCmd.OpenReport "TestReport", , , strF
>
> =A0 =A0 =A0Else
> =A0 =A0 =A0 =A0 =A0MsgBox "Please select some items from listbox."
> =A0 =A0 =A0End If
>
> Exit Sub
>
> Too Longhttp://www.youtube.com/watch?v=3Dhb8uv4lGnno- Hide quoted text -
>
> - Show quoted text -

Wow! Thanks! That was really helpfull, I'm still fresh to actually
getting hands on coding done as well as working in Access and this was
very enlightening. Not too long for me, gave me a few new ideas!
I appreciate your time and thoroughness!

Andrew