By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Accessing Current Record within On Format

P: 12
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.
Nov 12 '08 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. If Me![Date_Due] >= Date Then
  2.   Me![txtStatus] = "Overdue"
  3. Else
  4.   Me![txtStatus] = "On Time"
  5. 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.
Nov 12 '08 #2

P: 12
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:
Expand|Select|Wrap|Line Numbers
  1. If Me![Date_Due] >= Date Then
  2.   Me![txtStatus] = "Overdue"
  3. Else
  4.   Me![txtStatus] = "On Time"
  5. 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?
Nov 12 '08 #3

ADezii
Expert 5K+
P: 8,597
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.
Nov 12 '08 #4

Expert Mod 2.5K+
P: 2,545
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
Nov 12 '08 #5

P: 12
Thanks everyone.

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

Thanks.
Nov 12 '08 #6

P: 12
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?
Nov 12 '08 #7

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. If Me.TAGed = True Then X Else Y
  2. If Me.TAGed = -1 Then X Else Y
  3. 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
Nov 13 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.