A general question about report recordset binding and dataavailability

[Greg Crowe]

I've worked with Access way too long not to know this answer to this..

We run a lot of corp reporting through various legacy access front
ends to SQL data (linked tables, all ADO code in Access 2007).

On Report.Open we bind the RecordSource object to a SQL view. I cannot
access fields in that view in VBA unless I bind said fields to a
control on the report. Is this normal? Am I missing something stupid
obvious?


Example - set RecordSource, if some condition (WorkOrderStatus) is
met, make that line gray on the report:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "dbo.view_Blanket_Wrap"
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me!WorkOrderStatus = 4 Then
Me.Detail.BackColor = 12632256
Else
Me.Detail.BackColor = 16777215
End If
End Sub

This works if I bind WorkOrderStatus to a control on the report.
Additionally, none of the other fields in the SQL view
dbo.view_Blanket_Wrap are available unless bound to report objects. Am
I doing it wrong?

Regards,
- Greg Crowe

 

[Albert D. Kallal]

This works if I bind WorkOrderStatus to a control on the report.
Additionally, none of the other fields in the SQL view
dbo.view_Blanket_Wrap are available unless bound to report objects. Am
I doing it wrong?

Yes, the above is the case even when not using sql server. In other words, a
standard report based on a local table will not allow your code to "use"
fields from the reports datasource unless you have a text box placed on the
report.

Note that we don't have this problem in forms. However, in forms keep in
mind if you do change the forms datasource, then make sure you use
me!FieldName, as me.FieldName needs to be resolved at compile time and
that's not always possible when changing the forms recordsouce with code....

 

[Hans Up]

This works if I bind WorkOrderStatus to a control on the report.
Additionally, none of the other fields in the SQL view
dbo.view_Blanket_Wrap are available unless bound to report objects. Am
I doing it wrong?

Can you retrieve the value of WorkOrderStatus via the CurrentRecord
property of your report's RecordSource?

 

[Greg Crowe]

Yes, the above is the case even when not using sql server. In other words, a

standard report based on a local table will not allow your code to "use"
fields from the reports datasource unless you have a text box placed on the
report.

Thank you, Albert. I was almost certain this was the case.

Note that we don't have this problem in forms. However, in forms keep in
mind if you do change the forms datasource, then make sure you use
me!FieldName, as me.FieldName needs to be resolved at compile time and
that's not always possible when changing the forms recordsouce with code....

I have learned over the years to be very wary about on-demand compile
in Access. Thanks for the pointer!
- Greg

 

[Greg Crowe]

Can you retrieve the value of WorkOrderStatus via the CurrentRecord
property of your report's RecordSource?

Hans, I am not sure, but I think I have my answer from Albert.

How would one go about using CurrentRecord to pull the field value?

 

[Hans Up]

Hans, I am not sure, but I think I have my answer from Albert.

I think so, too, Greg.

How would one go about using CurrentRecord to pull the field value?

Based on Albert's reply, I think it's probably not possible. Sorry,
Greg. I'm especially ignernt about Access Reports, and shouldn't have
posted at all.

Hans

 

[Albert D. Kallal]

Thank you, Albert. I was almost certain this was the case.


I have learned over the years to be very wary about on-demand compile
in Access. Thanks for the pointer!
- Greg

Keep in mind that the above applies to a already compiled mde. You often
seen forms in which a whole bunch of invisible text boxes are piled up in
the corner because the developer was not aware of using me!FieldName in
place of me.fieldname when you change a forms reocrdsource. If you change
forms reocrdsouce using code, then you have to place text boxes on the form
(me.TextBoxName), **or** use me!FieldName. The 2nd choice is better here.

I as a rule use me.TextBoxname when needing to reference a textbox. (be it
bound, or un-bound). However, when referencing underlying reocrdset, then I
always use me!FieldName. This means that looking at code I tell what choice
I was making, and furthermore if you run-time change the recordsouce of the
form, the ! use will not break your code...

 

[Greg Crowe]

Albert,

Once more, thank you! I was aware of this with regard to Access forms.
It's a tad odd that reports work differently, but as you point out,
not terribly difficult to work around.

Cheers,
- Greg Crowe