Accessing record fields in VBA

  • Thread starter Thread starter Max Yaffe
  • Start date Start date
M

Max Yaffe

Dear Group,

I have a report Rpt with a record source, QRY. One of the fields in
QRY is [Contact]. Contact is not directly referenced on the report.
I'm trying to use VBA to create a variant of [Contact] as follows:

public function FmtContact() as String
FmtContact = TrimStr(Me.Contact)
end function

This is assigned as the control source for a control TEXT1, i.e.
TEXT1.ControlSource "=FormatAddress()"

When rendered, this fails with a meesage something like "Microsoft
Office Access can't find the field Contact...".

However, if Contact is assigned as the control source for a completely
different control, e.g. TEXT2, ControlSource "[Contact]", the original
code works fine.

Is there any way to get a reference to [Contact] in vba without having
to create several dummy fields?

Thanks,
Max
 
Max said:
I have a report Rpt with a record source, QRY. One of the fields in
QRY is [Contact]. Contact is not directly referenced on the report.
I'm trying to use VBA to create a variant of [Contact] as follows:

public function FmtContact() as String
FmtContact = TrimStr(Me.Contact)
end function

This is assigned as the control source for a control TEXT1, i.e.
TEXT1.ControlSource "=FormatAddress()"

When rendered, this fails with a meesage something like "Microsoft
Office Access can't find the field Contact...".

However, if Contact is assigned as the control source for a completely
different control, e.g. TEXT2, ControlSource "[Contact]", the original
code works fine.

Is there any way to get a reference to [Contact] in vba without having
to create several dummy fields?
Click to expand...


No. As a performance improvement, Access reports drop any
record source fields that are not referenced in a control.
 
Bummer.
Thanks anyway.
Max

Max said:
I have a report Rpt with a record source, QRY. One of the fields in
QRY is [Contact]. Contact is not directly referenced on the report.
I'm trying to use VBA to create a variant of [Contact] as follows:

public function FmtContact() as String
FmtContact = TrimStr(Me.Contact)
end function

This is assigned as the control source for a control TEXT1, i.e.
TEXT1.ControlSource "=FormatAddress()"

When rendered, this fails with a meesage something like "Microsoft
Office Access can't find the field Contact...".

However, if Contact is assigned as the control source for a completely
different control, e.g. TEXT2, ControlSource "[Contact]", the original
code works fine.

Is there any way to get a reference to [Contact] in vba without having
to create several dummy fields?
Click to expand...


No. As a performance improvement, Access reports drop any
record source fields that are not referenced in a control.
Click to expand...
 
Back
Top