Changing report source query at runtime

[KaleeyJ]

I have a report that uses a parameter query where the parameter is supplied by a form in my db - frmSINGLE. I need to run this report based on a different form - frmMULTI. frmSINGLE may or may not be open - in any case, I don't want to use it.

I would like to avoid having two reports (and two queries) that have the exact same contents save one criteria on one field - this db is big enough as is. So my plan is to change the record source on the report when I run the report from frmMULTI, then switch it back sometime later.

Anyone have any thoughts?

~KJ

[KaleeyJ]

I think that I may be forced to do the multiple rpeorts solution - seems the only way to programmatically change a report's source is to do so in the OnOpen Event of the report - I would much rather be able to do it from my form (so I can set it back as soon as I am finished, among other reasons).

If anyone comes up with a better solution (that doesn't involve changing the code in the report's on open event), I would appreciate it, but right now I am going with two separate reports.

~KJ

[NeoPa]

Kaleey,

The OnOpen event is not such a bad approach actually as it doesn't save the changes to the object design. It only changes the settings in that INSTANCE of the object (in other words while it is running).
I have used this approach a number of times to very beneficial effect. It's much better design than multiple reports and takes less maintenance too, when the report needs updating.

Let us know how you get on :)

[martin DH]

Could one of you post an example of code for changing a report's record source on open?

Thank you.
martin

[NeoPa]

[code]'On Open, this report determines the state of the form and adjusts various
'things based on that. If form not found then it defaults to showing :
' Main Stock Products only
' Costs
' Opal Column Prices
' Sorting must be handled by multiple reports.
Private Sub Report_Open(Cancel As Integer)
Dim intSort As Integer, intSelRange As Integer, intCols As Integer ', intPCs As Integer
Dim intSpare As Integer, intShift As Integer
Dim strSort As String, strWork As String, strType As String
Dim strPC1 As String, strPC2 As String
Dim blnCosts As Boolean, blnReplacement As Boolean, blnAll As Boolean
Dim blnVisible As Boolean

On Error Resume Next
'If next line fails then intFrom stays 0 - otherwise it will be > 0
intFrom = Forms("frm" & conStub).fraFrom
On Error GoTo 0
If intFrom = 0 Then
'Form not found
intSelRange = 1
blnCosts = True
blnReplacement = True
blnAll = False
intSort = 1
strSort = "Prod Group then Code"
intFrom = 3
intCols = 2
'intPCs = 1
dblPC2 = 100 / 60 '40% GPM
Else
'Form found - use settings from form
With Forms("frm" & conStub)
intSelRange = IIf(.chkLawForms, 2, 0) + IIf(.chkMainStock, 1, 0)
blnCosts = .chkShowCost
blnReplacement = .chkReplacement
blnAll = .chkAll
intSort = .fraSort
Select Case intSort
Case 1
strSort = .lblPGrpCode.Caption
Case 2
strSort = .lblPGrpDesc.Caption
Case 3
strSort = .lblProdCode.Caption
Case 4
strSort = .lblProdDesc.Caption
End Select
intCols = IIf(blnReplacement, 1, 0)
Select Case intFrom
Case 1
If Not blnReplacement Then intCols = 6
Case 2, 3
intCols = intCols + 1
strPC1 = Nz(.txtPC1, "")
dblPC1 = IIf(intFrom = 2, (100 + CDbl(strPC1)) / 100, _
100 / (100 - CDbl(strPC1)))
If blnReplacement Then
'First PC shows in second column
strPC2 = strPC1
dblPC2 = dblPC1
ElseIf (.txtPC2.Enabled And Not IsNull(.txtPC2)) Then
'We may have a second PC column
intCols = 2
strPC2 = .txtPC2
dblPC2 = IIf(intFrom = 2, (100 + CDbl(strPC2)) / 100, _
100 / (100 - CDbl(strPC2)))
End If
End Select
End With
End If
'First step is to change the Recordsource if required...
If blnAll Then Me.RecordSource = "qry" & conStub & "All"
'Set up Sorting & Grouping
'Only show PGroupHdr if sorting primarily by PGroup and turn triggering
'to as few as possible (using Prefix) if not showing (otherwise Each)
'Assume both levels must be set with different fields regardless
PGroupHdr.Visible = (intSort < 3)
'.GroupOn ==> 0 = Each; 1 = Prefix
GroupLevel(1).GroupOn = IIf(intSort < 3, 0, 1)
Select Case intSort
Case 1
GroupLevel(0).ControlSource = "PGroup"
GroupLevel(1).ControlSource = "Product"
Case 2
GroupLevel(0).ControlSource = "PGroup"
GroupLevel(1).ControlSource = "ProdDesc"
Case 3
GroupLevel(0).ControlSource = "Product"
GroupLevel(1).ControlSource = "PGroup"
Case 4
GroupLevel(0).ControlSource = "ProdDesc"
GroupLevel(1).ControlSource = "PGroup"
End Select
With txtTitle
Select Case intFrom
Case 1
strType = "Column" & IIf(blnReplacement, " 1 Only", "s")
Case 2
strType = "Markups"
Case 3
strType = "GPMs"
End Select
.ControlSource = MultiReplace(.Tag, "%S", strSort, "%T", strType)
End With
If intSelRange = 3 Then
FilterOn = False
Else
FilterOn = True
strWork = Split(Expression:=conFilters, Delimiter:="|")(intSelRange - 1)
Filter = Replace(strWork, "%L", conLawForms)
End If
lblCost.Visible = blnCosts
'Adjust widths and visibilities of various fields
'Some field attribute may need to be adjusted from their starting values
'Ignore some starting values in the design as they are just to fit visibly
'ProdDesc.Width = 7.354cm (4,170) conDescWidth
'Unit.Width = 1.24cm (703) conUnitWidth
'PriceFields.Width = 1.199cm (680) conPriceWidth
'Expand width of Description in all scenarios except when both the Cost AND
'All Columns are shown.
Me.lblProdDesc.Width = conDescWidth + IIf(blnCosts And (intCols = 6), _
0, Me.lblCost.Width)
'Set captions, width and visibility for Column fields used
Me.lblPrice1.Caption = Replace(conColLbl, "%N", "1")
Me.txtPrice1.ControlSource = "Price1"
Me.lblPrice1.Width = conPriceWidth
Me.lblPrice2.Caption = Replace(conColLbl, "%N", "2")
Me.txtPrice2.ControlSource = "Price2"
Me.lblPrice2.Width = conPriceWidth
strWork = Replace(strType, "s", "") & vbCrLf
If intFrom > 1 Then
Me.txtPrice2.ControlSource = "=Round([Cost]*" & dblPC2 & ",2)"
If blnReplacement Then
'First column same as default (Price1)
Me.lblPrice2.Caption = MultiReplace("Min Sell%L%N%", "%N", strPC1, _
"%L", vbCrLf)
Else
Me.lblPrice1.Caption = strWork & strPC1 & "%"
Me.txtPrice1.ControlSource = "=Round([Cost]*" & dblPC1 & ",2)"
Me.lblPrice2.Caption = strWork & strPC2 & "%"
End If
End If
Me.lblPrice2.Visible = (intCols > 1)
'Special handling for new style report (chkReplacement = True)
Me.lblReplacement.Visible = blnReplacement
'Handle last 4 column fields (Only leave required fields visible)
blnVisible = (intCols > 2)
Me.lblPrice3.Visible = blnVisible
Me.lblPrice4.Visible = blnVisible
Me.lblPrice5.Visible = blnVisible
Me.lblPrice6.Visible = blnVisible
Me.txtPrice3.Visible = blnVisible
Me.txtPrice4.Visible = blnVisible
Me.txtPrice5.Visible = blnVisible
Me.txtPrice6.Visible = blnVisible
'Build up positions and mirror widths & visibilities
intSpare = (Me.Width - BuildUp() - 10) / 4
If (intCols < 6) And (intSpare > 10) Then
If intSpare > conExtend Then
intShift = 2 * (intSpare - conExtend)
intSpare = conExtend
Else
intShift = 0
End If
Me.lblUnit.Width = conUnitWidth + intSpare
Me.lblCost.Width = conPriceWidth + intSpare
Me.lblPrice1.Width = conPriceWidth + intSpare
Me.lblPrice2.Width = conPriceWidth + intSpare
Call BuildUp(intShift)
End If
'Set up Report ID & date for bottom left corner of report
strDate = DLookup(Expr:="

", _
                      Domain:="[tblReport]", _
                      Criteria:="[ReportName]='" & Name & "'") & _
                                Format(Date, " - d mmmm yyyy")
End Sub

[NeoPa]

It doesn't need to be quite that complicated obviously :D

Private Sub Report_Open(Cancel As Integer)
    Dim intTest As Integer
 
    On Error Resume Next
    'If next line fails then intTest stays 0 - otherwise it will be > 0
    intTest = Forms("frmMyForm").fraFrom
    On Error GoTo 0
    'First step is to change the Recordsource if required...
    If intTest = 0 Then
        Me.RecordSource = "qryMyJobAll"
    Else
        Me.RecordSource = "qryMyJob"
    End If
End Sub

[martin DH]

Okay, here's my situation:

I would like to open a single report in one of two sort orders based on a selection on a form. I have two queries (one with one sort order and a second with another). I have the following code on the On_Open event of the report. The report has no control source named in its Properties. Am I going about this incorrectly, because this opens the report with the correct data but does not sort the data?

Private Sub Report_Open(Cancel As Integer)
'changes sort order on report by changing record source
If Forms![frmAgedNotices]!fraSort = 1 Then
  Me.RecordSource = "qryAgedNoticesGroupedAge"
ElseIf Forms![frmAgedNotices]!fraSort = 2 Then
  Me.RecordSource = "qryAgedNoticesGroupedAgency"
End If
 
End Sub

Thanks!

[Stewart Ross]

Hi. If I may add a comment to this intriguing thread - the sort order for a report is defined in the report's design view and does not depend on the sort order of the recordsource query. Your sorting issues must result from (1) the sort order of the report not being defined correctly, or (2) the defined sort order having to be changed to fit the appropriate recordsource.

If it is cause (1) you should be able to resolve the sorting using the Sorting and Grouping facilities in design view. If it is (2), that you need to change the sort order for one of the recordsources, you will need a programmatic approach using the CreateGroupLevel properties of the report object to create a new grouping and then set its associated sort properties. The help file will guide you how to do this.

-Stewart

Okay, here's my situation:

I would like to open a single report in one of two sort orders based on a selection on a form. I have two queries (one with one sort order and a second with another). I have the following code on the On_Open event of the report. The report has no control source named in its Properties. Am I going about this incorrectly, because this opens the report with the correct data but does not sort the data?
...

[NeoPa]

... Am I going about this incorrectly, because this opens the report with the correct data but does not sort the data?
...

You've hit the nail on the head there Martin. As Stewart implies, there is no benefit in having two separate queries which are simply sorted differently. The sort order of the underlying queries is completely overridden by that defined within the report.

Stewart has started you off in the right direction if you're interested in changing the sort order on the fly within the code. Let us know if you're interested in proceeding with this but need some more help with the details.

[martin DH]

Thanks, Stewart and NeoPa. As I was typing my post I began to think that I would have to programmatically change the sort/group order, not the record source. Unfortunately, when I search for help on the CreateGroupLevel Method, Microsoft takes me to a "content not found" page.

I really have no experience here - any guidance would be much appreciated. Thank you.
martin

[NeoPa]

Stewart introduces CreateGrouplevel(), and my joke post (#5) includes some code which plays with this in lines #74 through #94.

Have a look through that and see what you can work out. If you have any specific queries, let us know and we will see what we can do to help you through.

[martin DH]

OK, thanks to both of you. After doing some reading on MSDN at http://msdn.microsoft.com/en-us/libr...ffice.11).aspx, I decided upon the following solution. This works perfectly - the user selects a sort option from frmAgedNotices and clicks a command button that opens the report, running the following on open.

Private Sub Report_Open(Cancel As Integer)
'changes sort order on report by changing control source for sorting/grouping
 
If Forms![frmAgedNotices]!fraSort = 1 Then
  Me.GroupLevel(1).ControlSource = "Age"
  Reports("rptAgedNoticesGrouped").GroupLevel(1).SortOrder = True
  Me.GroupLevel(2).ControlSource = "SourceID"
  Reports("rptAgedNoticesGrouped").GroupLevel(2).SortOrder = False
ElseIf Forms![frmAgedNotices]!fraSort = 2 Then
  Me.GroupLevel(1).ControlSource = "SourceID"
  Reports("rptAgedNoticesGrouped").GroupLevel(1).SortOrder = False
  Me.GroupLevel(2).ControlSource = "Age"
  Reports("rptAgedNoticesGrouped").GroupLevel(2).SortOrder = True
End If
 
End Sub

PS... I haven't been on the scripts (it's bytes now?) for a few months...is there no longer the option to identify CODE tags as vb or sql? Thanks.
martin

[NeoPa]

Assuming this code is actually in the report "rptAgedNoticesGrouped", then your code is equivalent to :

Private Sub Report_Open(Cancel As Integer)
'changes sort order on report by changing control source for sorting/grouping
 
  Select Case Forms!frmAgedNotices.fraSort
  Case 1
    With Me.GroupLevel(1)
      .ControlSource = "Age"
      .SortOrder = True
    End With
    With Me.GroupLevel(2)
      .ControlSource = "SourceID"
      .SortOrder = False
    End With
  Case 2
    With Me.GroupLevel(1)
      .ControlSource = "SourceID"
      .SortOrder = False
    End With
    With Me.GroupLevel(2)
      .ControlSource = "Age"
      .SortOrder = True
    End With
  End Select
 
End Sub

I don't understand where Me.GroupLevel(0) went. Possibly it's in use for a higher level sort order. Possibly it doesn't actually matter that the first one is unused when working from code.

However that may be, a more succinct, and therefore less likely to cause you confusion when you come to revisit it, would be :

Private Sub Report_Open(Cancel As Integer)
'changes sort order on report by changing control source for sorting/grouping
 
  With Me.GroupLevel(1)
    .ControlSource = IIf(Forms!frmAgedNotices.fraSort = 1, "Age", "SourceID")
    .SortOrder = True
  End With
 
End Sub

You can probably lose GroupLevel(2) from the report's design.
Do test this though. I have to make some assumptions about your database. They're probably correct, but I can't be positive and I'm not in a position to test them myself.

In fact, you may even get away with the following if you always want it to sort on one of the fields (which seems implied).

Private Sub Report_Open(Cancel As Integer)
'changes sort order on report by changing control source for sorting/grouping
 
  Me.GroupLevel(1).ControlSource = _
    IIf(Forms!frmAgedNotices.fraSort = 1, "Age", "SourceID")
 
End Sub

[NeoPa]

...
PS... I haven't been on the scripts (it's bytes now?) for a few months...is there no longer the option to identify CODE tags as vb or sql? Thanks.
martin

This has been discussed (What happened to code tags ?). Please feel free to add your contribution(s) :)

[NeoPa]

Martin, you can forget most of the code in my earlier post (#13). I was misreading .SortOrder for .SortEnabled :(

The first block should do you fine though, and is a little tidier than what you had before.

[martin DH]

Martin, you can forget most of the code in my earlier post (#13). I was misreading .SortOrder for .SortEnabled :(

The first block should do you fine though, and is a little tidier than what you had before.

Thanks, NeoPa, for your responses. I agree with your tidier notion and changed the code to below which works as intended.

Private Sub Report_Open(Cancel As Integer)
'changes sort order on report by changing control source for sorting/grouping
 
  Select Case Forms!frmAgedNotices.fraSort
  Case 1
    With Me.GroupLevel(1)
      .ControlSource = "Age"
      .SortOrder = True
    End With
    With Me.GroupLevel(2)
      .ControlSource = "SourceID"
      .SortOrder = False
    End With
  Case 2
    With Me.GroupLevel(1)
      .ControlSource = "SourceID"
      .SortOrder = False
    End With
    With Me.GroupLevel(2)
      .ControlSource = "Age"
      .SortOrder = True
    End With
  End Select
 
End Sub

I don't understand where Me.GroupLevel(0) went. Possibly it's in use for a higher level sort order. Possibly it doesn't actually matter that the first one is unused when working from code.

As far as you question about Me.GroupLevel(0), and this may not be the best way to go about this, but I have that group set up in the Sorting/Grouping properties for the report. Basically, no matter what option the user selects, I want the report to group by X in GroupLevel(0) (where I have labels and text boxes in the header), and then group by the user's selection. Does that make sense?

Thanks,
martin

[NeoPa]

...
As far as you question about Me.GroupLevel(0), and this may not be the best way to go about this, but I have that group set up in the Sorting/Grouping properties for the report. Basically, no matter what option the user selects, I want the report to group by X in GroupLevel(0) (where I have labels and text boxes in the header), and then group by the user's selection. Does that make sense?

Thanks,
martin

It certainly does Martin. That's what I was hoping you'd say. It's the only scenario I could think of which indicated you had it fully sorted and there weren't further problems waiting to bite.

Nice going :)