MS Access code

In this chapter we show some real word MS Access code to handle the reporting events.

Event to control how often a group is processed

' This event is used to control how often a group will be processed

' The most important values of parameter 'State' are:

' 0=BeforeProcessGroup - check if we are at EOF

' 8=AfterProcessGroup - move to next record

Private Sub WPDLLInt1_OnReportState(ByVal NAME As String, ByVal State As Long, ByVal Band As Object, Abort As Boolean)

If State = 0 Then

    Abort = RepRS.EOF

End If

If State = 8 Then

    RepRS.MoveNext

    Abort = RepRS.EOF

End If

End Sub

Event to read the text of a field

' This event is used to retrieve the value of a certain field

Private Sub WPDLLInt1_OnFieldGetText(ByVal Editor As Long, ByVal FieldName As String, ByVal Contents As Object)

Dim ContentsObj As IWPFieldContents

Dim dn As String

Dim fn As String

Dim i As Integer

Dim j As Integer

Set ContentsObj = Contents

 j = -1

 dn = StripDBName(FieldName, fn)

For i = 0 To RepRS.Fields.Count - 1

  If (RepRS.Fields(i).SourceTable = dn) And _

      (RepRS.Fields(i).SourceField = fn) Then

        j = i

  End If

Next

If j >= 0 Then

    ContentsObj.StringValue = RepRS.Fields(j).Value

Else

  ' DO NOT ASSIGN ANYTHING HERE

  ' Otherwise Variables are not found

  ' ContentsObj.StringValue = 'unknown:' + FieldName

End If

End Sub

Event to read the value of a variable (used in formulas)

Private Sub WPDLLInt1_OnReadFormulaVar(ByVal FieldName As String, ByVal GroupContext As Object, ByVal BandContext As Long, Value As Double)

Dim dn As String

Dim fn As String

Dim i As Integer

Dim j As Integer

 j = -1

 dn = StripDBName(FieldName, fn)

For i = 0 To RepRS.Fields.Count - 1

  If (RepRS.Fields(i).SourceTable = dn) And _

      (RepRS.Fields(i).SourceField = fn) Then

        j = i

  End If

Next

If j >= 0 Then

    Value = RepRS.Fields(j).Value

End If

End Sub

The last 2 event handler require this utility function

Private Function StripDBName(aName As String, ByRef aFieldName As String) As String

Dim i As Integer

Dim DBName As String

Dim f As String

 DBName = ""

 i = InStr(aName, ".")

If i > 0 Then

   DBName = Left$(aName, i - 1)

   aFieldName = Mid$(aName, i + 1)

End If

 StripDBName = DBName

End Function

Also required are 2 global variables

Dim RepRS As Recordset

Dim RepRSName As String

The report is started using this code

Private Sub Create_Report_Click()

Const strQueryName = "ORDERS Query"

Dim db As Database

Dim td As WPDLLInt

Set td = WPDLLInt1.Object

Set db = CurrentDb() ' Open pointer to current database

 RepRSName = "ORDERS"

Set RepRS = db.OpenRecordset(strQueryName) ' Open recordset on saved query

' Now Create the report. MoveNext and EOF is used in events!

td.Report.CreateReport

 RepRS.Close

 db.Close

End Sub