newbie q's: referring to fields

[Nathan C. Lee]

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.

 

[John Vinson]

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.

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.

 

[Nathan C. Lee]

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.

I presume that these are the Name properties of Textbox controls on
the form? If so...

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?

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.

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?

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.

 

[Nathan C. Lee]

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

 

[John Vinson]

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.

 

[Nathan C. Lee]

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

 

[Nathan C. Lee]

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

 

[Nathan C. Lee]

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

 

[John Vinson]

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!

 

[Nathan C. Lee]

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]")

 

[Nathan C. Lee]

I feel like I should be able to say:

Set valuePlan = [Queries]![StatusReportQuery]![TRR Plan]

but that just doesn't fly. Access tells me it can't find "Queries"

 

[John Vinson]

I feel like I should be able to say:

Set valuePlan = [Queries]![StatusReportQuery]![TRR Plan]

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.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb ' get a reference to the open database
' Open a Recordset object based on the selected query
Set rs = db.OpenRecordset("[StatusReportQuery]", dbOpenDynaset)

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]")

Either use the code above and say
valuePlan = rs![TRR Plan]

or look up in the Query rather than in the Table:
valuePlan = DLookUp("[TRR Plan]", "[StatusReportQuery]")

In either case this will return the first record in StatusReportQuery
unless you do something specific to navigate to or find a selected
record. If the Query returns multiple records, you'll need to do
something to get to the correct record - not knowing what's on your
report or in the query I can't say offhand how to do that.

 

[Nathan C. Lee]

I was able to find a way by referencing the fields directly after they had
been used in my form. I'm able to write

valuePlan = [TRR Plan]

Since TRR Plan is already used by my form, and my query is the form's record
source. Oddly, even though the report uses my query as its record source, in
my VBA I can only refer to fields which are actually used in my form. Since
I had several fields I don't need in my form, but do need in my VBA, I just
made an invisible text box that references all of them to bring them into
scope. Is there a way to programatically bring fields into scope? It works
now, but I'd like more portable code.

Thanks for all of your help. You're slowly bringing a newbie out of the
cave.

Nathan

I feel like I should be able to say:

Set valuePlan = [Queries]![StatusReportQuery]![TRR Plan]

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.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb ' get a reference to the open database
' Open a Recordset object based on the selected query
Set rs = db.OpenRecordset("[StatusReportQuery]", dbOpenDynaset)

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]")

Either use the code above and say
valuePlan = rs![TRR Plan]

or look up in the Query rather than in the Table:
valuePlan = DLookUp("[TRR Plan]", "[StatusReportQuery]")

In either case this will return the first record in StatusReportQuery
unless you do something specific to navigate to or find a selected
record. If the Query returns multiple records, you'll need to do
something to get to the correct record - not knowing what's on your
report or in the query I can't say offhand how to do that.