Solved

What is the syntax to reference Access report fields in VBA

Posted on 2009-07-06
5
629 Views
Last Modified: 2013-11-28
I am trying to call an Access report field from a Macro but can't seem to get the syntax correct in my VBA script. I have enclosed a copy of my code. This may look a little muddled as I have tried several attempts to get it to work.

Hopefully from the code it should be quite obvious what I am trying to do, update the rag report output to appropriate colours so that I can then export the file. (which conditional formatting doesn't allow.


DoCmd.openreport "rpt90", acViewPreview
 

Dim rpt90 As Report_rpt90
 

If rpt90!RAG_Label = "A" Then

Rrpt90.RAG_Label.Properties("ForeColor") = vbYellow

ElseIf Reports!rpt90.TextBox("R/A/G") = "R" Then

Reports!rpt90.TextBox("R/A/G").ForeColor = vbRed

ElseIf UCase(Reports!rpt90.TextBox("R/A/G")) = "G" Then

Reports!rpt90.TextBox("R/A/G").TextBox.ForeColor = vbGreen

End If
 

End Sub

Open in new window

0
Comment
Question by:JayceW
  • 2
  • 2
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 125 total points
Comment Utility
Here are a few comments:

<If rpt90!RAG_Label = "A" Then>

If RAG_Label is a label (not a textbox) then you need to use the Caption property (otherwise the syntax is fine as-is):

If rpt90!RAG_Label.Caption = "A" Then

<Rrpt90.RAG_Label.Properties("ForeColor") = vbYellow>
Is simply:
Rrpt90.RAG_Label.Properties.ForeColor = vbYellow

<ElseIf Reports!rpt90.TextBox("R/A/G") = "R" Then>
is "R/A/G" the name of your textbox?  If so, consider renaming it using standard naming convetions which avoid spaces and special characters, for example txtRAG.

Otherwise, use Brackets and the following syntax:

ElseIf Reports!rpt90.[R/A/G] = "R" Then

The same thing applies to all your other references to R/A/G.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 125 total points
Comment Utility
The Reddick VBA Naming conventions are described here:
http://www.xoc.net/standards/rvbanc.asp

Conventions specific to Access are midway down the page.

Most of the regulars here follow these conventions.
0
 

Author Closing Comment

by:JayceW
Comment Utility
Excellent thanks, Next step I will find out how to loop through the rows on my report to apply the formatting.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
JayceW,

I think mbizup has most of this covered, but, ...
What is this line supposed to ber doing?:
    Dim rpt90 As Report_rpt90

AFAICT (Unless you have some special Library loaded), your code will error out right away at this line, with a "User defined type, not defined" error.

Perhaps it should be:
    Dim rpt90 As Report

?

JeffCoachman
0
 

Author Comment

by:JayceW
Comment Utility
Thanks Jeff, that line, as you suggest was not needed, so I have already removed it from the code
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now