×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Keeping RecordSet Open

Keeping RecordSet Open

Keeping RecordSet Open

(OP)
In my program, I am setting a RecordSet(RS) Source. Then I open the RS, and load the frist 25 records into my form. All that works okay. I have a Next button on there, and when I click it, the RS isn't open anymore, and it dies.

I'm not closing the RS or setting it to nothing or nil. From one Public Sub to another, shouldn't it stay "open", or am I missing something?

TIA

RE: Keeping RecordSet Open

Did you Dim the recordset at the procedure level (local) or at the module level (global)?

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Keeping RecordSet Open

Hi CanonShooter,

You didn't mention how you retrieved the first 25 records, if you've done it with an SQL statement like: "SELECT TOP 25 * FROM ..." you'll just need to rerun your SQL statement again - in other words, you'll need to close the recordset, and then reopen it again.

HTH
Todd

RE: Keeping RecordSet Open

(OP)
I Dim'ed the recordset like this:

Dim RS as New ADODB.Object

I had to have the "New" in there, else it didn't work as expected. I did use a SQL statement like mentioned. When I reran the SQL statement, it didn't know what RS was, so I had to redim it, and Open the RS again.

RE: Keeping RecordSet Open

Hi CanonShooter,

The recordset isn't closed, it just doesn't contain anymore than 25 records.  You shouldn't have to redim RS though.  So somewhere above your modules in the General area of your code, change:

CODE

Dim RS as New ADODB.Object
to

CODE

Public RS as New ADODB.Object

You may also try:

CODE

Public RS as New ADODB.Recordset

HTH
Todd

RE: Keeping RecordSet Open

If you are dimming any objects As New then be very careful about closing them and setting bthem to Nothing when you're finished with them, as they are regular sources of memory leaks if left live. It's usually better to Dim xx as ADODB.Recordset, then instantiate it when required, use it and kill it all in one place.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

Steam Engine enthusiasts: www.essexsteam.co.uk

RE: Keeping RecordSet Open

(OP)
In my General Declarations, I used:

Public RS As New ADODB.Recordset

When the routine that starts filling a table runs, I get a message, and all it says is "Catastrophic Failure".

So if the Dim statement isn't in the routine, it won't work. I currently Dim it right before I do a RS.Source = "Select * FROM Table", and then a Call RS.Open......

RE: Keeping RecordSet Open

Like johnwm, I strongly advise against using the New in the Dim statement, and I would never use New with any object in general declarations.  That is asking for memory troubles.

==> In my General Declarations, I used: Public RS As New ADODB.Recordset
==> I currently Dim it right before I do a RS.Source = "Select * FROM Table", and then a Call RS.Open.

That second one cannot be in the general declartions, so if you're doing both, you have two different recordsets in play, each with a different scope, and although it's legal, it can be very confusing.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

RE: Keeping RecordSet Open

(OP)
I see what you are saying. I can take the general declaration out, and the thing loads records. If I take the Dim out of the Public Sub, it will croak with the "Catastrophic Failure" message. If I leave the Dim in the one Sub, when I run another Sub that loads the next set of records, it errors out because it doesn't know what RS is (I never closed or nil'd it).

It looks very simple, but it doesn't work. Frustrating

RE: Keeping RecordSet Open

It's all about the scope.  If you declare a recordset inside of a sub, then it's scope is limited to that sub, meaning it cannot be used in any other sub.

Sounds like you need to declare the RS in the general declarations section, but without the 'new', because you use it in several different sub.  You need to allocate it when necessary, possibly in the Form_Load event, manage it during the run, and finally close and release it, possibly in the Form_Unload event, when you're done.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources