reference fields in a query in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having some trouble bringing some fields into scope.
I have a procedure in a form that changes the Record Source when the
user clicks on a button from the record source used by the form,
tblData, to a query used by the procedure behind the button,
qryFilteredData (it's supposed to spit out a text file).

When the VBA runs after the button is clicked, the Record Source
successfully changes, but I'm not able to reference the fields within
the query. I'm referencing a field by referring to [field1Name], but
it's coming up as Null, and Access says "Invalid Use of Null." This
would work if the fields were being shown on the form, but since they're
not actually used by the form itself, they don't seem to be in scope.

How can I bring the fields that are within my query into scope, so that
I can reference them? Alternatively, how can I point to them directly,
without just putting the name in square brackets?

Nathan
 
nlee144(NoSpamTakeSquareRootOfNumber) said:
I'm having some trouble bringing some fields into scope.
I have a procedure in a form that changes the Record Source when the
user clicks on a button from the record source used by the form,
tblData, to a query used by the procedure behind the button,
qryFilteredData (it's supposed to spit out a text file).

When the VBA runs after the button is clicked, the Record Source
successfully changes, but I'm not able to reference the fields within
the query. I'm referencing a field by referring to [field1Name], but
it's coming up as Null, and Access says "Invalid Use of Null." This
would work if the fields were being shown on the form, but since they're
not actually used by the form itself, they don't seem to be in scope.

How can I bring the fields that are within my query into scope, so that
I can reference them? Alternatively, how can I point to them directly,
without just putting the name in square brackets?
Click to expand...


Excuse me Nathan, but I'm having trouble making sense of
this situation. Why are you changing the form's record
source when you do not have controls bound to the fields in
the query?

It sounds like you might(?) be just using this to retrieve
some(?) values from a table. If so, then you should be
opening a recordset, not setting the form's record source.

Maybe you've gone off on the wrong track and would get a
more helpful response if you explained what you're trying to
achieve instead of how you tried to implement it.
 
Marshall said:
nlee144(NoSpamTakeSquareRootOfNumber) said:
I'm having some trouble bringing some fields into scope.
I have a procedure in a form that changes the Record Source when the
user clicks on a button from the record source used by the form,
tblData, to a query used by the procedure behind the button,
qryFilteredData (it's supposed to spit out a text file).

When the VBA runs after the button is clicked, the Record Source
successfully changes, but I'm not able to reference the fields within
the query. I'm referencing a field by referring to [field1Name], but
it's coming up as Null, and Access says "Invalid Use of Null." This
would work if the fields were being shown on the form, but since they're
not actually used by the form itself, they don't seem to be in scope.

How can I bring the fields that are within my query into scope, so that
I can reference them? Alternatively, how can I point to them directly,
without just putting the name in square brackets?
Click to expand...

Excuse me Nathan, but I'm having trouble making sense of
this situation. Why are you changing the form's record
source when you do not have controls bound to the fields in
the query?

It sounds like you might(?) be just using this to retrieve
some(?) values from a table. If so, then you should be
opening a recordset, not setting the form's record source.

Maybe you've gone off on the wrong track and would get a
more helpful response if you explained what you're trying to
achieve instead of how you tried to implement it.
Click to expand...

Yes, I'm trying to retrieve values from a table (query, actually, but it
should be the same). I want to use those values when I output to a text
file. If opening a recordset is the way to gain access to those fields,
that's perfectly fine, how would I reference them?

I'll restate with a more open ended goal: I need to click on a button,
then look up various fields in a record in a query, so that I can put
them into a text file. I'll then need to go to the next record and do it
again. How do I refer to them, and how would I cycle through the records?

Nathan
 
nlee144(NoSpamTakeSquareRootOfNumber) said:
Marshall said:
nlee144(NoSpamTakeSquareRootOfNumber) said:
I'm having some trouble bringing some fields into scope.
I have a procedure in a form that changes the Record Source when the
user clicks on a button from the record source used by the form,
tblData, to a query used by the procedure behind the button,
qryFilteredData (it's supposed to spit out a text file).

When the VBA runs after the button is clicked, the Record Source
successfully changes, but I'm not able to reference the fields within
the query. I'm referencing a field by referring to [field1Name], but
it's coming up as Null, and Access says "Invalid Use of Null." This
would work if the fields were being shown on the form, but since they're
not actually used by the form itself, they don't seem to be in scope.

How can I bring the fields that are within my query into scope, so that
I can reference them? Alternatively, how can I point to them directly,
without just putting the name in square brackets?
Click to expand...

Excuse me Nathan, but I'm having trouble making sense of
this situation. Why are you changing the form's record
source when you do not have controls bound to the fields in
the query?

It sounds like you might(?) be just using this to retrieve
some(?) values from a table. If so, then you should be
opening a recordset, not setting the form's record source.

Maybe you've gone off on the wrong track and would get a
more helpful response if you explained what you're trying to
achieve instead of how you tried to implement it.
Click to expand...

Yes, I'm trying to retrieve values from a table (query, actually, but it
should be the same). I want to use those values when I output to a text
file. If opening a recordset is the way to gain access to those fields,
that's perfectly fine, how would I reference them?

I'll restate with a more open ended goal: I need to click on a button,
then look up various fields in a record in a query, so that I can put
them into a text file. I'll then need to go to the next record and do it
again. How do I refer to them, and how would I cycle through the records?
Click to expand...


The general way to open a recordset should be demonstrated
in an example of the OpenRecordset method in VBA Help.
Unfortunately, that example is overly complex for your
needs, here's a simple outline to open a recordset with all
the records in the query:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsf As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("nameorquery", dbOpenDynaset)

There are several ways to locate a specific record or set of
records. One way is to use a filter on that recordset

rs.Filter = "fieldA = " & anumber _
& " And fieldB = """ & a string & """ _
& " And fieldC = " & Format(adate, "\#m\/d\/yyyy\#")
rsf = rs.OpenRecordset(dbOpenDynaset)

Fields in a recordset are members of the Fields collection
of the specified recordset object. Formally:

rsf.Fields!fieldname
or
rsf.Fields("fieldname")

Since Fields is the default property of the recordset
object, it can be left out:

rsf!fieldname
or
rsf("fieldname")
 
Marshall said:
The general way to open a recordset should be demonstrated
in an example of the OpenRecordset method in VBA Help.
Unfortunately, that example is overly complex for your
needs, here's a simple outline to open a recordset with all
the records in the query:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsf As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("nameorquery", dbOpenDynaset)

There are several ways to locate a specific record or set of
records. One way is to use a filter on that recordset

rs.Filter = "fieldA = " & anumber _
& " And fieldB = """ & a string & """ _
& " And fieldC = " & Format(adate, "\#m\/d\/yyyy\#")
rsf = rs.OpenRecordset(dbOpenDynaset)

Fields in a recordset are members of the Fields collection
of the specified recordset object. Formally:

rsf.Fields!fieldname
or
rsf.Fields("fieldname")

Since Fields is the default property of the recordset
object, it can be left out:

rsf!fieldname
or
rsf("fieldname")
Click to expand...

I'm trying this now, but I'm getting an error on the

Set rsQuery = dbCurrentDatabase.OpenRecordset("qryStatusReport",
dbOpenDynaset)

line. (I'm using "dbCurrentDatabase" instead of "db" and "rsQuery"
instead of "rs" as you stated in your example.)

The error is:

"Run-time error '3061':
Too few parameters. Expected 1."

Any idea why it wouldn't like my Set line?

Nathan
 
nlee144(NoSpamTakeSquareRootOfNumber) said:
I'm trying this now, but I'm getting an error on the

Set rsQuery = dbCurrentDatabase.OpenRecordset("qryStatusReport",
dbOpenDynaset)

line. (I'm using "dbCurrentDatabase" instead of "db" and "rsQuery"
instead of "rs" as you stated in your example.)

The error is:

"Run-time error '3061':
Too few parameters. Expected 1."
Click to expand...


That error message is caused by an undefined term in the
query. If the query runs fine when you run it directly from
the query design window, then it probably has either a
pop-up prompt for a value or a reference to a control on a
form. If that's what you have, then we have to open the
recordset somewhat differently, because VBA does not
automatically resolve query parameters the way Access does.

If you need more help with this issue, then I'll need to see
a copy/paste of the query's SQL view along with an
explanation of what your using for parameters in the query.
 
Marshall said:
nlee144(NoSpamTakeSquareRootOfNumber) wrote:





That error message is caused by an undefined term in the
query. If the query runs fine when you run it directly from
the query design window, then it probably has either a
pop-up prompt for a value or a reference to a control on a
form. If that's what you have, then we have to open the
recordset somewhat differently, because VBA does not
automatically resolve query parameters the way Access does.

If you need more help with this issue, then I'll need to see
a copy/paste of the query's SQL view along with an
explanation of what your using for parameters in the query.
Click to expand...

Yes, that is what happens. One field, Supplier, of the query is bound to
a control on my form. It's called [Forms]![Switchboard]![Supplier List].
Here is the paste of the SQL view (it's quite long) of my query:

SELECT tblToolDatasheet.Supplier, Max(tblToolDatasheet.Revision) AS
MaxOfRevision, tblToolDatasheet.[Program Type],
tblToolDatasheet.Program, tblToolDatasheet.[Unique Part],
tblToolDatasheet.[Part Number], tblToolDatasheet.[Part Description],
tblToolDatasheet.[TRR Plan], tblToolDatasheet.[TRR Actual],
tblToolDatasheet.[TRR Risk], tblToolDatasheet.[TRR NA],
tblToolDatasheet.[CRR Plan], tblToolDatasheet.[CRR Actual],
tblToolDatasheet.[CRR Risk], tblToolDatasheet.[CRR NA],
tblToolDatasheet.[CP 150 Plan], tblToolDatasheet.[CP 150 Actual],
tblToolDatasheet.[CP 150 Risk], tblToolDatasheet.[CP 150 NA],
tblToolDatasheet.[Tool Design Plan], tblToolDatasheet.[Tool Design
Actual], tblToolDatasheet.[Tool Design Risk], tblToolDatasheet.[Tool
Design NA], tblToolDatasheet.[TFMEA Plan], tblToolDatasheet.[TFMEA
Actual], tblToolDatasheet.[TFMEA Risk], tblToolDatasheet.[TFMEA NA],
tblToolDatasheet.[Mold Fill Plan], tblToolDatasheet.[Mold Fill Actual],
tblToolDatasheet.[Mold Fill Risk], tblToolDatasheet.[Mold Fill NA],
tblToolDatasheet.[Gage Concept Plan], tblToolDatasheet.[Gage Concept
Actual], tblToolDatasheet.[Gage Concept Risk], tblToolDatasheet.[Gage
Concept NA], tblToolDatasheet.[PFMEA Plan], tblToolDatasheet.[PFMEA
Actual], tblToolDatasheet.[PFMEA Risk], tblToolDatasheet.[PFMEA NA],
tblToolDatasheet.[Tool Construct Plan], tblToolDatasheet.[Tool Construct
Actual], tblToolDatasheet.[Tool Construct Risk], tblToolDatasheet.[Tool
Construct NA], tblToolDatasheet.[Gage Construct Plan],
tblToolDatasheet.[Gage Construct Actual], tblToolDatasheet.[Gage
Construct Risk], tblToolDatasheet.[Gage Construct NA],
tblToolDatasheet.[First Shots Plan], tblToolDatasheet.[First Shots
Actual], tblToolDatasheet.[First Shots Risk], tblToolDatasheet.[First
Shots NA], tblToolDatasheet.[Tool Adjustment Plan],
tblToolDatasheet.[Tool Adjustment Actual], tblToolDatasheet.[Tool
Adjustment Risk], tblToolDatasheet.[Tool Adjustment NA],
tblToolDatasheet.[Process Adjustment Plan], tblToolDatasheet.[Process
Adjustment Actual], tblToolDatasheet.[Process Adjustment Risk],
tblToolDatasheet.[Process Adjustment NA], tblToolDatasheet.[Process
Adjustment NA Reason], tblToolDatasheet.[GRR Plan],
tblToolDatasheet.[GRR Actual], tblToolDatasheet.[GRR Risk],
tblToolDatasheet.[GRR NA], tblToolDatasheet.[Inspect Tool Steel Plan],
tblToolDatasheet.[Inspect Tool Steel Actual], tblToolDatasheet.[Inspect
Tool Steel Risk], tblToolDatasheet.[Inspect Tool Steel NA],
tblToolDatasheet.[Scr DOE Plan], tblToolDatasheet.[Scr DOE Actual],
tblToolDatasheet.[Scr DOE Risk], tblToolDatasheet.[Scr DOE NA],
tblToolDatasheet.[Tool Metro Plan], tblToolDatasheet.[Tool Metro
Actual], tblToolDatasheet.[Tool Metro Risk], tblToolDatasheet.[Tool
Metro NA], tblToolDatasheet.[Tool Capability Plan],
tblToolDatasheet.[Tool Capability Actual], tblToolDatasheet.[Tool
Capability Risk], tblToolDatasheet.[Tool Capability NA],
tblToolDatasheet.[3 Way DOE Plan], tblToolDatasheet.[3 Way DOE Actual],
tblToolDatasheet.[3 Way DOE Risk], tblToolDatasheet.[3 Way DOE NA],
tblToolDatasheet.[Measuring DOE Components Plan],
tblToolDatasheet.[Measuring DOE Components Actual],
tblToolDatasheet.[Measuring DOE Components Risk],
tblToolDatasheet.[Measuring DOE Components NA], tblToolDatasheet.[DOE
Analysis Plan], tblToolDatasheet.[DOE Analysis Actual],
tblToolDatasheet.[DOE Analysis Risk], tblToolDatasheet.[DOE Analysis
NA], tblToolDatasheet.[First Art Cap St Plan], tblToolDatasheet.[First
Art Cap St Actual], tblToolDatasheet.[First Art Cap St Risk],
tblToolDatasheet.[First Art Cap St NA], tblToolDatasheet.[Control Plan
Plan], tblToolDatasheet.[Control Plan Actual], tblToolDatasheet.[Control
Plan Risk], tblToolDatasheet.[Control Plan NA], tblToolDatasheet.[EES
G10002 App Plan], tblToolDatasheet.[EES G10002 App Actual],
tblToolDatasheet.[EES G10002 App Risk], tblToolDatasheet.[EES G10002 App
NA], tblToolDatasheet.[CP 198 Plan], tblToolDatasheet.[CP 198 Actual],
tblToolDatasheet.[CP 198 Risk], tblToolDatasheet.[CP 198 NA]
FROM qryStatusReportMaxRevision INNER JOIN tblToolDatasheet ON
(qryStatusReportMaxRevision.MaxOfRevision = tblToolDatasheet.Revision)
AND (qryStatusReportMaxRevision.[Part Number] = tblToolDatasheet.[Part
Number]) AND (qryStatusReportMaxRevision.Supplier =
tblToolDatasheet.Supplier)
GROUP BY tblToolDatasheet.Supplier, tblToolDatasheet.[Program Type],
tblToolDatasheet.Program, tblToolDatasheet.[Unique Part],
tblToolDatasheet.[Part Number], tblToolDatasheet.[Part Description],
tblToolDatasheet.[TRR Plan], tblToolDatasheet.[TRR Actual],
tblToolDatasheet.[TRR Risk], tblToolDatasheet.[TRR NA],
tblToolDatasheet.[CRR Plan], tblToolDatasheet.[CRR Actual],
tblToolDatasheet.[CRR Risk], tblToolDatasheet.[CRR NA],
tblToolDatasheet.[CP 150 Plan], tblToolDatasheet.[CP 150 Actual],
tblToolDatasheet.[CP 150 Risk], tblToolDatasheet.[CP 150 NA],
tblToolDatasheet.[Tool Design Plan], tblToolDatasheet.[Tool Design
Actual], tblToolDatasheet.[Tool Design Risk], tblToolDatasheet.[Tool
Design NA], tblToolDatasheet.[TFMEA Plan], tblToolDatasheet.[TFMEA
Actual], tblToolDatasheet.[TFMEA Risk], tblToolDatasheet.[TFMEA NA],
tblToolDatasheet.[Mold Fill Plan], tblToolDatasheet.[Mold Fill Actual],
tblToolDatasheet.[Mold Fill Risk], tblToolDatasheet.[Mold Fill NA],
tblToolDatasheet.[Gage Concept Plan], tblToolDatasheet.[Gage Concept
Actual], tblToolDatasheet.[Gage Concept Risk], tblToolDatasheet.[Gage
Concept NA], tblToolDatasheet.[PFMEA Plan], tblToolDatasheet.[PFMEA
Actual], tblToolDatasheet.[PFMEA Risk], tblToolDatasheet.[PFMEA NA],
tblToolDatasheet.[Tool Construct Plan], tblToolDatasheet.[Tool Construct
Actual], tblToolDatasheet.[Tool Construct Risk], tblToolDatasheet.[Tool
Construct NA], tblToolDatasheet.[Gage Construct Plan],
tblToolDatasheet.[Gage Construct Actual], tblToolDatasheet.[Gage
Construct Risk], tblToolDatasheet.[Gage Construct NA],
tblToolDatasheet.[First Shots Plan], tblToolDatasheet.[First Shots
Actual], tblToolDatasheet.[First Shots Risk], tblToolDatasheet.[First
Shots NA], tblToolDatasheet.[Tool Adjustment Plan],
tblToolDatasheet.[Tool Adjustment Actual], tblToolDatasheet.[Tool
Adjustment Risk], tblToolDatasheet.[Tool Adjustment NA],
tblToolDatasheet.[Process Adjustment Plan], tblToolDatasheet.[Process
Adjustment Actual], tblToolDatasheet.[Process Adjustment Risk],
tblToolDatasheet.[Process Adjustment NA], tblToolDatasheet.[Process
Adjustment NA Reason], tblToolDatasheet.[GRR Plan],
tblToolDatasheet.[GRR Actual], tblToolDatasheet.[GRR Risk],
tblToolDatasheet.[GRR NA], tblToolDatasheet.[Inspect Tool Steel Plan],
tblToolDatasheet.[Inspect Tool Steel Actual], tblToolDatasheet.[Inspect
Tool Steel Risk], tblToolDatasheet.[Inspect Tool Steel NA],
tblToolDatasheet.[Scr DOE Plan], tblToolDatasheet.[Scr DOE Actual],
tblToolDatasheet.[Scr DOE Risk], tblToolDatasheet.[Scr DOE NA],
tblToolDatasheet.[Tool Metro Plan], tblToolDatasheet.[Tool Metro
Actual], tblToolDatasheet.[Tool Metro Risk], tblToolDatasheet.[Tool
Metro NA], tblToolDatasheet.[Tool Capability Plan],
tblToolDatasheet.[Tool Capability Actual], tblToolDatasheet.[Tool
Capability Risk], tblToolDatasheet.[Tool Capability NA],
tblToolDatasheet.[3 Way DOE Plan], tblToolDatasheet.[3 Way DOE Actual],
tblToolDatasheet.[3 Way DOE Risk], tblToolDatasheet.[3 Way DOE NA],
tblToolDatasheet.[Measuring DOE Components Plan],
tblToolDatasheet.[Measuring DOE Components Actual],
tblToolDatasheet.[Measuring DOE Components Risk],
tblToolDatasheet.[Measuring DOE Components NA], tblToolDatasheet.[DOE
Analysis Plan], tblToolDatasheet.[DOE Analysis Actual],
tblToolDatasheet.[DOE Analysis Risk], tblToolDatasheet.[DOE Analysis
NA], tblToolDatasheet.[First Art Cap St Plan], tblToolDatasheet.[First
Art Cap St Actual], tblToolDatasheet.[First Art Cap St Risk],
tblToolDatasheet.[First Art Cap St NA], tblToolDatasheet.[Control Plan
Plan], tblToolDatasheet.[Control Plan Actual], tblToolDatasheet.[Control
Plan Risk], tblToolDatasheet.[Control Plan NA], tblToolDatasheet.[EES
G10002 App Plan], tblToolDatasheet.[EES G10002 App Actual],
tblToolDatasheet.[EES G10002 App Risk], tblToolDatasheet.[EES G10002 App
NA], tblToolDatasheet.[CP 198 Plan], tblToolDatasheet.[CP 198 Actual],
tblToolDatasheet.[CP 198 Risk], tblToolDatasheet.[CP 198 NA]
HAVING (((tblToolDatasheet.Supplier)=[Forms]![Switchboard]![Supplier
List]) AND ((tblToolDatasheet.[Unique Part])<>"New Part"))
ORDER BY tblToolDatasheet.Supplier, Max(tblToolDatasheet.Revision),
tblToolDatasheet.[Program Type], tblToolDatasheet.Program,
tblToolDatasheet.[Unique Part];
 
nlee144(NoSpamTakeSquareRootOfNumber) said:
Marshall said:
nlee144(NoSpamTakeSquareRootOfNumber) wrote:





That error message is caused by an undefined term in the
query. If the query runs fine when you run it directly from
the query design window, then it probably has either a
pop-up prompt for a value or a reference to a control on a
form. If that's what you have, then we have to open the
recordset somewhat differently, because VBA does not
automatically resolve query parameters the way Access does.

If you need more help with this issue, then I'll need to see
a copy/paste of the query's SQL view along with an
explanation of what your using for parameters in the query.
Click to expand...

Yes, that is what happens. One field, Supplier, of the query is bound to
a control on my form. It's called [Forms]![Switchboard]![Supplier List].
Here is the paste of the SQL view (it's quite long) of my query:
Click to expand...
[snip the really long query]


I will refrain from questioning the need for the
horrendously long Group By clause ;-)

Ok, you need to resolve the parameter before opening the
recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsf As DAO.Recordset
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("nameorquery")
qdf.Parameters(0) = Forms!Switchboard![Supplier List]
Set rs = qdf.OpenRecordset(dbOpenDynaset)
 
Marshall said:
I will refrain from questioning the need for the
horrendously long Group By clause ;-)

Ok, you need to resolve the parameter before opening the
recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsf As DAO.Recordset
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("nameorquery")
qdf.Parameters(0) = Forms!Switchboard![Supplier List]
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Click to expand...

I inherited the project... that's why it's messy. :)

Okay, I've entered what you put in here, and that seems to be working
much better. I'm still not able to point to the fields for some reason.
Access says the values of the fields to which I'm trying to point are
null. I haven't worked with recordsets before, which is why I'm not able
to do this.

To go to the first record, I have

rs.MoveFirst
While Not rs.EOF
' code in here
' use the field:
procedureCall rsQuery![TRR Actual]

rs.MoveNext
Wend

but I get the null error when I point to the field. It certainly
shouldn't be null. Am I not moving through correctly, or am I just
naming the fields wrong?

Nathan
 
nlee144(NoSpamTakeSquareRootOfNumber) said:
Okay, I've entered what you put in here, and that seems to be working
much better. I'm still not able to point to the fields for some reason.
Access says the values of the fields to which I'm trying to point are
null. I haven't worked with recordsets before, which is why I'm not able
to do this.

To go to the first record, I have

rs.MoveFirst
While Not rs.EOF
' code in here
' use the field:
procedureCall rsQuery![TRR Actual]

rs.MoveNext
Wend

but I get the null error when I point to the field. It certainly
shouldn't be null. Am I not moving through correctly, or am I just
naming the fields wrong?
Click to expand...


Well, that code is using my example's rs object in some
places and your rsQuery in another place. Other than that,
it looks perfectly legal to me.

I checked you query again and [TRR Actual] is a field in the
query so the rsQuery![TRR Actual] reference is the right way
to do it (assuming you get the recordset names straightened
out).

You may have reached the point where it would be most
productive to place some breakpoints in the code and verify
that everything is as it shoud be (e.g
rsQuery.RecordCount).

While it's not pertinate to your problem, note that While -
Wend is an obsolete construct, the recommended equivalent
statments are:
Do While Not rs.EOF
. . .
Loop
or
Do Until rs.EOF
. . .
Loop
 
Marshall said:
nlee144(NoSpamTakeSquareRootOfNumber) said:
Okay, I've entered what you put in here, and that seems to be working
much better. I'm still not able to point to the fields for some reason.
Access says the values of the fields to which I'm trying to point are
null. I haven't worked with recordsets before, which is why I'm not able
to do this.

To go to the first record, I have

rs.MoveFirst
While Not rs.EOF
' code in here
' use the field:
procedureCall rsQuery![TRR Actual]

rs.MoveNext
Wend

but I get the null error when I point to the field. It certainly
shouldn't be null. Am I not moving through correctly, or am I just
naming the fields wrong?
Click to expand...



Well, that code is using my example's rs object in some
places and your rsQuery in another place. Other than that,
it looks perfectly legal to me.

I checked you query again and [TRR Actual] is a field in the
query so the rsQuery![TRR Actual] reference is the right way
to do it (assuming you get the recordset names straightened
out).

You may have reached the point where it would be most
productive to place some breakpoints in the code and verify
that everything is as it shoud be (e.g
rsQuery.RecordCount).

While it's not pertinate to your problem, note that While -
Wend is an obsolete construct, the recommended equivalent
statments are:
Do While Not rs.EOF
. . .
Loop
or
Do Until rs.EOF
. . .
Loop
Click to expand...

yes, I think I see what I need to do now. Thanks so much for your help here.

Nathan
 
Back
Top