In fact, yes there is.
I will copy in an example of something I've done along those lines.
Bear in mind, this was not designed for your purpose and is only included to prompt you with ideas of
some of the things that can be done.
Bear in mind that setting the report's RecordSource property will cause the underlying dataset to be re-calculated.
The help system should explain which properties are available to you.
Above all, don't be daunted by the length of the code sample, it has various concepts within but you're not likely to need all. Treat it as a 'reference library'. Also bear in mind some of the code may refer to items (procedures, tables, etc)which are not available to you.
[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, intPCs As Integer
Dim intSpare As Integer
Dim strSort As String, strWork As String, strType As String
Dim strPC1 As String, strPC2 As String
Dim blnReplacement As Boolean, blnCosts As Boolean, 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
intPCs = 0
If intFrom = 0 Then
'Form not found
intSelRange = 1
blnReplacement = True
blnCosts = True
intSort = 2
strSort = "Prod Group then Desc"
intFrom = 3
intPCs = 1
dblPC1 = 100 / 60 '40% GPM
Else
'Form found - use settings from form
With Forms("frm" & conStub)
intSelRange = IIf(.chkMainStock, 1, 0) + IIf(.chkLawForms, 2, 0)
blnReplacement = .chkReplacement
blnCosts = .chkShowCost
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
If intFrom > 1 Then
intPCs = 1
strPC1 = Nz(.txtPC1, "")
dblPC1 = IIf(intFrom = 2, (100 + CDbl(strPC1)) / 100, _
100 / (100 - CDbl(strPC1)))
strPC2 = Nz(.txtPC2, "")
dblPC2 = CDbl(0)
If strPC2 > "" Then
intPCs = 2
dblPC2 = IIf(intFrom = 2, (100 + CDbl(strPC2)) / 100, _
100 / (100 - CDbl(strPC2)))
End If
End If
End With
End If
'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(0).GroupOn = IIf(intSort < 3, 0, 1)
Select Case intSort
Case 1
GroupLevel(0).ControlSource = "PGroup"
GroupLevel(1).ControlSource = "Product"
Case 1
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 = "Columns"
Case 2
strType = "Markups"
Case 3
strType = "GPMs"
End Select
.ControlSource = ParamReplace(.ControlSource, "%S", strSort, _
"%T", strType)
End With
If intSelRange = 3 Then
FilterOn = False
Else
FilterOn = True
strWork = Split(Expression:=conFilters, Delimiter:="|")(intSelRange - 1)
Filter = ParamReplace(strWork, "%L", conLawForms)
End If
lblCost.Visible = blnCosts
txtCost.Visible = blnCosts
'Adjust widths and visibilities of various fields
'Some field attribute may need to be adjusted from their starting values
'ProdDesc.Width = 7.354cm (4,170) conDescWidth
'Unit.Width = 1.24cm (703) conUnitWidth
'PriceFields.Width = 1.199cm (680) conPriceWidth
'Expand width of Description if either Columns OR Cost not shown
lblProdDesc.Width = _
conDescWidth + IIf(intFrom > 1 Or Not blnCosts, lblCost.Width, 0)
'Set basic widths for Price Columns 1 - 2
lblPrice1.Width = conPriceWidth
lblPrice2.Width = conPriceWidth
'Set captions, width and visibility for Column fields used
If intPCs < 1 Then
lblPrice2.Caption = ParamReplace(conColLbl, "%N", 2)
lblPrice3.Caption = ParamReplace(conColLbl, "%N", 3)
txtPrice2.ControlSource = "Price2"
txtPrice3.ControlSource = "Price3"
Else
strWork = strType & vbCrLf
lblPrice2.Caption = strWork & strPC1 & "%"
txtPrice2.ControlSource = "=Round([Cost]*" & dblPC1 & ",2)"
lblPrice3.Caption = strWork & strPC2 & "%"
txtPrice3.ControlSource = "=Round([Cost]*" & dblPC2 & ",2)"
End If
lblPrice3.Visible = (intPCs > 1)
'Special handling for new style report (chkReplacement = True)
If blnReplacement Then
lblPrice2.Caption = ParamReplace("Min Sell%L%N%", "%N", strPC1, _
"%L", vbCrLf)
lblReplacement.Visible = True
End If
'Handle last 3 column fields (Only leave required fields visible)
blnVisible = (intFrom = 1)
lblPrice4.Visible = blnVisible
lblPrice5.Visible = blnVisible
lblPrice6.Visible = blnVisible
txtPrice4.Visible = blnVisible
txtPrice5.Visible = blnVisible
txtPrice6.Visible = blnVisible
'Build up positions and mirror widths & visibilities
intSpare = (Width - BuildUp() - 10) / 4
If intFrom > 1 And intSpare > 25 Then
If intSpare > conExtend Then intSpare = conExtend
lblUnit.Width = conUnitWidth + intSpare
lblCost.Width = conPriceWidth + intSpare
lblPrice1.Width = conPriceWidth + intSpare
lblPrice2.Width = conPriceWidth + intSpare
Call BuildUp
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
2
