Associating Report field to VB code - where 2 find good reference

[blackduck603]

Hi,
I am fairly new to Access programming and Reports. I have inherited a project and am trying to understand some of the inner workings. There is a field named Overall in the Detail section of my report. The Control source for this field is SumOfbalance (and no - I did NOT forget to capitalize the b in balance). After a good deal of digging, I managed to find a reference to this field in a query in the VB code associated with this report. A stub of the VB code is shown below.

Private Sub Report_Open(Cancel As Integer)

On Error GoTo Err_Report_Open

Dim strSQL As String
Dim strInnerSQL As String
Dim strFROM As String
Dim strWHERE As String
Dim strGROUPBY As String
Dim strORDERBY As String

'-- Create SQL for report record source


strORDERBY = " ORDER BY [AcctNumber] & [idsuffix]"

strSQL = "SELECT DISTINCT tblAccount.Accttyp, [AcctNumber]" & _
" & [idsuffix] AS ACCT_REC, tblAccount.primname," & _
" tblAccount.street1, tblAccount.street2, tblAccount.city," & _
" tblAccount.state, tblAccount.zip," & _
" IIf(IsNull(Sum(qryAcctRec.balance)),0,Sum(qryAcctR ec.balance))" & _
" AS SumOfbalance"
strFROM = " FROM tblAccount LEFT JOIN qryAcctRec ON" & _
" tblAccount.pkeyAccount = qryAcctRec.fkeyAccount"
strGROUPBY = " GROUP BY tblAccount.Accttyp," & _
" [AcctNumber] & [idsuffix], tblAccount.primname," & _
" tblAccount.street1, tblAccount.street2," & _
" tblAccount.city, tblAccount.state, tblAccount.zip" & _
" HAVING (((tblAccount.Accttyp) = 'EC'))"
Me.RecordSource = strSQL & strFROM & strGROUPBY & strORDERBY

'-- Print the report creation date on each page
Me.lblPrintedOn.Caption = "Created " & Now()

Exit_Report_Open:
Exit Sub

Err_Report_Open:
MsgBox Err.Description
Resume Exit_Report_Open
End Sub

I sort of understand how it works, but am a bit perplexed as to how someone would ever figure this out since I have yet to find any documentation that explains this way of implementing a report - essentially associating a report field with a variable that is defined in an AS clause of a query in the report's Visual Basic code.

I appreciate any information on where I might find additional information on advanced reporting topics like this.

Thanks....

[jmpigott]

I'm far from an expert. However, I do recognize that this code is merely creating a query programmatically your field in question is calculated. So it is similar to
everything you would do when you build a query.

expr1: [variable1]+[variable2]

trouble: [dogs]+[cats] ; )

In agreement, I have no idea why the query is coded except that it probably relates to a form used to create a report. It probably makes the form more modular, flexible. the IIf statement is just ensuring you don't get an #error if in my silly formula there were no dogs and the field is null. All this you probably know.

[Jim Doherty]

Hi,
I am fairly new to Access programming and Reports. I have inherited a project and am trying to understand some of the inner workings. There is a field named Overall in the Detail section of my report. The Control source for this field is SumOfbalance (and no - I did NOT forget to capitalize the b in balance). After a good deal of digging, I managed to find a reference to this field in a query in the VB code associated with this report. A stub of the VB code is shown below.

Private Sub Report_Open(Cancel As Integer)

On Error GoTo Err_Report_Open

Dim strSQL As String
Dim strInnerSQL As String
Dim strFROM As String
Dim strWHERE As String
Dim strGROUPBY As String
Dim strORDERBY As String

'-- Create SQL for report record source


strORDERBY = " ORDER BY [AcctNumber] & [idsuffix]"

strSQL = "SELECT DISTINCT tblAccount.Accttyp, [AcctNumber]" & _
" & [idsuffix] AS ACCT_REC, tblAccount.primname," & _
" tblAccount.street1, tblAccount.street2, tblAccount.city," & _
" tblAccount.state, tblAccount.zip," & _
" IIf(IsNull(Sum(qryAcctRec.balance)),0,Sum(qryAcctR ec.balance))" & _
" AS SumOfbalance"
strFROM = " FROM tblAccount LEFT JOIN qryAcctRec ON" & _
" tblAccount.pkeyAccount = qryAcctRec.fkeyAccount"
strGROUPBY = " GROUP BY tblAccount.Accttyp," & _
" [AcctNumber] & [idsuffix], tblAccount.primname," & _
" tblAccount.street1, tblAccount.street2," & _
" tblAccount.city, tblAccount.state, tblAccount.zip" & _
" HAVING (((tblAccount.Accttyp) = 'EC'))"
Me.RecordSource = strSQL & strFROM & strGROUPBY & strORDERBY

'-- Print the report creation date on each page
Me.lblPrintedOn.Caption = "Created " & Now()

Exit_Report_Open:
Exit Sub

Err_Report_Open:
MsgBox Err.Description
Resume Exit_Report_Open
End Sub

I sort of understand how it works, but am a bit perplexed as to how someone would ever figure this out since I have yet to find any documentation that explains this way of implementing a report - essentially associating a report field with a variable that is defined in an AS clause of a query in the report's Visual Basic code.

I appreciate any information on where I might find additional information on advanced reporting topics like this.

Thanks....

Looking at the post there is nothing specifically unorthodox about the authors implementation of that reporting technique. As has been already mentioned the reports recordsource is merely being built on the fly when the report runs and the SQL syntax supports the reports fielded output.

The syntax is no different to that which consitutes a saved query in Access except that it is being used in code. It would seem to me the author has decided to take the view that the SQL, given that it is fixed and specific to the report, might be best saved with the structure of the report itself rather than as saved SQL (in favour of this decision) over time this could have, in the absence of that decision added potentially to a very long list of queries which would have to be scrolled through in the query window (reasoning based on the projected life cycle of the software?). Alternatively the designer might have built 50 reports all the same as that one.... the only marked difference being 50 hard coded HAVING clauses.

A pat on the back for perception of system object overload some might say in the early days of design and a smack on the wrist from those who asked that same designer for 49 extra reports once every two weeks for the last two years having stipulated originally that they definitely only needed one or two maybe three at the most and now..... because they didnt think far enough ahead, the designer must ....in the best interest of the organisation you understand....get it in the neck. LOL

Some others might also take the view that any SQL is best saved for performance reasons as a saved query in a centralised area so that it can be easily referenced.

The long and short of this is this, the technique employed is nothing uncommon yes it is advanced Access and 'will' easily be recognised by individuals with the relevant skillset. The documentation, disciplines and adherence to business rules employed in the design concept naming convention as with anything else is another matter entirely. as I am sure you appreciate and which I sense maybe where your perplexion lies?

As for readable material I have a huge stack of 4" thick manuals here ready for the next car boot sale if your interested? :))))

Regards

Jim :))