Evenlater,
Yeah, I don't open the report in preview mode when I setup my
recordsource, filters, or sorts. Before I give my solution, I would
say that your code will be simpler if you drop the "Sort" button and
simply include the sorts in the primary window rather than a window in
dialog mode. For example, I use 5 sorts in my preview screen, the
first sort is always enabled, the second sort only becomes enabled if
the first is not null and not empty string.
Back to your question, the solution I use is as follows:
1. Setup a bunch of variables in your code that identify the pieces of
the recordsource, filter, or sort criteria that you want to modify
(e.g., varCategory, varType, varLocation); I use variant here b/c I
can get null strings
2. Set these variables equal to the values chosen by the user
3. Build a SQL string that can accomodate your recordsource, filter,
or sort under the variety of circumstances imposed by the user
4. Open the report
5. Set the recordsource, filter, or sort equal to your SQL string
Here's an example:
This is in the cmdPreviewRepor t object:
'Open inventory count sheet report with proper filter and sequence
DoCmd.OpenRepor t "rptInvento ry", acPreview
Reports!rptInve ntory.Filter = FilterSequence
Reports!rptInve ntory.FilterOn = True
Reports!rptInve ntory.OrderBy = SortSequence
Reports!rptInve ntory.OrderByOn = True
This is the Function FilterSequence:
Private Function FilterSequence( )
On Error GoTo Error_Handler
Dim varClass As Variant
Dim varCategory As Variant
Dim varType As Variant
Dim varLocation As Variant
Dim strClassFltr As String
Dim strCategoryFltr As String
Dim strTypeFltr As String
Dim strLocationFltr As String
'Check to see if any filters exist
If Me.fraPrintOpti ons = 1 Then
'No filters exist, exit sub with no filter
FilterSequence = "(SortZero< >0)"
Exit Function
End If
'Set variables equal to values in filtering combo boxes
varClass = Me.cboSelectCla ss
varCategory = Me.cboSelectIte mCategory
varType = Me.cboSelectIte mType
varLocation = Me.cboSelectIte mLocation
'Compile Class, Category, Type, and Location portions
'of the filter
'Note: company location portion of filter is handled
'within the various build queries as criteria; this was
'done to improve the speed of the final source query
If varClass = "" Or varClass = "<all>" Then
strClassFltr = ""
Else
strClassFltr = "((qryInventory ReportBuild2.Cl ass = '" &
varClass & "')) AND "
End If
If varCategory = "" Or varCategory = "<all>" Then
strCategoryFltr = ""
Else
strCategoryFltr = "((qryInventory ReportBuild2.It em_Category =
'" & varCategory & "')) AND "
End If
If varType = "" Or varType = "<all>" Then
strTypeFltr = ""
Else
strTypeFltr = "((qryInventory ReportBuild2.It em_Type = '" &
varType & "')) AND "
End If
If varLocation = "" Or varLocation = "<all>" Then
strLocationFltr = ""
Else
strLocationFltr = "((qryInventory ReportBuild2.It em_Location =
'" & varLocation & "')) AND "
End If
FilterSequence = "(" & strClassFltr & strCategoryFltr &
strTypeFltr & strLocationFltr & _
"SortZero <0)"
Exit_Procedure:
On Error Resume Next
Exit Function
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Function
I hope something like this works for you.
Kelii
Yeah, I don't open the report in preview mode when I setup my
recordsource, filters, or sorts. Before I give my solution, I would
say that your code will be simpler if you drop the "Sort" button and
simply include the sorts in the primary window rather than a window in
dialog mode. For example, I use 5 sorts in my preview screen, the
first sort is always enabled, the second sort only becomes enabled if
the first is not null and not empty string.
Back to your question, the solution I use is as follows:
1. Setup a bunch of variables in your code that identify the pieces of
the recordsource, filter, or sort criteria that you want to modify
(e.g., varCategory, varType, varLocation); I use variant here b/c I
can get null strings
2. Set these variables equal to the values chosen by the user
3. Build a SQL string that can accomodate your recordsource, filter,
or sort under the variety of circumstances imposed by the user
4. Open the report
5. Set the recordsource, filter, or sort equal to your SQL string
Here's an example:
This is in the cmdPreviewRepor t object:
'Open inventory count sheet report with proper filter and sequence
DoCmd.OpenRepor t "rptInvento ry", acPreview
Reports!rptInve ntory.Filter = FilterSequence
Reports!rptInve ntory.FilterOn = True
Reports!rptInve ntory.OrderBy = SortSequence
Reports!rptInve ntory.OrderByOn = True
This is the Function FilterSequence:
Private Function FilterSequence( )
On Error GoTo Error_Handler
Dim varClass As Variant
Dim varCategory As Variant
Dim varType As Variant
Dim varLocation As Variant
Dim strClassFltr As String
Dim strCategoryFltr As String
Dim strTypeFltr As String
Dim strLocationFltr As String
'Check to see if any filters exist
If Me.fraPrintOpti ons = 1 Then
'No filters exist, exit sub with no filter
FilterSequence = "(SortZero< >0)"
Exit Function
End If
'Set variables equal to values in filtering combo boxes
varClass = Me.cboSelectCla ss
varCategory = Me.cboSelectIte mCategory
varType = Me.cboSelectIte mType
varLocation = Me.cboSelectIte mLocation
'Compile Class, Category, Type, and Location portions
'of the filter
'Note: company location portion of filter is handled
'within the various build queries as criteria; this was
'done to improve the speed of the final source query
If varClass = "" Or varClass = "<all>" Then
strClassFltr = ""
Else
strClassFltr = "((qryInventory ReportBuild2.Cl ass = '" &
varClass & "')) AND "
End If
If varCategory = "" Or varCategory = "<all>" Then
strCategoryFltr = ""
Else
strCategoryFltr = "((qryInventory ReportBuild2.It em_Category =
'" & varCategory & "')) AND "
End If
If varType = "" Or varType = "<all>" Then
strTypeFltr = ""
Else
strTypeFltr = "((qryInventory ReportBuild2.It em_Type = '" &
varType & "')) AND "
End If
If varLocation = "" Or varLocation = "<all>" Then
strLocationFltr = ""
Else
strLocationFltr = "((qryInventory ReportBuild2.It em_Location =
'" & varLocation & "')) AND "
End If
FilterSequence = "(" & strClassFltr & strCategoryFltr &
strTypeFltr & strLocationFltr & _
"SortZero <0)"
Exit_Procedure:
On Error Resume Next
Exit Function
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Function
I hope something like this works for you.
Kelii