We're using Access '97 for this. I need to change the color of several text
fields in a report.

1) My first question has to do with referencing the fields themselves. I
have 16 different fields, but for simplicity, I'm using a variable
"fieldBeingChanged" to reference them, and using a Select Case operation to
select which field is being referenced. E.g.

....

Dim fieldBeingChanged As String

....

Case TRR

fieldBeingChanged = "Report!StatusReport!TRRField"

Is this the correct way to refer to the field? I'm a little unsure about
when to use quotes and when not.

2) In the same Select Case, I need to reference two dates, valuePlan and
valueActual. The values in the database's table sometimes contain spaces,
for instance, the value is held as "TRR Plan". The code looks like this:

Dim valuePlan As Date

Dim valueActual As Date

....

Select Case currentPlace

....

Case TRR

....

valuePlan = "Table!tblToolDatasheet!TRR Plan"

valueActual = "Table!tblToolDatasheet!TRR Actual"

I think I need quotes here, because the compiler yells at me because of the
white space. Is this the correct way? It tells me that "Table" is
undefined... so how am I supposed to get something out of a table?

3) Finally, how should I actually change the color? I've tried

fieldBeingChanged.BackColor = vbGreen

but that's not working at all. The compiler highlights "fieldBeingChanged"
and says "Invalid Qualifier". I'm guessing it thinks I'm trying to change
the BackColor on the string, but I really want to do it on the field the
string is referencing.

Thank you in advance for your time.

On Fri, 16 Apr 2004 15:11:06 -0400, "Nathan C. Lee"
<(E-Mail Removed)> wrote:

>We're using Access '97 for this. I need to change the color of several text
>fields in a report.

well... nitpick here, but one that may help. A Report has *controls*,
not fields; each control may (or may not) be bound to a table field.

>1) My first question has to do with referencing the fields themselves. I
>have 16 different fields, but for simplicity, I'm using a variable
>"fieldBeingChanged" to reference them, and using a Select Case operation to
>select which field is being referenced. E.g.
>
>...
>
>Dim fieldBeingChanged As String

I presume that these are the Name properties of Textbox controls on
the form? If so...
>...
>
>Case TRR
>
>fieldBeingChanged = "Report!StatusReport!TRRField"

Dim fieldBeingChanged As Control
Set fieldBeingChanged = Report!Statusreport.Controls("TRRField")
fieldBeingChange.BackColor = vbRed

<or whatever you want to do with the Control object)

>Is this the correct way to refer to the field? I'm a little unsure about
>when to use quotes and when not.

No. It's not. <g>

>2) In the same Select Case, I need to reference two dates, valuePlan and
>valueActual. The values in the database's table sometimes contain spaces,
>for instance, the value is held as "TRR Plan". The code looks like this:
>
>Dim valuePlan As Date
>
>Dim valueActual As Date
>
>...
>
>Select Case currentPlace

What is CurrentPlace? a String?
>...
>
>Case TRR

What is TRR? A String Variable? A string value which CurrentPlace
might take (in which case you need Case "TRR")?
>...
>
>valuePlan = "Table!tblToolDatasheet!TRR Plan"
>
>valueActual = "Table!tblToolDatasheet!TRR Actual"
>
>I think I need quotes here, because the compiler yells at me because of the
>white space. Is this the correct way? It tells me that "Table" is
>undefined... so how am I supposed to get something out of a table?

By opening a Recordset, or using the DLookUp function:

valuePlan = DLookUp("[TRR Plan]", "[tblToolDatasheet]", "<some
criteria you don't specify to select which record in tblToolDatasheet
you want to select>")

>3) Finally, how should I actually change the color? I've tried
>
>fieldBeingChanged.BackColor = vbGreen
>
>but that's not working at all. The compiler highlights "fieldBeingChanged"
>and says "Invalid Qualifier". I'm guessing it thinks I'm trying to change
>the BackColor on the string, but I really want to do it on the field the
>string is referencing.

Dim it as a Control as above.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:<(E-Mail Removed)>...
> On Fri, 16 Apr 2004 15:11:06 -0400, "Nathan C. Lee"
> <(E-Mail Removed)> wrote:
>
> >We're using Access '97 for this. I need to change the color of several
text
> >fields in a report.
>
> well... nitpick here, but one that may help. A Report has *controls*,
> not fields; each control may (or may not) be bound to a table field.

Ahh, thank you. I was in fact referring to TextBoxes and Labels. The
TextBoxes are getting their values from a table or a query.

> >1) My first question has to do with referencing the fields themselves. I
> >have 16 different fields, but for simplicity, I'm using a variable
> >"fieldBeingChanged" to reference them, and using a Select Case operation
to
> >select which field is being referenced. E.g.
> >
> >...
> >
> >Dim fieldBeingChanged As String
>
> I presume that these are the Name properties of Textbox controls on
> the form? If so...
> >...
> >
> >Case TRR
> >
> >fieldBeingChanged = "Report!StatusReport!TRRField"
>
> Dim fieldBeingChanged As Control
> Set fieldBeingChanged = Report!Statusreport.Controls("TRRField")
> fieldBeingChange.BackColor = vbRed
>
> <or whatever you want to do with the Control object)

Yes, fieldBeingChanged is meant to refer to the name of the TextBox
controls.
I've made the changes, but now I'm getting an error (Run-Time error 2465)
telling me
"Microsoft Access can't find the field 'StatusReport' referred to in your
expression."
I'm sure that I've spelled it right, and I do indeed have a report called
"StatusReport". Any idea what's going on here?

> >Is this the correct way to refer to the field? I'm a little unsure about
> >when to use quotes and when not.
>
> No. It's not. <g>
>
> >2) In the same Select Case, I need to reference two dates, valuePlan and
> >valueActual. The values in the database's table sometimes contain spaces,
> >for instance, the value is held as "TRR Plan". The code looks like this:
> >
> >Dim valuePlan As Date
> >
> >Dim valueActual As Date
> >
> >...
> >
> >Select Case currentPlace
>
> What is CurrentPlace? a String?

It's just an integer to keep track of steps of iteration. Based on the value
of CurrentPlace, the program chooses which of my TextBox or Label controls
to alter.

> >...
> >
> >Case TRR
>
> What is TRR? A String Variable? A string value which CurrentPlace
> might take (in which case you need Case "TRR")?

TRR is an enumerated integer value, working with CurrentPlace. If
CurrentPlace == TRR Then the program needs to alter TRRField. I don't think
I need quotes for that, do I?

> >...
> >
> >valuePlan = "Table!tblToolDatasheet!TRR Plan"
> >
> >valueActual = "Table!tblToolDatasheet!TRR Actual"
> >
> >I think I need quotes here, because the compiler yells at me because of
the
> >white space. Is this the correct way? It tells me that "Table" is
> >undefined... so how am I supposed to get something out of a table?
>
> By opening a Recordset, or using the DLookUp function:
>
> valuePlan = DLookUp("[TRR Plan]", "[tblToolDatasheet]", "<some
> criteria you don't specify to select which record in tblToolDatasheet
> you want to select>")

That makes sense. I shouldn't need to put in criteria if I don't need any
further filtering logic there, should I?

> >3) Finally, how should I actually change the color? I've tried
> >
> >fieldBeingChanged.BackColor = vbGreen
> >
> >but that's not working at all. The compiler highlights
"fieldBeingChanged"
> >and says "Invalid Qualifier". I'm guessing it thinks I'm trying to change
> >the BackColor on the string, but I really want to do it on the field the
> >string is referencing.
>
> Dim it as a Control as above.

Yes, I don't think it will give me trouble now that I am referring to the
controls correctly.
Thank you so much for your help.

> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
> http://go.compuserve.com/msdevapps?loc=us&access=public

I have actually resolved the problem of referring to the TextBox and labels by using

Set fieldBeingChanged = Reports!StatusReport.Controls("TRRField")

I was using "Report!" instead of "Reports!", but now it works properly.

My only remaining question, I believe, has to do with the DLookup function.

I am using the DLookup function to assign values to my date variables, valuePlan and valueActual (in the program, these two variables will need to be assigned to dates taken from the table and then be used in logic operations several times). I'm not sure how to form the DLookup call. It's going to look something like:

valuePlan = DLookup("[TRR Plan]", "[tblToolDataSheet]", <some criteria here>)

but I don't know how to form the criteria to make sure that it gets the proper item. I believe that I need to have it say something like "<Part Number of current record> == tblToolDataSheet![Part Number]" This code is in the "Detail" section of the report, so it should execute once for each record that is being evaluated. How can I refer to the "Part Number" field of the current record, and distinguish it from the "Part Number" field in the table in which I'm looking? (TRR Plan is a date field in the table, tblToolDataSheet is the table).
When I try to run DLookup without any criteria

valuePlan = DLookup("[TRR Plan]", "[tblToolDataSheet]")

I get "Run-time error '94': Invalid use of Null. Even if there is no TRR Plan in my table, shouldn't it just assign a null value into valuePlan (or does a date object require a non-null value?)
Thanks

On Mon, 19 Apr 2004 13:34:47 -0400, "Nathan C. Lee"
<(E-Mail Removed)> wrote:

>I am using the DLookup function to assign values to my date variables, valuePlan and valueActual (in the program, these two variables will need to be assigned to dates taken from the table and then be used in logic operations several times). I'm not sure how to form the DLookup call. It's going to look something like:
>
>valuePlan = DLookup("[TRR Plan]", "[tblToolDataSheet]", <some criteria here>)
>
>but I don't know how to form the criteria to make sure that it gets the proper item. I believe that I need to have it say something like "<Part Number of current record> == tblToolDataSheet![Part Number]" This code is in the "Detail" section of the report, so it should execute once for each record that is being evaluated. How can I refer to the "Part Number" field of the current record, and distinguish it from the "Part Number" field in the table in which I'm looking? (TRR Plan is a date field in the table, tblToolDataSheet is the table).
>When I try to run DLookup without any criteria
>
>valuePlan = DLookup("[TRR Plan]", "[tblToolDataSheet]")

Why use DLookUp at ALL? It's much less efficient than including
tblToolDataSheet in the Query upon which your report is based; just
join it to your table by PartNumber.

>I get "Run-time error '94': Invalid use of Null. Even if there is no TRR Plan in my table, shouldn't it just assign a null value into valuePlan (or does a date object require a non-null value?)

A Date object does not allow NULLs; you'ld need to Dim valuePlan as a
Variant for NULL to work.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

> Why use DLookUp at ALL? It's much less efficient than including
> tblToolDataSheet in the Query upon which your report is based; just
> join it to your table by PartNumber.

I don't see what you mean by including a table in my query. I already have

> Why use DLookUp at ALL? It's much less efficient than including
> tblToolDataSheet in the Query upon which your report is based; just
> join it to your table by PartNumber.

I don't see what you mean by including a table in my query. I already have
fields from the table in my query, if that's what you mean. The records are
enumerated by part number, so there should be distinct records from the
table in the query. So I should put the relevant fields I want into my
query, then reference those instead? How would I do that?

Dim valuePlan As Variant
....
valuePlan = [TRR Plan]

doesn't seem to work.

Nathan

Sorry... problem with the last post attempt.

> Why use DLookUp at ALL? It's much less efficient than including
> tblToolDataSheet in the Query upon which your report is based; just
> join it to your table by PartNumber.

I don't see what you mean by including a table in my query. I already have
fields from the table in my query, if that's what you mean. The records are
enumerated by part number, so there should be distinct records from the
table in the query. So I should put the relevant fields I want into my
query, then reference those instead? How would I do that?

Dim valuePlan As Variant
....
valuePlan = [TRR Plan]

doesn't seem to work.

Nathan

On Tue, 20 Apr 2004 10:01:29 -0400, "Nathan C. Lee"
<(E-Mail Removed)> wrote:

>I don't see what you mean by including a table in my query. I already have
>fields from the table in my query, if that's what you mean. The records are
>enumerated by part number, so there should be distinct records from the
>table in the query. So I should put the relevant fields I want into my
>query, then reference those instead? How would I do that?
>
>Dim valuePlan As Variant
>...
>valuePlan = [TRR Plan]
>
>doesn't seem to work.
>
>Nathan

It appears that you're mixing SQL queries and VBA code. They are
different languages and do not have access to each other's values
directly.

If the [TRR Plan] field is in your Query, you can either open the
query as a Recordset and refer to

rs![TRR Plan]

or use DLookUp in your code. My point was that it is (usually) not
necessary or beneficial to use DLookUp as a calculated field in a
Query, if you already have the field in the Query.

Perhaps you could post a copy of the relevant section of your code -
I'm not sure just what you are attempting to accomplish, so my advice
may be wide of the mark!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

> >Dim valuePlan As Variant
> >...
> >valuePlan = [TRR Plan]
> >
> >doesn't seem to work.
> >
> >Nathan
>
> It appears that you're mixing SQL queries and VBA code. They are
> different languages and do not have access to each other's values
> directly.

Indeed I may be. I thought there should be some way to directly refer to a field in my query, as I can a control in my report, or as I can in the Expression Builder (not VBA I know).

> If the [TRR Plan] field is in your Query, you can either open the
> query as a Recordset and refer to
>
> rs![TRR Plan]
>
> or use DLookUp in your code. My point was that it is (usually) not
> necessary or beneficial to use DLookUp as a calculated field in a
> Query, if you already have the field in the Query.

How do I open my query as a Recordset? The data I need certainly already is in my query, so I'd like to go get it in the most direct way possible.

> Perhaps you could post a copy of the relevant section of your code -
> I'm not sure just what you are attempting to accomplish, so my advice
> may be wide of the mark!

Here's some of it:

While currentPlace < 17

Select Case currentPlace

Case TRR
Set fieldBeingChanged = Reports!StatusReport.Controls("TRRField")
Set numberFieldBeingChanged = Reports!StatusReport.Controls("TRRNumberField")
' I just want to get "TRR Plan" out of my query here.
valuePlan = DLookup("[TRR Plan]", "[tblToolDataSheet]")
valueActual = DLookup("[TRR Actual]", "[tblToolDataSheet]")

Case CRR
Set fieldBeingChanged = Reports!StatusReport.Controls("CRRField")
Set numberFieldBeingChanged = Reports!StatusReport.Controls("CRRNumberField")
valuePlan = DLookup("[CRR Plan]", "[tblToolDataSheet]")
valueActual = DLookup("[CRR Actual]", "[tblToolDataSheet]")