I need help with RecordSetClone

[Robin Hickman]

Hi,

What is the problem with this code?



Private Sub Form_Current()
Dim rst As Recordset
Set rst = Me.RecordsetClone

Forms!Actions.Controls!lblMaxRecordCount.Caption = rst.RecordCount

End Sub

I get a runtime error 13 "Type Mismatch" and debug points to the line:
Set rst = Me.RecordsetClone as the problem

What am I doing wrong?

Thanks!!
Robin

 

[Sandra Daigle]

Hi Robin,

Typically the recordsetclone is a DAO recordset - if you do not disambiguate
your declaration of the recordset variable, you will get a recordset object
from the first reference library that has a recordset object, which in your
case I suspect is the Microsoft ActiveX Data Objects library.

So, how to fix your problem? Well first, you don't really need the recordset
object. You can work directly with the Recordsetclone and skip the extra
object all together (actually you already did since your findfirst is using
the recordsetclone).

If you really did need a recordset object, first you would need to make sure
that you have a reference set to the Microsoft Data Access Objects library
(DAO).
You can keep the ADO reference if you need it, otherwise just uncheck it all
together. Regardless, it is a good idea to disambiguate your object
declarations.

Instead of:

dim rst as recordset

you would use:

dim rst as DAO.Recordset

 

[Robin Hickman]

Thanks, that seemed to work (getting rid of the variable altogether) but
I still have problems. Maybe I can expalin what I am trying to accomplish.

I have a subform (Actions) in a form that I have added record navigation
controls to because I don't want the default record navigation control
at the bottom of the subform. All the buttons work fine (I just used
the wizard to create next record, last record buttons, etc.) I want to
add two controls to make it more like the default control, a text box
that tells the record I'm on, and a label that tells the total records.

I created this code as an event handler from the Current event of the
subform (Actions). It does that just fine (now that I got rid of the
variable) when I run "Actions" by itself as a stand alone form, but when
I run the main from with Actions as a subform it causes an error.

Private Sub Form_Current()

Forms!Actions.Controls!lblMaxRecordCount.Caption=Me.RecordsetClone.RecordCount
Forms!Actions.Controls!txtCurrentRecordIndicator.Value=Forms!Actions.CurrentRecord

End Sub

I am fairly new to Access and VBA (this is Access 97 btw) so I get lost
easily! I really appreciate the help!!!

Thanks,
Robin

 

[Sandra Daigle]

Hi Robin,

The short answer:

Change

Forms!Actions.Controls!lblMaxRecordCount.Caption=Me.RecordsetClone.RecordCou
nt

to
me.lblMaxRecordCount.Caption=Me.RecordsetClone.RecordCount

AlsoDo the same for the next statement also.


The 'me' keyword is used in place of the full reference whenever the code is
in the class module of the class being referenced. In other words, the code
is in the class module of the Actions form so you can refer to the the
control via the me keyword.

The reason that the original syntax works for a main form but not for a
subform is that the subform is not open as a member of the forms
collection - instead it has to be referenced through the main form (unless
the code referencing the form is within the subform's class module in which
case you use the 'me' keyword as described above).

 

[Robin Hickman]

Thanks! Once again it worked! Now the only problem is that the
lblMaxRecordCount.Caption reads 1 no matter how many records there are
until I click on one of the navigation controls in the subform, then it
reads the right number. So If I'm clicking through the records on the
main form, watching that number on the subform, it isn't right until I
do somthing on the subform. I assume this is because the code I (or
rather you) wrote is triggered on the event "Current" which apparently
isn't triggered by changes on the main form. Is there a way to do fix
that? do I need the code to be triggered by the main form's Current
event instead of the subform's? I hope this makes sense and I really
appreciate your patience!!!

Thanks,
Robin

PS you've been extremely helpful and I feel a little like I might be
taking advantage. If you would like me to start a new thread with this
question and let someone else tackle it I would be happy to do that.

 

[Robin Hickman]

The Caption does change as I navigate through the main form, but only
from 0 if there are no records to 1 if there are records, regardless of
how many. It's not until Iclick on one of the navigation buttons that I
put on the subform to naivigate through those records does it show the
correct number. That actually makes less sense to me than when I thought
it wasn't updating at all.

Robin

 

[Sandra Daigle]

Answers inline:

Thanks! Once again it worked! Now the only problem is that the
lblMaxRecordCount.Caption reads 1 no matter how many records there are
until I click on one of the navigation controls in the subform, then it
reads the right number. So If I'm clicking through the records on the
main form, watching that number on the subform, it isn't right until I
do somthing on the subform. I assume this is because the code I (or
rather you) wrote is triggered on the event "Current" which apparently
isn't triggered by changes on the main form. Is there a way to do fix
that? do I need the code to be triggered by the main form's Current
event instead of the subform's? I hope this makes sense and I really
appreciate your patience!!!

I should have caught that - the recordcount property is a low priority
property - meaning that Access doesn't update it until it gets around to it
unless forced. So, what you need to do is move to the last record then get
the recordcount. Since you don't want to be doing extra record navigation
when it is not necessary you can test for when the recordcount = 1, which
indicates that there are records, but that there *might* be more than just
one record.

This is still appropriate for the current event of the subform's form since
it fires after record navigation on the form, which always occurs after
record navigation on the main form (when the subform is linked of course).

With Me.RecordsetClone
If .RecordCount = 1 Then
.MoveLast
End If
Me.lblMaxRecordCount.Caption = .RecordCount
End With

PS you've been extremely helpful and I feel a little like I might be
taking advantage. If you would like me to start a new thread with this
question and let someone else tackle it I would be happy to do that.

Never! I wouldn't answer questions if I didn't enjoy it. Ask away - if the
subject or context of your question is totally different than the current
thread then it is often a good idea to start a new one but as long as the
new questions relate to the original I think it's best to keep it to one
thread.

 

[Sandra Daigle]

Hi Robin,

The answer to your last post should explain this - post back if you are
still having problems!

 

[Robin Hickman]

Bingo!

Works perfectly now!

Thanks so much, I learned a lot!

Robin

PS I'm just getting started with what I want this database to do, but
I'm taking it one thing at a time. I'm sure I'll be back here with more
questions later on!

 

[Sandra Daigle]

Glad to hear it!