Use a Parent function's OpenRecordset() current record in Sub

[Nicholas Scarpinato]

I have a function that checks a record's Status field and, based on the
status, calls a status-specific Sub. My problem is I don't know how to make
the Sub see the current record in the recordset opened by the Function. I
could write all the code inline in the function, but it would get rather
cumbersome. I won't list the entire code due to it's length, but the gist of
it is this:

Function RejectedItemOptionSelect()
.... DIM's, etc ...
Set db = CurrentDB()
sql = "SELECT * FROM [Main Returns Table]"
Set rs = db.OpenRecordSet(sql)
With rs
..MoveFirst
Do Until .EOF
If .Fields("Status") = "Resubmit to correct vendor" Then Call BinLocationReset
If .Fields("Status") = .... etc ...


When I call BinLocationReset, I want to work with the current open record in
recordset "rs". But attempting to use rs.Edit in my Sub gives me an Object
Required error, and if I use .Edit, I get an Invalid or Unqualified Reference
error.

Do I need to Global "rs"?

 

[mray29]

You need to pass a parameter to CallBinLocationReset so it will know which
record to work with. Is there an ID field in the MainReturnsTable that you
can pass to the routine?

M

 

[Marshall Barton]

I have a function that checks a record's Status field and, based on the
status, calls a status-specific Sub. My problem is I don't know how to make
the Sub see the current record in the recordset opened by the Function. I
could write all the code inline in the function, but it would get rather
cumbersome. I won't list the entire code due to it's length, but the gist of
it is this:

Function RejectedItemOptionSelect()
... DIM's, etc ...
Set db = CurrentDB()
sql = "SELECT * FROM [Main Returns Table]"
Set rs = db.OpenRecordSet(sql)
With rs
.MoveFirst
Do Until .EOF
If .Fields("Status") = "Resubmit to correct vendor" Then Call BinLocationReset
If .Fields("Status") = .... etc ...


When I call BinLocationReset, I want to work with the current open record in
recordset "rs". But attempting to use rs.Edit in my Sub gives me an Object
Required error, and if I use .Edit, I get an Invalid or Unqualified Reference
error.

Do I need to Global "rs"?

I suggest that you modify the BinLocationReset procedure so
it accepts a recordset object as an argument. This way the
procedure can use rs.Edit

Public Sub BinLocationReset(rs As Dao.Recordset)
...
rs.Edit
...
rs.Update
...
End Sub

Then call the procedure using:
Call BinLocationReset(rs)