How do I query a property on a record source for a Access 2002 report in code?

Question

In the 'code behind' of the report is there anything I can do in code to query a property of the record source.

Sorry I'm new to access and VB, but I am wanting to achieve something along the lines of this

If Me.RecordSource["MYFieldName"] = "dan" Then
   //do something
End If

Is this possible?

Answer 1

If you want to do something every time the report comes across "dan"

Private Sub Detail_Format(Cancel As Integer, PrintCount As Integer)
  If [MyFieldName] = "dan" Then
    ' do something here
  End If
End Sub

For things like, "I want this field to have red text" you should look into Conditional Formating.

Used detail_format instead of print. [MyFieldName] instead of Me.MyFieldName

Answer 2

The only method for examining values in the recordset of a report is to have the field bound as the ControlSource of a control on the report. If it's a field that does not need to be printed on the report, then you have to add an invisible control. Deciding whether to place it in the form's header/footer or in the detail will depend on the layout of your report and what kind of data you're attempting to examine.

You used to be able to do this directly in A97 (without a hidden control), but the results were often confusing, as the data buffer behind the report is often one record ahead of what is displayed onscreen.

Also, you have to be careful which events you try to use, as the data in a report has a very different relationship to what is displayed than is the case in forms. That is, certain events cannot refer to data or controls because they happen at times where the controls do not really exist (or do not have any data in them).

In general, the only events I use in reports are OnOpen, OnNoData, OnClose and the Detail's OnFormat event, and I use them to set recordsource, controls sources, control width/visibility and to draw lines, and not much else.

EDIT: In another answer, @GuinnessFan points out something I didn't know, and that's that if you have a control on your report that is bound to the underlying field you want to refer to in code, you can do so. For instance, if you have a field called "Phone" in the underlying RecordSource and a control "txtPhone" bound to it, you can refer to Me!Phone directly in code.

My guess is that what's going on is that Access is setting up a VBA-usable wrapper only for the fields (i.e., the old hidden properties that are why you get compile-time checking of references like Me.Phone) that are used as ControlSources.

But it is still the case that to use the values from the underlying RecordSource in code, there has to be a control with that field as ControlSource, whether it's hidden or not. The new information is that you don't have to use the control name to get the value. This means it's possible to distinguish between the displayed value for a field and the value in the recordset behind the report, which are not always in alignment. I think that in general, in most situations you'll want to use the control value, as it's not clear whether the cursor of the underlying recordsource is on the same record as the one that's being displayed. Also, if you use page-level events for a report that displays more than one record on a page, it won't be obvious which record you'll be getting the data from.