Re-using a recordset

[LAS]

I got the following code from http://allenbrowne.com/ser-29.html. The use
of ls_sql instead of "MyTable" is my own. My question is, are all of these
steps necessary if I want to modify ls_sql and re-retrieve a number of times
in the app?

TIA
LAS

On Error Goto Err_MyProc
Dim db as Database
Dim rst As Recordset

Set db = CurrentDb()
'Set rst = db.OpenRecordset("MyTable") original code
Set rst = db.OpenRecordset(ls_sql)

'Useful code here.
rst.Close 'Close what you opened.

Exit_MyProc:
Set rst = Nothing 'Deassign all objects.
Set db = Nothing
Exit Sub

Err_MyProc:
'Error handler here.
Resume Exit_MyProc
End Sub

 

[John W. Vinson]

I got the following code from http://allenbrowne.com/ser-29.html. The use
of ls_sql instead of "MyTable" is my own. My question is, are all of these
steps necessary if I want to modify ls_sql and re-retrieve a number of times
in the app?

TIA
LAS

On Error Goto Err_MyProc
Dim db as Database
Dim rst As Recordset

Set db = CurrentDb()
'Set rst = db.OpenRecordset("MyTable") original code
Set rst = db.OpenRecordset(ls_sql)

'Useful code here.
rst.Close 'Close what you opened.

Exit_MyProc:
Set rst = Nothing 'Deassign all objects.
Set db = Nothing
Exit Sub

Err_MyProc:
'Error handler here.
Resume Exit_MyProc
End Sub

Not sure what you're trying here... you're opening a recordset using ls_sql
but (at least in this code) you have not defined ls_sql as anything. Where is
it coming from? What does it refer to?

You can open a recordset; do various things with it (including reading it
multiple times); when you're all done, close it and set it to Nothing. But you
don't need to keep reopening it and reclosing it.

Perhaps you could post some of the context and what you're trying to
accomplish.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[LAS]

OK, let's say that the first time I execute the function below ls_sql =
"Select first_name, last_name from tblNames where student_id = 21"

I execute it, do some stuff with rst and then I want to do the same sort of
things when ls_sql = "Select first_name, last_name from tblNames where
student_id = 199" I've added a line of code under "useful code here", which
isn't particularly useful, but it should do for the purposes of this
question.

I want to work with a different student. Do I have to close and set both db
and rst to nothing before I execute " Set rst = db.OpenRecordset(ls_sql)"
again?

txtFullName = rst.First_name & " " & rst.Last_Name

 

[John W. Vinson]

OK, let's say that the first time I execute the function below ls_sql =
"Select first_name, last_name from tblNames where student_id = 21"

How and where is ls_sql set? Within this routine? In another routine? Passed
as a parameter? Is it a local variable or a global variable? Where (if
anywhere) do you have a Dim ls_SQL As String declaration?

I execute it, do some stuff with rst and then I want to do the same sort of
things when ls_sql = "Select first_name, last_name from tblNames where
student_id = 199" I've added a line of code under "useful code here", which
isn't particularly useful, but it should do for the purposes of this
question.

I want to work with a different student. Do I have to close and set both db
and rst to nothing before I execute " Set rst = db.OpenRecordset(ls_sql)"
again?

No. You can just redefine it and reopen the recordset, you don't need to
explicitly close it.

But I can't imagine any context where doing so would make any sense,
especially not with literal number values. ls_sql should be a Parameter query
such as

"Select first_name, last_name from tblNames where student_id =
Forms!someform!somecontrol"

and you should requery whatever Form or Report is based on the query - or just
the query - when it changes.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[LAS]

OK, thanks. I got what I needed to know. I'm using an unbound form. Does
that help explain things?

 

[John W. Vinson]

OK, thanks. I got what I needed to know. I'm using an unbound form. Does
that help explain things?

I'm not sure, because you don't say "how" you're using the form. What are you
using it FOR? What's your actual code?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[LAS]

It's a fairly complex form and it would be cumbersome to explain it all and
I got the answer to my question. I simplified on purpose to isolate the
particular issue.

Thanks again for the response.