R
ryguy7272
I’m struggling a bit with some code that dynamically filters elements in a
report. I have a form with the following objects: lstCustomer,
lstExecBroker, an Option Group named ‘fraTrader’, with three radio buttons;
optOption, optCross, optBoth. Also, I have cboSortOrder1, cboSortOrder2, and
cboSortOrder3 as well as cboSortOrder1, cboSortOrder2, and cboSortOrder3, all
of which control sort order. Finally, here’s the code:
Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strCustomer As String
Dim strExecBroker As String
Dim strTrade As String
Dim strFilter As String
Dim strSortOrder As String
DoCmd.OpenReport "OptionsWorking", acViewPreview
For Each varItem In Me.lstCustomer.ItemsSelected
strCustomer = strCustomer & ",'" & Me.lstCustomer.ItemData(varItem) _
& "'"
Next varItem
If Len(strCustomer) = 0 Then
strCustomer = "Like '*'"
Else
strCustomer = Right(strCustomer, Len(strCustomer) - 1)
strCustomer = "IN(" & strCustomer & ")"
End If
For Each varItem In Me.lstExecBroker.ItemsSelected
strExecBroker = strExecBroker & ",'" &
Me.lstExecBroker.ItemData(varItem) _
& "'"
Next varItem
If Len(strExecBroker) = 0 Then
strExecBroker = "Like '*'"
Else
strExecBroker = Right(strExecBroker, Len(strExecBroker) - 1)
strExecBroker = "IN(" & strExecBroker & ")"
End If
Select Case Me.fraTrader.Value
Case 1
strTrade = "='O'"
Case 2
strTrade = "='C'"
Case 3
strTrade = "Like '*'"
End Select
strFilter = "[Customer] " & strCustomer & _
" AND [ExecBroker] " & strExecBroker & _
" AND [Trade] " & strTrade
If Me.cboSortOrder1.Value <> "Not Sorted" Then
strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
If Me.cmdSortDirection1.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder2.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
If Me.cmdSortDirection2.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder3.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value
& "]"
If Me.cmdSortDirection3.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
End If
End If
End If
With Reports![Options]
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With
End Sub
I’ve seen similar things before, but I’ve never seen the
WithReports![Options] . . . End With stuff in Access. If I comment that out,
the report doesn’t get filtered. Am I missing something fairly basic here,
or is this pretty complex. The code pretty much makes sense to me, except
that last part. I can’t figure out why the report is not getting filtered.
Let me draw your attention to one thing; a thing that may actually be the
culprit. When I open a report I get prompted for two Parameters, FirstName
and
LastName. I looked everywhere for these variables; not seeing anything at
all, but there must be something in there that’s causing this behavior. How
can I find out where those pesky parameters are?
Finally, there is one other noteworthy thing. I added a few TextBoxes to
the Report. All went in fine, except for one. I have a little green triangle
in the upper left hand corner and a message that reads ‘this control has an
invalid control source’. I go to Properties > ControlSource > … > then open
the query that this TextBox is supposed to be linked to and double-click the
field in the query to get this: [ExecBroker] «Expr»
[QueryWorking]![ExecBroker]
I deleted the first part and ended up with this:
= [QueryWorking]![ExecBroker]
Access doesn’t like that very much because it gives me an #Error when I view
the report.
Then I delete everything in the Control Source so I have this:
[ExecBroker]
Then the green triangle is back!!
Can anyone tell me how to get this working?
Thanks!
Ryan--
report. I have a form with the following objects: lstCustomer,
lstExecBroker, an Option Group named ‘fraTrader’, with three radio buttons;
optOption, optCross, optBoth. Also, I have cboSortOrder1, cboSortOrder2, and
cboSortOrder3 as well as cboSortOrder1, cboSortOrder2, and cboSortOrder3, all
of which control sort order. Finally, here’s the code:
Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strCustomer As String
Dim strExecBroker As String
Dim strTrade As String
Dim strFilter As String
Dim strSortOrder As String
DoCmd.OpenReport "OptionsWorking", acViewPreview
For Each varItem In Me.lstCustomer.ItemsSelected
strCustomer = strCustomer & ",'" & Me.lstCustomer.ItemData(varItem) _
& "'"
Next varItem
If Len(strCustomer) = 0 Then
strCustomer = "Like '*'"
Else
strCustomer = Right(strCustomer, Len(strCustomer) - 1)
strCustomer = "IN(" & strCustomer & ")"
End If
For Each varItem In Me.lstExecBroker.ItemsSelected
strExecBroker = strExecBroker & ",'" &
Me.lstExecBroker.ItemData(varItem) _
& "'"
Next varItem
If Len(strExecBroker) = 0 Then
strExecBroker = "Like '*'"
Else
strExecBroker = Right(strExecBroker, Len(strExecBroker) - 1)
strExecBroker = "IN(" & strExecBroker & ")"
End If
Select Case Me.fraTrader.Value
Case 1
strTrade = "='O'"
Case 2
strTrade = "='C'"
Case 3
strTrade = "Like '*'"
End Select
strFilter = "[Customer] " & strCustomer & _
" AND [ExecBroker] " & strExecBroker & _
" AND [Trade] " & strTrade
If Me.cboSortOrder1.Value <> "Not Sorted" Then
strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
If Me.cmdSortDirection1.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder2.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
If Me.cmdSortDirection2.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder3.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value
& "]"
If Me.cmdSortDirection3.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
End If
End If
End If
With Reports![Options]
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With
End Sub
I’ve seen similar things before, but I’ve never seen the
WithReports![Options] . . . End With stuff in Access. If I comment that out,
the report doesn’t get filtered. Am I missing something fairly basic here,
or is this pretty complex. The code pretty much makes sense to me, except
that last part. I can’t figure out why the report is not getting filtered.
Let me draw your attention to one thing; a thing that may actually be the
culprit. When I open a report I get prompted for two Parameters, FirstName
and
LastName. I looked everywhere for these variables; not seeing anything at
all, but there must be something in there that’s causing this behavior. How
can I find out where those pesky parameters are?
Finally, there is one other noteworthy thing. I added a few TextBoxes to
the Report. All went in fine, except for one. I have a little green triangle
in the upper left hand corner and a message that reads ‘this control has an
invalid control source’. I go to Properties > ControlSource > … > then open
the query that this TextBox is supposed to be linked to and double-click the
field in the query to get this: [ExecBroker] «Expr»
[QueryWorking]![ExecBroker]
I deleted the first part and ended up with this:
= [QueryWorking]![ExecBroker]
Access doesn’t like that very much because it gives me an #Error when I view
the report.
Then I delete everything in the Control Source so I have this:
[ExecBroker]
Then the green triangle is back!!
Can anyone tell me how to get this working?
Thanks!
Ryan--