By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Changing report source query at runtime

P: 8
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
Sep 20 '07 #1
Share this Question
Share on Google+
16 Replies


P: 8
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
Sep 20 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
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 :)
Sep 20 '07 #3

100+
P: 114
Could one of you post an example of code for changing a report's record source on open?

Thank you.
martin
May 28 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
[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:="
Expand|Select|Wrap|Line Numbers
  1. ", _
  2.                       Domain:="[tblReport]", _
  3.                       Criteria:="[ReportName]='" & Name & "'") & _
  4.                                 Format(Date, " - d mmmm yyyy")
  5. End Sub
May 28 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
It doesn't need to be quite that complicated obviously :D
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.     Dim intTest As Integer
  3.  
  4.     On Error Resume Next
  5.     'If next line fails then intTest stays 0 - otherwise it will be > 0
  6.     intTest = Forms("frmMyForm").fraFrom
  7.     On Error GoTo 0
  8.     'First step is to change the Recordsource if required...
  9.     If intTest = 0 Then
  10.         Me.RecordSource = "qryMyJobAll"
  11.     Else
  12.         Me.RecordSource = "qryMyJob"
  13.     End If
  14. End Sub
May 28 '08 #6

100+
P: 114
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. 'changes sort order on report by changing record source
  3. If Forms![frmAgedNotices]!fraSort = 1 Then
  4.   Me.RecordSource = "qryAgedNoticesGroupedAge"
  5. ElseIf Forms![frmAgedNotices]!fraSort = 2 Then
  6.   Me.RecordSource = "qryAgedNoticesGroupedAgency"
  7. End If
  8.  
  9. End Sub
Thanks!
May 28 '08 #7

Expert Mod 2.5K+
P: 2,545
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?
...
May 28 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
... 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.
May 29 '08 #9

100+
P: 114
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
May 29 '08 #10

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 29 '08 #11

100+
P: 114
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. 'changes sort order on report by changing control source for sorting/grouping
  3.  
  4. If Forms![frmAgedNotices]!fraSort = 1 Then
  5.   Me.GroupLevel(1).ControlSource = "Age"
  6.   Reports("rptAgedNoticesGrouped").GroupLevel(1).SortOrder = True
  7.   Me.GroupLevel(2).ControlSource = "SourceID"
  8.   Reports("rptAgedNoticesGrouped").GroupLevel(2).SortOrder = False
  9. ElseIf Forms![frmAgedNotices]!fraSort = 2 Then
  10.   Me.GroupLevel(1).ControlSource = "SourceID"
  11.   Reports("rptAgedNoticesGrouped").GroupLevel(1).SortOrder = False
  12.   Me.GroupLevel(2).ControlSource = "Age"
  13.   Reports("rptAgedNoticesGrouped").GroupLevel(2).SortOrder = True
  14. End If
  15.  
  16. 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
May 29 '08 #12

NeoPa
Expert Mod 15k+
P: 31,186
Assuming this code is actually in the report "rptAgedNoticesGrouped", then your code is equivalent to :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. 'changes sort order on report by changing control source for sorting/grouping
  3.  
  4.   Select Case Forms!frmAgedNotices.fraSort
  5.   Case 1
  6.     With Me.GroupLevel(1)
  7.       .ControlSource = "Age"
  8.       .SortOrder = True
  9.     End With
  10.     With Me.GroupLevel(2)
  11.       .ControlSource = "SourceID"
  12.       .SortOrder = False
  13.     End With
  14.   Case 2
  15.     With Me.GroupLevel(1)
  16.       .ControlSource = "SourceID"
  17.       .SortOrder = False
  18.     End With
  19.     With Me.GroupLevel(2)
  20.       .ControlSource = "Age"
  21.       .SortOrder = True
  22.     End With
  23.   End Select
  24.  
  25. 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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. 'changes sort order on report by changing control source for sorting/grouping
  3.  
  4.   With Me.GroupLevel(1)
  5.     .ControlSource = IIf(Forms!frmAgedNotices.fraSort = 1, "Age", "SourceID")
  6.     .SortOrder = True
  7.   End With
  8.  
  9. 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).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. 'changes sort order on report by changing control source for sorting/grouping
  3.  
  4.   Me.GroupLevel(1).ControlSource = _
  5.     IIf(Forms!frmAgedNotices.fraSort = 1, "Age", "SourceID")
  6.  
  7. End Sub
May 30 '08 #13

NeoPa
Expert Mod 15k+
P: 31,186
...
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) :)
May 30 '08 #14

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 30 '08 #15

100+
P: 114
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. 'changes sort order on report by changing control source for sorting/grouping
  3.  
  4.   Select Case Forms!frmAgedNotices.fraSort
  5.   Case 1
  6.     With Me.GroupLevel(1)
  7.       .ControlSource = "Age"
  8.       .SortOrder = True
  9.     End With
  10.     With Me.GroupLevel(2)
  11.       .ControlSource = "SourceID"
  12.       .SortOrder = False
  13.     End With
  14.   Case 2
  15.     With Me.GroupLevel(1)
  16.       .ControlSource = "SourceID"
  17.       .SortOrder = False
  18.     End With
  19.     With Me.GroupLevel(2)
  20.       .ControlSource = "Age"
  21.       .SortOrder = True
  22.     End With
  23.   End Select
  24.  
  25. 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
May 30 '08 #16

NeoPa
Expert Mod 15k+
P: 31,186
...
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 :)
Jun 2 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.