How to access a field value in Access VB

[Colin Coleman]

MILKMAN ROUNDS PROJECT:

I have a simple project that has a customer table (Detail) and a "Rounds"
(master) table that contains the round details for the round each customer
belongs to....no problem.

I have created a query and then a report that selects all customers that
belong to a selected round number...fine

In the reports detail OnFormat event i want to reference the value of any
field in the underlying dataset for the report. The only way i have managed
to get access to any field value is to place a stupid label on every report
bound to the field i want to question, and make them invisible. then using
the construct MyLocalVar = Me![AnyFieldx] i am able to get to the value.

QUESTION: How do i get to read/use any value in a report WITHOUT loading a
label on the report IN the band im in so i can use it in VB?

Im sure this is easy peasy, but ive been trying for about 2 weeks now and
always give in and slap loads of hidden labels all over the report just so i
can get to the values in VB.

Best regards
Colin

 

[Albert D. Kallal]

For the most part, you do have to place a text box bound to the field that
you need/want to reference in the particular forms section.
(by the way, you called that a label, but I *think* you meant to say text
box control).

Usually, the above is NOT much of a problem, after all, if you are using
report, and need to report on some data, then the chances are VERY HIGH that
the fields you need to test/check in code are also likely have some
significance in the report, and thus likely to be on the report. (else, why
would the data matter!!!).

In some cases, for reason of performance, I do load up a reocrdset in the
reports on-load event, and then using a findfirst to position the record
pointer in the detail section of the report. This approach is good when your
report sizes are only going to be 1000 or less records.

So, there is not really any way references fields of the underlying
recordset unless you place a control on the report (and, that control is in
the particular section that you need).

Do note that some of my cross-tab reports have dynamic fields/headings, and
you CAN set the datasouce of a control box on a form at runtime, but I don't
think that would help in your case.

 

[Marshall Barton]

MILKMAN ROUNDS PROJECT:

I have a simple project that has a customer table (Detail) and a "Rounds"
(master) table that contains the round details for the round each customer
belongs to....no problem.

I have created a query and then a report that selects all customers that
belong to a selected round number...fine

In the reports detail OnFormat event i want to reference the value of any
field in the underlying dataset for the report. The only way i have managed
to get access to any field value is to place a stupid label on every report
bound to the field i want to question, and make them invisible. then using
the construct MyLocalVar = Me![AnyFieldx] i am able to get to the value.

QUESTION: How do i get to read/use any value in a report WITHOUT loading a
label on the report IN the band im in so i can use it in VB?

Im sure this is easy peasy, but ive been trying for about 2 weeks now and
always give in and slap loads of hidden labels all over the report just so i
can get to the values in VB.

Access reports have an optimization that omits record source
fields that are not bound to a control. Using an invisible
text box (not label) is the standard way to deal with it.>

 

[Colin Coleman]

Thanks to Albert and Marshall for the replies, As i suspected !!!

Am i correct in assuming that i cannot Create an "on the fly SQL" to build
the detail i require, Eg All house numbers in a street seperated by ~,
filtered by the round number for the detail i am currently on. ?

Colin

Thanks

MILKMAN ROUNDS PROJECT:

I have a simple project that has a customer table (Detail) and a "Rounds"
(master) table that contains the round details for the round each customer
belongs to....no problem.

I have created a query and then a report that selects all customers that
belong to a selected round number...fine

In the reports detail OnFormat event i want to reference the value of any
field in the underlying dataset for the report. The only way i have
managed
to get access to any field value is to place a stupid label on every
report
bound to the field i want to question, and make them invisible. then using
the construct MyLocalVar = Me![AnyFieldx] i am able to get to the value.

QUESTION: How do i get to read/use any value in a report WITHOUT loading a
label on the report IN the band im in so i can use it in VB?

Im sure this is easy peasy, but ive been trying for about 2 weeks now and
always give in and slap loads of hidden labels all over the report just so
i
can get to the values in VB.

Access reports have an optimization that omits record source
fields that are not bound to a control. Using an invisible
text box (not label) is the standard way to deal with it.>

 

[Marshall Barton]

That's too vague. You can certainly build an SQL statement
on the fly. This can be done as late as the report's Open
event, but no later.

OTOH, as Albert said, you can open a recordset with the
report's data and then filter it any way you want. Ignoring
the report's Filter property (which can contain the
OpenReport method's WhereCondition argument) something like
this should get what you're asking for:

Dim db As Database, rsx As Recordset, fld As Field
' .Filter = "[house number] Like '*-*' And [round] = 3"
Set db = CurrentDb()
Set rsx = db.OpenRecordset(Me.RecordSource)
For Each fld In rsx.Fields
Debug.Print fld.Name, ;
Next fld
Debug.Print
rsx.Close: Set rsx = Nothing
Set db = Nothing

I am still worried about why you need to do this instead of
having a few invisible text boxes in the report. Is it
because your table is unnormalized and has too many fields?
--
Marsh
MVP [MS Access]

Thanks to Albert and Marshall for the replies, As i suspected !!!

Am i correct in assuming that i cannot Create an "on the fly SQL" to build
the detail i require, Eg All house numbers in a street seperated by ~,
filtered by the round number for the detail i am currently on. ?

MILKMAN ROUNDS PROJECT:

I have a simple project that has a customer table (Detail) and a "Rounds"
(master) table that contains the round details for the round each customer
belongs to....no problem.

I have created a query and then a report that selects all customers that
belong to a selected round number...fine

In the reports detail OnFormat event i want to reference the value of any
field in the underlying dataset for the report. The only way i have
managed
to get access to any field value is to place a stupid label on every
report
bound to the field i want to question, and make them invisible. then using
the construct MyLocalVar = Me![AnyFieldx] i am able to get to the value.

QUESTION: How do i get to read/use any value in a report WITHOUT loading a
label on the report IN the band im in so i can use it in VB?

Im sure this is easy peasy, but ive been trying for about 2 weeks now and
always give in and slap loads of hidden labels all over the report just so
i can get to the values in VB.

Access reports have an optimization that omits record source
fields that are not bound to a control. Using an invisible
text box (not label) is the standard way to deal with it.