varArray with type mismatch error using GetRows method

[KSimm]

I'm trying to populate an array with rows from a table.

Right now I've only got one row in the table for test and debugging purposes, and I'm able to connect to the db, set the recordset, and I can count the rows in the recordset (while debugging, I can see the RecordCount is holding a value of 1).

for some reason, I'm getting a type mis-match error on this statement:

Set varArray = rstBedDays.GetRows(rstBedDays.RecordCount)

I've already declared varArray as Variant, so now I'm really confused. I've searched the forums and the web to see if I can find out where things are going wrong, but still haven't been able to fix this.

Can anyone give me any advice, please?

Thanks so much.

 

[KSimm]

type mismatch due to ADO library and DAO library both being active

I was able to solve (I think) my original problem. I was only referring to DAO objects, but I had the DAO library at a lower priority than the ADO library.

I switched that and now I still get an initial type mismatch error, but can step through it while debugging? After stepping past the type mismatch I am getting an error message that there is no current record. (Even though RecordCount still counts the one record that is in the table.)

Back to the drawing board. I'm still searching the forums and MSDN Library, but I welcome any suggestions.....

 

[KSimm]

clone is the answer

I finally got it to work.

I ended up changing my statement to:

Set varArray = rstBedDays.Clone

and so far everything seems to be fine.

 

[Jon K]

for some reason, I'm getting a type mis-match error on this statement:

Set varArray = rstBedDays.GetRows(rstBedDays.RecordCount)

To populate an array, you don't need to use the word Set.


Set varArray = rstBedDays.Clone will give you a recordset, not an array.
.

 

[KSimm]

Thanks.

so does that mean that I've created another recordset named varArray that is a clone of my original recordset named rstBedDays?

My apologies for all of the questions. It's been quite a while since I've used VB, and I've never used VBA while in Access to manipulate Access records.

 

[Jon K]

so does that mean that I've created another recordset named varArray that is a clone of my original recordset named rstBedDays?

Yes. You can test for a recordset object with a message box like this:-

Set varArray = rstBedDays.Clone
MsgBox varArray![aFieldNameHere]
   
varArray.Close
MsgBox varArray![aFieldNameHere]

If the field is not Null, the first message box should return the field contents.

The second message box should return an error because the recordset has now been closed.

.

 

[KSimm]

Thanks for your help and explanations. I really appreciate it.