Report based on check box and list box...

[Andrew Meador]

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!

 

[Klatuu]

Don't do any filtering on the query that relates to the check box or the list
box. You will do that in the form in the event where you open the report.
Build a string variable to use as the Where argument of the OpenReport
method. That will do the filtering for your.

Dim strWhere As String

strWhere "[SomeField] "

If Me.chkNot Then
strWhere = strWhere & "<> "
Else
strWhere = strWhere & "= "
End If

strWhere = strWhere & "Me.MyListBox"

Docmd.OpenReport "Change Card List", , , strWhere

 

[Andrew Meador]

Don't do any filtering on the query that relates to the check box or the list
box.  You will do that in the form in the event where you open the report.  
Build a string variable to use as the Where argument of the OpenReport
method.  That will do the filtering for your.

Dim strWhere As String

    strWhere "[SomeField] "

    If Me.chkNot Then
        strWhere = strWhere & "<> "
    Else
        strWhere = strWhere & "= "
    End If

    strWhere = strWhere & "Me.MyListBox"

    Docmd.OpenReport "Change Card List", , , strWhere

--
Dave Hargis, Microsoft Access MVP

   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!- Hide quoted text -

- Show quoted text -

This has gotten me closer. I setup the click event for the Open Report
button as follows:

Private Sub btnOpenReport_Click()
Dim txtFilter As String
Dim Temp

If (lstStatus.ListIndex <> -1) Then
If (cboNot.Value) Then
txtFilter = "[ChangeStatusID]<>" & lstStatus.Column(0,
lstStatus.ListIndex)
Else
txtFilter = "[ChangeStatusID]=" & lstStatus.Column(0,
lstStatus.ListIndex)
End If
Temp = MsgBox("Filter String: " & txtFilter, vbOKOnly, "Filter
Setting")
DoCmd.OpenReport "Report - Change Card List", acViewReport, ,
txtFilter, acWindowNormal
End If
End Sub

I have verified that the data I am getting for the filter should be
right based on the list box selection. For example, item 0 in the list
box has a text entry of 'Done' in the list box, and I am getting the
ChangeStatusID value of 'Done' from Column 0, which is supplied by
ChangeStatusID from the table I'm trying to report on. Anyway, for
example the text that is displayed stored in txtFilter after clicking
on 'Done' in the list box and then clicking the Open Report button is:

[ChangeStatusID]=1

Then when the DoCmd.OpenReport method is called I get a popup asking
"Enter Parameter Value". If I click ok, I get the report with not
data. When I go into Design View mode of the report and check the
Data...Filter Property of the Report, the text: [ChangeStatusID]=1 is
there. But if I try to got back to Report View, I get the same popup.

If I take this text and edit the SQL Query, just putting a where in
front of it, it works fine. I don't see what's wrong. Here's the SQL
query too (as it is being used, not with the WHERE added for testing):

SELECT CD.TaxYear, CD.CardID, CS.Description
FROM dbo_ChangeData AS CD INNER JOIN dbo_ChangeStatus AS CS ON
CD.ChangeStatusID=CS.ChangeStatusID
GROUP BY CD.TaxYear, CD.CardID, CD.ChangeStatusID, CS.Description
ORDER BY CD.TaxYear, CD.CardID;

See anything obvious?
Thanks!

 

[Klatuu]

You missed a line of code to do the filtering.
You are making it harder than it needs to be.

Here are some notes on your code:

Private Sub btnOpenReport_Click()
-- Most professionals see a name with txt and will think it is a text box.
Most will use str
Dim txtFilter As String
-- Temp is a variant because you nave not defined a type. Use a string
Dim Temp
-- Dim Temp As String

If (lstStatus.ListIndex <> -1) Then
-- You don't need to use the .Value property, It is the default
-- The logic is reversed by not using the Not
-- You should always qualify your control names
If (cboNot.Value) Then
-- If Not Me.cboNot Then

-- There is no need to use the column or the ListIndex properties. If it is
not a multiselect list box.
-- Since ChangeStatusID is a test field, you need to enclose it in quotes
txtFilter = "[ChangeStatusID]<>" & lstStatus.Column(0,
lstStatus.ListIndex)
-- txtFilter = "[ChangeStatusID]<>'" & Me.lstStatus & "'"
Else
txtFilter = "[ChangeStatusID]=" & lstStatus.Column(0,
lstStatus.ListIndex)
-- txtFilter = "[ChangeStatusID]='" & Me.lstStatus & "'"
End If
-- What is this for, debugging?
Temp = MsgBox("Filter String: " & txtFilter, vbOKOnly, "Filter
Setting")
DoCmd.OpenReport "Report - Change Card List", acViewReport, ,
txtFilter, acWindowNormal
End If
End Sub

Try making the above changes and see if that helps.
--
Dave Hargis, Microsoft Access MVP

Don't do any filtering on the query that relates to the check box or the list
box. You will do that in the form in the event where you open the report.
Build a string variable to use as the Where argument of the OpenReport
method. That will do the filtering for your.

Dim strWhere As String

strWhere "[SomeField] "

If Me.chkNot Then
strWhere = strWhere & "<> "
Else
strWhere = strWhere & "= "
End If

strWhere = strWhere & "Me.MyListBox"

Docmd.OpenReport "Change Card List", , , strWhere

--
Dave Hargis, Microsoft Access MVP

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!- Hide quoted text -

- Show quoted text -

This has gotten me closer. I setup the click event for the Open Report
button as follows:

Private Sub btnOpenReport_Click()
Dim txtFilter As String
Dim Temp

If (lstStatus.ListIndex <> -1) Then
If (cboNot.Value) Then
txtFilter = "[ChangeStatusID]<>" & lstStatus.Column(0,
lstStatus.ListIndex)
Else
txtFilter = "[ChangeStatusID]=" & lstStatus.Column(0,
lstStatus.ListIndex)
End If
Temp = MsgBox("Filter String: " & txtFilter, vbOKOnly, "Filter
Setting")
DoCmd.OpenReport "Report - Change Card List", acViewReport, ,
txtFilter, acWindowNormal
End If
End Sub

I have verified that the data I am getting for the filter should be
right based on the list box selection. For example, item 0 in the list
box has a text entry of 'Done' in the list box, and I am getting the
ChangeStatusID value of 'Done' from Column 0, which is supplied by
ChangeStatusID from the table I'm trying to report on. Anyway, for
example the text that is displayed stored in txtFilter after clicking
on 'Done' in the list box and then clicking the Open Report button is:

[ChangeStatusID]=1

Then when the DoCmd.OpenReport method is called I get a popup asking
"Enter Parameter Value". If I click ok, I get the report with not
data. When I go into Design View mode of the report and check the
Data...Filter Property of the Report, the text: [ChangeStatusID]=1 is
there. But if I try to got back to Report View, I get the same popup.

If I take this text and edit the SQL Query, just putting a where in
front of it, it works fine. I don't see what's wrong. Here's the SQL
query too (as it is being used, not with the WHERE added for testing):

SELECT CD.TaxYear, CD.CardID, CS.Description
FROM dbo_ChangeData AS CD INNER JOIN dbo_ChangeStatus AS CS ON
CD.ChangeStatusID=CS.ChangeStatusID
GROUP BY CD.TaxYear, CD.CardID, CD.ChangeStatusID, CS.Description
ORDER BY CD.TaxYear, CD.CardID;

See anything obvious?
Thanks!

 

[Andrew Meador]

You missed a line of code to do the filtering.
You are making it harder than it needs to be.

Here are some notes on your code:

Private Sub btnOpenReport_Click()
-- Most professionals see a name with txt and will think it is a text box.
Most will use str
    Dim txtFilter As String
-- Temp is a variant because you nave not defined a type.  Use a string

I just did this as a quick debug - as you noticed in the code below
where I have the msgbox, this is not staying.

    Dim Temp
-- Dim Temp As String

    If (lstStatus.ListIndex <> -1) Then
-- You don't need to use the .Value property, It is the default
-- The logic is reversed by not using the Not
-- You should always qualify your control names

Will do that - qualifying names. I forgot about Value being
default... ooops (still somewhat new at this - hands on needs a lot of
practice
The logic is correct - maybe my naming is weird but I didn't see a
good alternative. If they check this checkbox (cboNot), I want the
comparative operator in the

        If (cboNot.Value) Then
--     If Not Me.cboNot Then

-- There is no need to use the column or the ListIndex properties.  If it is
not a multiselect list box.

This is not a multi-select list box, but it has two columns. Column
0 is hidden and holds the ChangeStatusID and column 1 has the
associated description that matches (so the user gets to pick from a
verbos list verses a numeric list).

-- Since ChangeStatusID is a test field, you need to enclose it in quotes

I assume ou mean 'text' field? If so, no, this is a numeric field
(integer I think - I'm not in front of this system now to check which
numeric type). That being the case, my method was correct (other than
the qualified names), yes?

            txtFilter = "[ChangeStatusID]<>" & lstStatus.Column(0,
lstStatus.ListIndex)
--         txtFilter = "[ChangeStatusID]<>'" & Me.lstStatus & "'"
        Else
            txtFilter = "[ChangeStatusID]=" & lstStatus.Column(0,
lstStatus.ListIndex)
--         txtFilter = "[ChangeStatusID]='" & Me.lstStatus & "'"
        End If
-- What is this for, debugging?

Yep, just wanted to see for sure what I was creating and pushing to
the report.

        Temp = MsgBox("Filter String: " & txtFilter, vbOKOnly, "Filter
Setting")
        DoCmd.OpenReport "Report - Change Card List", acViewReport, ,
txtFilter, acWindowNormal
    End If
End Sub

Try making the above changes and see if that helps.

I ended up getting this to work just before leaving work, what I
ended up figuring out was that the [ChangeStatusID] seems to be
refering to the name of a control on the report verses the field name
contained within the query. I had the field name in the query for the
purpose of joining the the table that had the verbal description of
the ChangeStatusID data for verbose reporting, but I did not include
the ChangeStatusID query field in the report layout. I added that to
the report, and set it to be invisible, and that made it happy. Seems
to be working fine now. I will update it and write in your suggested
coding techniques when I get back to work on Monday.

Thanks for the help, without your input I wouldn't have gotten this
to work. Thanks!