fields in the RecordSource

[Bill]

I get a runtime 2465 error in attempting to
reference one of the fields in the RecordSource
of a report.

E.g.,
Dim intCount As Integer

intCount = Me.CountField

Access can't find the referenced field, "CountField".
Yet, using the "Me." autofill shows all the field
names in the RecordSource of the report.

What am I missing?

Bill

 

[Ken Snell \(MVP\)]

Which event is being used to run that code? If it's the Open event, the
report's fields are not available to the VBA code at that time. You more
likely want to use the Format or Print event of a section of the report that
might be appropriate for running the code.

 

[Bill]

Hi Ken,

The code is running in the Details OnFormat event, which is where
I'd expect to not have any problems. Since you seem to agree with
that as an appropriate section of code I'm now really puzzled.

Bill

 

[Bill]

Also fails when run in the OnPrint event!

Hi Ken,

The code is running in the Details OnFormat event, which is where
I'd expect to not have any problems. Since you seem to agree with
that as an appropriate section of code I'm now really puzzled.

Bill

 

[Ken Snell \(MVP\)]

Post the SQL statement of the report's RecordSource query.

 

[Bill]

Okay, you asked for it!

SELECT ReunionRoster.RecordID, ReunionRoster.FirstName,
ReunionRoster.LastName, ReunionRoster.CurrentName, ReunionRoster.Address,
ReunionRoster.City, ReunionRoster.State, ReunionRoster.Zip,
ReunionRoster.PhoneNo, ReunionRoster.[E-mailAddr],
ReunionRoster.[e-mailListed], ReunionRoster.AddrListed,
ReunionRoster.AddrShare, ReunionRoster.[e-mailShare],
ReunionRoster.[Spouse-Guest], ReunionRoster.FridaySocial,
ReunionRoster.SatBrunchTour, ReunionRoster.SatDinner, ReunionRoster.Book,
ReunionRoster.PaymtAmount, ReunionRoster.NotAttending,
ReunionRoster.InvReturned, ReunionRoster.deceased
FROM ReunionRoster
ORDER BY ReunionRoster.LastName;


I'm attempting to reference 1 of the three fields:

ReunionRoster.FridaySocial, ReunionRoster.SatBrunchTour,
ReunionRoster.SatDinner

Bill

 

[Bill]

So, in the report's Detail OnFormat, I have a statement
like:

intCount = Me.SatDinner

What I really want, aside from trying to get this simple
code to run is to make a more general reference from
the "Fields Collection", like Me.Fields(strMyFieldName)

Bill

Okay, you asked for it!

SELECT ReunionRoster.RecordID, ReunionRoster.FirstName,
ReunionRoster.LastName, ReunionRoster.CurrentName, ReunionRoster.Address,
ReunionRoster.City, ReunionRoster.State, ReunionRoster.Zip,
ReunionRoster.PhoneNo, ReunionRoster.[E-mailAddr],
ReunionRoster.[e-mailListed], ReunionRoster.AddrListed,
ReunionRoster.AddrShare, ReunionRoster.[e-mailShare],
ReunionRoster.[Spouse-Guest], ReunionRoster.FridaySocial,
ReunionRoster.SatBrunchTour, ReunionRoster.SatDinner, ReunionRoster.Book,
ReunionRoster.PaymtAmount, ReunionRoster.NotAttending,
ReunionRoster.InvReturned, ReunionRoster.deceased
FROM ReunionRoster
ORDER BY ReunionRoster.LastName;


I'm attempting to reference 1 of the three fields:

ReunionRoster.FridaySocial, ReunionRoster.SatBrunchTour,
ReunionRoster.SatDinner

Bill

Post the SQL statement of the report's RecordSource query.

 

[Larry Linson]

I get a runtime 2465 error in attempting to
reference one of the fields in the RecordSource
of a report.

Just a guess: Is this Field in the RecordSource but not used as Control
Source for a Control on the Report? That's been a subtle _feature_ of
Access -- you can include a Field in the RecordSource of a Form and refer to
it from code behind the Form; but Reports are "smart", and "looking out for
your best interests" so they ignore any Fields in the RecordSource of the
Report, but not used.

An easy way to determine, if you aren't certain, is to display the Field
List, drag that Field onto the surface of the Report, then run it again. If
you don't get the error, leave it there, but set its Visible property to
"No" and you should be OK until the next time you stumble over it...

Larry Linson
Microsoft Access MVP

 

[Bill]

Hi Larry,

I had a suspicion that something like that was biting me
on the tail. I dragged the three fields of interest onto the
header section and the references to those fields worked
as desired..........I have a vague reccolection of something
like that having happened many "moons ago" in A2K with
pretty much the same solution.

Now, having done that, is there a way to reference the
fields collection so that I can make the reference using a
string variable? like: Me.Fields(strFieldName)

Thanks,
Bill

 

[Marshall Barton]

Now, having done that, is there a way to reference the
fields collection so that I can make the reference using a
string variable? like: Me.Fields(strFieldName)

Just use:
Me(strFieldName)

if strFieldName is the name of a control, it will retrieve
it's value. Otherwise it will get the value of the field.
Often, it doesn't matter because they have the same value.

 

[Bill]

Thanks Marsh, Larry and Ken

Code and report now behaving as designed.

Bill