using once opened Record Set differently

[questionit]

I am opening record set at different places with different queries i.e:

 
Set rst = db.OpenRecordset("Select Names, Tasks, Datetime FROM tblUsers WHERE Users='" & strUName & "'" & "AND Tasks='" & strTask & "'")
 
Set rst = db.OpenRecordset("Select Tasks FROM tblUsers WHERE Users='" & strUName & "'" )
 

I was wondering if it is possible or preferrable to open the record set only once and when i need to use the opened record set, i just query it as required - so i dont have to open record set all the times - avoiding doing same thing more than once ?

[Rabbit]

I am opening record set at different places with different queries i.e:

 
Set rst = db.OpenRecordset("Select Names, Tasks, Datetime FROM tblUsers WHERE Users='" & strUName & "'" & "AND Tasks='" & strTask & "'")
 
Set rst = db.OpenRecordset("Select Tasks FROM tblUsers WHERE Users='" & strUName & "'" )
 

I was wondering if it is possible or preferrable to open the record set only once and when i need to use the opened record set, i just query it as required - so i dont have to open record set all the times - avoiding doing same thing more than once ?

What do you mean? Are you talking about in the same event but later on?

Using one record set and reopening what you need or using more than one record set; Both have their trade offs. Using one record set incurs computational time. Using more than one record set uses more RAM.

If you're asking my opinion, my computers have more than enough RAM to get along so I usually choose to use more than one record set.

[questionit]

Thats fine, but just for efficieny puposes, i would like to open the Record once and query it at different times - In the different Events.

So if i was to do like i have said, after opening the Record set , how would i do a complete query later as required ?

Note: I would open the Record Set in the Constructor : Class_Initialize()

Thanks

What do you mean? Are you talking about in the same event but later on?

Using one record set and reopening what you need or using more than one record set; Both have their trade offs. Using one record set incurs computational time. Using more than one record set uses more RAM.

If you're asking my opinion, my computers have more than enough RAM to get along so I usually choose to use more than one record set.

[Rabbit]

Then use a global variable.

[questionit]

Rabbit,

after declaring it global and opening the record set once.

whats the method to query the recordset if required in any event?

Then use a global variable.

[Rabbit]

Rabbit,

after declaring it global and opening the record set once.

whats the method to query the recordset if required in any event?

RecordsetVariable!FieldName

[questionit]

Rabbit,

ths way you get a value from a record set variable, right?

i meant to ask how you re-query a recordset once it is opened. When its is opened we execute a SQL query at that time but what if you need to change the SQL query and run onto the already opened RecordSet ?

RecordsetVariable!FieldName

[questionit]

Probably the concept of opening recordSet is different than what i had thought.

i think i dont need anymore posts on this.

but if someone could tell me if we can somehow pass a parameter to Class_Initialize() ? I have tried but not allowed - its a very useful feature in C/C++ , is there alternative of passing parameter to Class_Initialize() ?

Thanks

Rabbit,

ths way you get a value from a record set variable, right?

i meant to ask how you re-query a recordset once it is opened. When its is opened we execute a SQL query at that time but what if you need to change the SQL query and run onto the already opened RecordSet ?

[Rabbit]

Probably the concept of opening recordSet is different than what i had thought.

i think i dont need anymore posts on this.

but if someone could tell me if we can somehow pass a parameter to Class_Initialize() ? I have tried but not allowed - its a very useful feature in C/C++ , is there alternative of passing parameter to Class_Initialize() ?

Thanks

I have no idea what you mean by Class_Initialize. What are you trying to do? If we know what you're trying to do then we can help.

[JKing]

Class_Initialize and Class_Terminate act as constructors and destructors for classes. Unfortunately they do not support parameters. I don't believe VBA was intended to be used for full out object oriented based programming and there for doesn't support overloading constructors.

Here's a link to a previous thread that discusses this matter
http://www.thescripts.com/forum/thread206362.html