function calls in a report?

[kvon]

Hello,



Total MS Access newbie - former software engineer, C++ mostly, no db experience...

I've generated a report, and would like to call a function to calculate the value to display for the record. Writing the code is no problem, I just have no idea where to put it, or how to get the references I need - currentrecord, current form, etc. So:
- in the VBA editor, do I need to write the function as a class object for the report, and if so - can you write functions for reports that aren't event handlers? OR
- do I create a module and write a (public?) function in the module? I did this and managed to return a dummy value to display, but couldn't figure out how to get reference to the current record - which I need in order to access the fields for the calculation.

Sidebar - pretty sure I can't do what I'm trying to do with a query, but maybe I can? In the query used to generate my report, there's several fields that are the same type (approx 25 fields) and I just want to count how many are empty or non-null for a specified subset of those fields, and print the count in the report. Seems best done with a function...?

Many thanks for any info...

[RuralGuy]

Maybe Paul's page will help get you started. http://www.baldyweb.com/Function.htm

[kvon]

Maybe Paul's page will help get you started. http://www.baldyweb.com/Function.htm

Hey, thanks for the info - still spinning my wheels though. I'm thinking maybe the way I'm trying to do this is the wrong approach. (But that would be the Lotus database, which is way off. {rim shot} Sorry )

I've got a text box in the detail section of this report -- what I want to do is to bind that text box to a calculation, rather than to a specific field in the attached query results table. So -- that means 1 of 2 ways (I think):
1) write the function as an event handler (?) in the report object - and if that's the case it's not clear that there's a handler in the report object that would handle this... OR
2) write it as a public function - but then I can't figure out how to send in a reference to the current record.

So how would i do this? Seems like the event handler (if there is to be one) should belong to the report object, and be called every time the text box is filled...

I'll keep digging, but I'm still totally confused -- thanks...

[RuralGuy]

Look at the Print event of the detail section. You should get one of those for every record in the RecordSource of the report.

[pbaldy]

I think you could also have a textbox in the detail section with a control source of:

=FunctionName(Arguments)

That should fire for every record.

[kvon]

I think you could also have a textbox in the detail section with a control source of:

=FunctionName(Arguments)

That should fire for every record.

That really sounds like the way I would do it - but how to I get the reference to the current record inside {FunctionName}? (Don't want to pass all the arguments in - there's a lot of them).

Thanks!

[pbaldy]

=FunctionName(Me.IDFieldName)

would pass the contents of the ID field to the function. The function would obviously have to be set up to accept it.

[kvon]

=FunctionName(Me.IDFieldName)

would pass the contents of the ID field to the function. The function would obviously have to be set up to accept it.

OK, so are you recommending that I access the current record through the IDFieldName parameter, something like IDFieldName.Parent.CurrentRecord ...?

[pbaldy]

Well, I wouldn't say recommending, because I thought that's what you were asking for. That said, if you don't want to pass all the fields to the function, it would make sense to pass an ID field and then get the field data within the function. You couldn't do it with that syntax though. You would open a recordset on it. Here's a function that's not doing the exact same thing you want, but maybe gives you an idea of the basic structure you need:

Public Function CheckAbbr(varInput As Variant) As Variant
  Dim strSQL                  As String
  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset

  On Error GoTo ErrorHandler
  
  Set db = CurrentDb()

  strSQL = "SELECT AbbrDesc FROM tblAbbreviations WHERE AbbrID = '" & Nz(varInput, "") & "'"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If Not rs.EOF Then
    CheckAbbr = rs!AbbrDesc
  Else
    CheckAbbr = varInput
  End If

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Function

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description & " in CheckAbbr "
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Function

[RuralGuy]

Paul's ideas are always great. I was suggesting using the Print event of the Detail section because all of the fields of the current record would be directly available for inspection at that time, assuming you have bound the fields to controls on your report. Access will not put the fields in the Recordset of your report if you don't bind each field you want to a control. The controls can be invisible but they need to be defined. Just another way to approach the problem.

[kvon]

I did end up using the Print detail for the reason you mentioned - it gave me the most direct access to the current record, and all of its fields that I needed. thanks!

[RuralGuy]

You're very welcome. Glad to hear you got it sorted.