Accessing Current Record within On Format

[senort01]

Hi,

Stupid question of someone just starting to program in VBA.

I just want to access the current record in a report within the On Format event so that I can dynamically create various variables based on data stored in a database. I, however, don't have a clue how to reference the data and everything I try results in Run time error '2465: Microsoft Access can't find the field errors.

Is there a tutorial out there that answers this, what seems like, simple question?

I've tried me.[field_name], the field name, etc.

Thanks.

[ADezii]

Just a general response, to access data in every Record in a report, then make decisions based on that data, you could use code similar to the following:

If Me![Date_Due] >= Date Then
  Me![txtStatus] = "Overdue"
Else
  Me![txtStatus] = "On Time"
End If

P.S. - Depending on your circumstances, you may be better off using the Detail Section's Print() Event as opposed to the Format() Event.

[senort01]

Just a general response, to access data in every Record in a report, then make decisions based on that data, you could use code similar to the following:

If Me![Date_Due] >= Date Then
  Me![txtStatus] = "Overdue"
Else
  Me![txtStatus] = "On Time"
End If

P.S. - Depending on your circumstances, you may be better off using the Detail Section's Print() Event as opposed to the Format() Event.

Thanks.

Is the only mistake I was making that I was using a period as opposed to an !? Or is there something else I need to do to make the record visible within the on format event?

[ADezii]

Thanks.

Is the only mistake I was making that I was using a period as opposed to an !? Or is there something else I need to do to make the record visible within the on format event?

You can also use a period, if you like.

[Stewart Ross]

Hi. As ADezii's reply shows, accessing the controls on your report is straightforward. From what you have written it would appear that the cause may lie elsewhere. One possibility is that you may be trying to access a field in the underlying recordset for that report which does not have a matching control of that name on the report, or trying to access a field from another table which is not part of the report's recordset.

What Me.yourcontrolname means is "the value of control 'yourcontrolname' located on the current report". It does not mean the value of field X, where X is a field that has no matching control on the report.

You will see a list of the controls on your report as you type if you use the period notation - for instance, typing me.m in the VB editor will show all the objects (including controls) beginning with the letter m that you can access. Access will replace any spaces in the names with underscores, like this

me.a_control_name_with_spaces

I'd suggest you check that the control you are trying to access actually exists simply by trying the matching I have suggested. If it doesn't all you need to do is to drag the name of the field from the field list onto your report to create a textbox control for that field on your report (assuming that the field does exist in your report's recordsource query).

If the control really does exist and you are getting such errors I am not at all certain what more to suggest. Perhaps if that happens you could post the code you are trying to use so we can look at what may be going wrong.

-Stewart

[senort01]

Thanks everyone.

I must have mess something else up as I completely started over and now I can see the fields.

Thanks.

[senort01]

I spoke too fast.

My problem has reemerged.

Here is the very simple code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim banner As String

If Me.TAGed = Yes Then
banner = "Foo"
Else
banner = "Bar"
End If

Me.TestText = banner


End Sub


It works for some of the fields within the result set, but, others it says it can't locate the variable.

It autocompletes with any of the fields within the field list, and, all of the fields show up in the field list. Do I need to drop some hidden control for every field in other to access it? Or, is there a way I can directly access the returned field?

[Stewart Ross]

Hi. Problem is that you are trying to compare the value of TAGed to a constant called Yes - which simply does not exist in VBA. This results in the error you are experiencing about missing variables.

If TAGed is a Boolean value (one which is defined in the underlying table as a Yes/No field) its values can be -1 (represented by the constant True) or 0 (represented by the constant False.

You can test its value in a number of ways, all equivalent:

If Me.TAGed = True Then X Else Y
If Me.TAGed = -1 Then X Else Y
If Me.TAGed Then X Else Y

It is the last of these I would recommend to you, as it is redundant to check a value of True or False against the constant True.

-Stewart