=<expr> function call - how to pass record?

[Erik Johnson]

Greetings,

I am new to the MS Access product, but have experience writing DB
middle ware in other environments.

Right now my challenge is this: I want to write an expression in a
report control that is a complex expression of the record fields. I
see how to directly write an expression referring to the record fields
like [Customer Name] and so on, and I also see how to make a function
call passing one or more fields as function arguments as above, but
what I really want to do is pass a reference to the current record,
and then within my function be able to refer to all the different
fields of that record.

I'm thinking there's probably some very simple way to do this, but
it's syntax is not guessable to me and I'm not having much luck with
Microsoft "Help".

For example, in the 'Detail' section of my report, what I want to
print is:

=CategoryA([record])


Then in my Module, I want to define a function something like this:

Function CategoryA(rec)
CategoryA = rec.speed + rec.weight ' + ... some nasty formula
involving many record fields
End Function

What is the syntax to pass the current record as a whole and then to
refer to it's fields?

Thanks for any help you can provide!

-ej

 

[Marshall Barton]

I am new to the MS Access product, but have experience writing DB
middle ware in other environments.

Right now my challenge is this: I want to write an expression in a
report control that is a complex expression of the record fields. I
see how to directly write an expression referring to the record fields
like [Customer Name] and so on, and I also see how to make a function
call passing one or more fields as function arguments as above, but
what I really want to do is pass a reference to the current record,
and then within my function be able to refer to all the different
fields of that record.

I'm thinking there's probably some very simple way to do this, but
it's syntax is not guessable to me and I'm not having much luck with
Microsoft "Help".

For example, in the 'Detail' section of my report, what I want to
print is:

=CategoryA([record])


Then in my Module, I want to define a function something like this:

Function CategoryA(rec)
CategoryA = rec.speed + rec.weight ' + ... some nasty formula
involving many record fields
End Function

What is the syntax to pass the current record as a whole and then to
refer to it's fields?

No such syntax.

BUT, there is no need either. All of the fields (and
controls) in a report's current record are available by just
referencing the report. This means that your function can
use code like:

Function CategoryA()
With Reports!yourreport
CategoryA = .speed + .weight + . . .
. . .
End With
End Function

 

[Erik Johnson]

That works!

Thanks very much for your reply, Marsh!

-ej

 

[Erik Johnson]

Actually, I think I have to take that back (at least partially).
Initially I thought I had the solution I was looking for because the
functions I was testing with were referring to fields that I had in my
detail section from before. I can construct functions that refer to
controls within the detail section of my report but, I am trying to
create summary reports where my function analyzes the individual
fields and comes up with with a value representing multiple fields.
That's the value I want to be in the detail section of my report, not
all the individual column values. I don't want to show the individual
fields in the detail section of my report, and so I never want to put
a control in there that holds the value for the individual fields.

It's those named controls that are being referenced in the exacmple
you gave me (as can be verified by deleting a control the function is
referring to). So, I am back to the same question: How can I refer to
columns on the current record (outside of controls that have been
plopped into the detail section of the report)?

-ej

 

[Marshall Barton]

Actually, I think I have to take that back (at least partially).
Initially I thought I had the solution I was looking for because the
functions I was testing with were referring to fields that I had in my
detail section from before. I can construct functions that refer to
controls within the detail section of my report but, I am trying to
create summary reports where my function analyzes the individual
fields and comes up with with a value representing multiple fields.
That's the value I want to be in the detail section of my report, not
all the individual column values. I don't want to show the individual
fields in the detail section of my report, and so I never want to put
a control in there that holds the value for the individual fields.

It's those named controls that are being referenced in the exacmple
you gave me (as can be verified by deleting a control the function is
referring to). So, I am back to the same question: How can I refer to
columns on the current record (outside of controls that have been
plopped into the detail section of the report)?

Ahhh yes.

First, the code is referring to either the field or the
control with whatever name you use in the code. If a field
in the record source and a control in the report have the
same name, then the code will reference the control.

The serious issue is that (unlike forms) Access tries to
optimize report performance by not retrieving fields that
are not used in a control. That means that you need to
include each field in either a bound control or use a text
box with an expression that refers to a bunch of fields in
an expression such as
=f1 & f2 & ...
Either way, you can make those control(s) invisible so they
don't get in the way of the stuff you want to display.