Report "do loop"

  • Thread starter Thread starter Rass
  • Start date Start date
R

Rass

I can supply all coding if necessary:

I want to call a "do until rst.eof" that is in another
private sub named "CallUS"

The loop works fine if I keep it in the same sub routine
as the rst.open etc, and just don't do the call. I must
do the call, since I change all of the search parameters
for every record and I don't want to duplicate the do loop
for every one of 150 searches. I just want to change the
parameters and let the loop do the work in another sub.

When I put the "do until" routine (direct copy) and place
a call for it in the main sub, it will call the proper
routine. Using F8 I step through each line of coding and
when I get to the "do until rst.eof" I get an error that
an object is required.

Why can't I run the do loop in a seperate sub and still
access my record set?

Thanks for helping!
 
Rass said:
I can supply all coding if necessary:

I want to call a "do until rst.eof" that is in another
private sub named "CallUS"

The loop works fine if I keep it in the same sub routine
as the rst.open etc, and just don't do the call. I must
do the call, since I change all of the search parameters
for every record and I don't want to duplicate the do loop
for every one of 150 searches. I just want to change the
parameters and let the loop do the work in another sub.

When I put the "do until" routine (direct copy) and place
a call for it in the main sub, it will call the proper
routine. Using F8 I step through each line of coding and
when I get to the "do until rst.eof" I get an error that
an object is required.

Why can't I run the do loop in a seperate sub and still
access my record set?
Click to expand...

This is just a guess since you didn't let us see an outline
of your acutal code, but it sounds like the called procedure
doesn't know what rst is. You could declare rst in the
module's declarations section, but it is a better practice
to pass it in an argument to the procedure.
 
Actual Code:

Dim rst as new adodb.recordset
dim cnn as new adodb.connection
set cnn = CurrentProject.connection
rst.open "qryYU, ccn, adopenstatic, adlockoptimistic
rst.find "[Department] like 'Purchasing' "
' position is a VBA declared string variable
Position = "Manager"

'here is where I want to call the "do loop"
Call CallUS

' If I just stay in this procedure the loop works like this

Do Until rst.eof
If rst!Pos = Position then
BP = rst!PreferredName & " " & rst!Surname
Exit Do

Else
rst.movenext
If rst.eof then
rst.movefirst
Exit do
End if
End if
Loop
End sub
' The above does just what I want until I go to the other
sub

Private Sub CallUS ()
Do Until rst.EOF

' Etc.

The minute I execute the first statement in the private
sub CallUS I get the error:

Run-time Error "424"
Object Required.

Thanks for helping again.
 
As I suspected, the CallUS procedure doesn't know what rst
is. Variables local (declared inside a procedure) to the
calling procedure are not available outside that procedure.

Seeing the way you're using the Position variable, you
should also pass that as an argument:

. . .
rst.find "[Department] like 'Purchasing' "
Position = "Manager"
Call CallUS(rst, Position)

Private Sub CallUS (rst As Recordset, Position As String)
Do Until rst.EOF
If rst!Pos = Position then
. . .
--
Marsh
MVP [MS Access]


Actual Code:

Dim rst as new adodb.recordset
dim cnn as new adodb.connection
set cnn = CurrentProject.connection
rst.open "qryYU, ccn, adopenstatic, adlockoptimistic
rst.find "[Department] like 'Purchasing' "
' position is a VBA declared string variable
Position = "Manager"

'here is where I want to call the "do loop"
Call CallUS

' If I just stay in this procedure the loop works like this

Do Until rst.eof
If rst!Pos = Position then
BP = rst!PreferredName & " " & rst!Surname
Exit Do

Else
rst.movenext
If rst.eof then
rst.movefirst
Exit do
End if
End if
Loop
End sub
' The above does just what I want until I go to the other
sub

Private Sub CallUS ()
Do Until rst.EOF

' Etc.

The minute I execute the first statement in the private
sub CallUS I get the error:

Run-time Error "424"
Object Required.

Thanks for helping again.

-----Original Message-----


This is just a guess since you didn't let us see an outline
of your acutal code, but it sounds like the called procedure
doesn't know what rst is. You could declare rst in the
module's declarations section, but it is a better practice
to pass it in an argument to the procedure.
Click to expand...
Click to expand...
 
Back
Top