Using the same recordset from the report in VBA

[mdProgrammer]

Maybe I'm asking or wording this wrong, but how do I loop through the fields that the report uses without running the query again?

I have a report that gets data from a crosstab report for a survey(thus, the fields are dynamic). So, the results might look like this:

Run1: Question1, Question2, Question3
Run2: Question2, Question3, Question7
Run3: Question4, Question5, Question6

What I'm doing right now is I have a function to select the field name from the query, and if it fails, return 0, else return the value of the field. As it stands now, it's running for each possible answer to each question. If it fails, there's a noticable delay before writing to the report. I'm looking for a faster way of doing this. Something like - (in psuedocode)

for each field in report.recordset
if field = fieldName then
' do something
else
'do something else
end if
next

I just don't know how to get the current recordset (assuming that's the right place to look, but dim rs = me.recordset doesn't seem to work) that the report uses.

 

[mdProgrammer]

Also, why is it that reports can only run functions within the report code behind itself and not the 'global' module? I have 9 sub reports that are all going to have the same function.

 

[dhookom]

You might want to search on "hookom cross tab report" for a solution that efficiently creates dynamic reports.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I would look into Duane's suggestion. However...

The report does not expose a recordset property. Not sure the reason why not. The only reason you see it in the intellisense is that it exists in an ADP. You can often create a recordset from the recordsource. Does not always work, there are limitations.

example

Private Sub Report_Load()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
  LoopFields rs
End Sub

This is an incorrect statement.

Also, why is it that reports can only run functions within the report code behind itself and not the 'global' module?

Not sure what you are trying that would make you believe this.

The above code calls this code in a standard module and prints the field names.

Public Sub LoopFields(rs As DAO.Recordset)
  Dim fld As DAO.Field
  Dim strField As String
  For Each fld In rs.Fields
     strField = strField & ", " & fld.Name
  Next fld
  MsgBox strField
End Sub

 

[mdProgrammer]

Duane's suggestion sort of helped. It wasn't 100% what I was looking for, but it did point me in the right direction.

Not 100% cleaned up yet, but this is what worked -

Private Function findField(fieldName As String) As Boolean

On Error GoTo FieldError
  
Dim rs As Recordset, db As Database
Dim i As Integer

Set rs = CurrentDb.OpenRecordset(Me.RecordSource)

With rs

    If .Fields.Count > 0 Then

    For i = 0 To .Fields.Count - 1
    
       If .Fields(i) = fieldName Then
    
            findField = True
            .Close
            Exit Function
        End If
        
    Next
    
    findField = False

    End If

End With

Exit Function

FieldError:

   With Err
      MsgBox "Find Field Error " & .Number & vbCrLf & .Description, vbOKOnly Or vbCritical, "ifFieldExists"
   End With
  
End Function

Private Sub Report_Load()

' This field exists for this survey question
If findField("Answer_1") Then
    txtP1.Caption = PerformSomeCalc()
else
    txtP1.Caption = "0"
End If

.
.
.
   
End Sub

Also, for the function thing, if I put something into Module1 (public function), the report will say that it can't find that function.

 

[MajP]

Have to see what you are doing wrong, but there is no reason that you cannot call a public function in a standard module.

You can test this simply using intellisense. In the reports module in a procedure type a module name followed by a period. The methods will come up. If they come up in intellisense they are available. My guess you stuck it in a class module.

 

[dhookom]

I think any solution with a recordset in the report is slow and inefficient.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

As I stated I suggest the OP uses your approach. I only want to dispel the idea that you can not call a public method from a report and that you can often use the recordsource property to get a report recordset.

 

[dhookom]

My reply regarding the recordset was more directed to

Duane's suggestion sort of helped. It wasn't 100% what I was looking for, but it did point me in the right direction.

There was no suggestion of why it wasn't 100% or how the code resembled my crosstab report solution.

Duane
Hook'D on Access
MS Access MVP

 

[mdProgrammer]

Your code was referencing a table, but I wanted to use the recordset found in the report (which I found on another site, and was faster than what I was usiong). Also, not every answer in this survey database has percentages. Some answers have a list of counts, and a top 3 list of counts.

At any rate, it is working now.