Collection

This is what i'm doing - I make a collelection in module (MyAdoCol as new collection)

then when forms are open they do a connect to a database use SQL and Ado I then say:

AlterRecords.MyAdoCol.Add RsHealth '(alter records is the module)

'at this point there is only 1 item in the collection

just for a test i say:
For Each rs In AlterRecords.MyAdoCol
Command2.Caption = CStr(rs.Name) ' blows up here
Next

what i want to be able to do is. call a sub such as:

private sub DeleteData (myRs as adodb.recordset)
myrs.delete
myrs.update
end sub


-Does anyone know how to do this?

-Thanks
Doug

Are you closing the recordsets after you add them to the collection?
I have never seen recordset objects added to a collection before and in
general this is a very bad idea (you should almost never have more than
one recordset open at a time). You also have to be careful about the
connections being closed on those recordsets (unless disconnected
recordsets).

Thanks for responding,

no i am not closing the recordset or diconnecting them. There proabbly won't be more than 1 RS open at once. The only reason why i'm trying to add them to a collection is to reference them later on (though a module) I tried saying:

for each RS in me
if rs istypeof adodb.recordset
next

but that didn't work - not an object. hence the collection meathod

If you can think of a different/better way pls tell me. To help you (help me out) i will go on from start to finsih what is happening.

In the form i make a Rs connection such as:
"select * fromhealthplan" 'on form load
then i populate a flexgrid with the data.
then i create dynamic controls (created by a class)
then when use clicks on a cell (textbox example is simpler, is sent to that cell,size and text.
then If an change occurs on the text i will send the new info back to the grid - this is done thought the class in the dynamic page
then i call a routine where i pass the recordset so i am able to update the records.

i update every time a cell changes- i find the correct item to update- this is not a problem.

it works if i hard cord the recordset ex:

HealthPlan.SaveAnyData HealthPlan.RsHealth

where i want the 'HealthPlan.Rshealth' to be a varible - i thought this would be done by extracting its value form a collection. If you can think of another meathod Pls let me know.

i can change where the code resides from healthplan to a module

Thanks
-Doug


if this was unclear what i'm trying to so let me know

I'm sorry, but I really got lost in your explanation. I did my own simple
test. I declared a connection object and collection object in a standard
module, then in the form I declared a recordset object in Form_Load.
I created the new Collection, Connection and Recordset objects, opened
the Connection, opened the Recordset, bound the Recordset to some
Textboxes, then added the Recordset to the collection. I added a
couple of command buttons to the form and in the click events I
declared a Recordset object, then set it to the collection.Item("key")
and then used it with the movenext, moveprevious, eof, bof, movefirst
and movelast methods and properties. At the end of each click event I
set the local recordset variable = Nothing. The project runs as expected
without any errors.

If you want to attach a sample project and database (zipped up) so we
can see what you are doing and the effect it is having, I think we would
have a better chance of figuring out why your collection isn't working.

Thanks for your input!

could you send me your code that you described in your last post.

---
All of my data is using an SQL database, but i will send you the approppite froms.

ok - here is the file

let me know what you think - i've attached a short doc in the begginning of the form.


good luck

-Doug

I had a fair amount of trouble getting this to run. The main problem you
are having is you don't use Option Explicit at the top of your modules
and so you don't end up declaring all your variables. So, you end up
using a variant instead of a recordset object variable in the loop you
first posted, and on top of that, the recordset object has no Name
property, which you would have seen with Intellisense if you had
declared rs As ADODB.RecordSet. Other than that, the collection works.

Sorry about the issue getting it to run.

if i used 'option explicit' how would anything change - this just uses more rigours standards.

Also i recognizied that there was no .name property for adodb.recordset.

When you say the collection works; what do you mean? Were you able to pass the Recordset using a varible?

-Doug

i'm fairly new to coding - hence this level of guidence is needed. Thanks.

my code is somehow like a maze - you could argue; without going in much detail what youwould do differently or what was my main organizational error.

I tried to put some thought into how everthing would work - and i could't think of a 'better' meathod - of course i'm new to VB and i don't know what other way there are.

-Doug

Always, always, always use Option Explicit. Debugging is a nightmare without it. I've taught some VB courses, and I absolutely will NOT accept code without Option Explicit turned on. I won't even look at it. To turn Option Explicit on so that it's automatically added to each new module, click Tools->Options and make sure that the 'Require Variable Declaration' checkbox is checked.

As to your original question, I think you need to rethink your plan. It's usually best to never have Global or Public RecordSets. I won't even declare RecordSets at the Module or Form level. It's best to keep the scope on a local procedural level so that you're not debugging thousands of lines of code when one RecordSet blows up in a small subroutine. Doing it this way, there's no need to create a collection to store the recordset. If you absolutely need a global recordset, declare it privately in a class module that you create, than expose the Properties/Methods (such as delete) you need through Get/Let/Set statements. That way, you can control how the RecordSet is used, opened, etc. You could then drop those objects instantiated from your RecordSet class into a custom Collection class created by you. Personally though, the collection seems like "overkill."

First, create a class with a Private RecordSet and Get/Set/Let procedures to expose the methods you need. Second, use the VB Class Builder to create a collection based on your new RecordSet class. Lastly, add the Delete method to the collection class that enumerates all your objects and calls their Delete methods. But, you will need to have a lot of error checking in the Class to check the State of the recordset, the cursor position, etc. (So that you're not calling the Delete method on a closed recordset, or a recordset with a cursor in the EOF or BOF position).

Sorry about the length of this post, but it's extremely important to use common sense software engineering techniques to plan and implement your projects. One of the best ways to learn though is to do what you're doing; you learn the most from your mistakes.

As I said, the troubles I had were because you weren't using more
rigours standards. Intellisense is your friend and if you just use variants
to hold objects, you can't tell what is going on. The program wouldn't
run because it knew at run time that Name wasn't a property of a
Recordset object. You can't just stick anything in and think it will run.
Once I got past that error I could do anything with the recordset object
stored in the Collection.

Please bear with me.

I went to tool->options and turned on 'Require Varible Declarations'

I then tested the program and it worked - to that point it had before this option was turned on.


My question for 'Thinker' is how did you do this; since .name still isn't a property how could you add/recall them to the collection.

Once I got past that error I could do anything with the recordset object
stored in the Collection.
This to me is a mystery. I still can't recall them.

Could you please upload the files so i could see exactly what you did.

-Doug

Turning that option on only affects new projects or new modules added
to existing projects. I actually went to the top of each module and
typed in Option Explicit, then fixed each error as I ran the program.

All I did to get past the name error was declare an actual recordset
object variable and use the name of field(0)

Private Sub Command2_Click()
Dim rs As ADODB.Recordset
'For Each rs In Me
' If TypeOf rs Is adodb.Recordset Then
' HealthPlan.Combo1.AddItem "testa"
' End If
'Next

For Each rs In AlterRecords.MyAdoCol
Command2.Caption = CStr(rs.Fields(0).Name)
Next

'Dim YY As adodb.Recordset
'Set YY.Name = myString
'SaveAnyData YY
End Sub

Ok that seems good.

Although I still can't pass the Rs in -- and this was my main problem.

Public Sub SaveAnyData(AnyRs As ADODB.Recordset)

Dim Var As String
Var = anyrs.Fields(0).Name
'there was more but not needed for this point
end sub

so then i was going to try to pass it:

Public Sub SaveAnyData(AnyRs.Fields(i) As ADODB.Recordset)

that way i could use the collection to get the field name then pass it etc

but the problem was i can't figure out how to allow: AnyRs.fields(i) to be passed, it blows up at the . between AnyRs & Fields.

Is what i am trying to impossible?
What next?

Thanks
-Doug

Sorry about my ignorance in the past. I have resolved most of my issues, Thanks again for your help.

Sincerely
Doug