This should be simple but it is not!

[Steven Greenberg]

Greetings (again) all.

Well, I'm developing my skills more and more with using VBA (thanks to
great support on this newsgroups) and I have ventured into working with
custom navagation buttons. I am working out of the Access 2000 VBA Handbook
by Novalis (yeah everyone knows that book).
Well anyway, I am playing with creating the recordset clone so I can create
these "smart" nav buttons that disable themselves when at certain records.
(page 546). I created the functions and the buttons and the coding and when
I tried to run it I got a type mismatch error which pointed to my
assignment line.
I then tried a simple example on another form. just the basics:

form linked to a table, command button who's click event is as follows:

dim rstClone as recordset
set rstclone = me.recordsetclone

that's all! When I run the button, I get the same debug message of type
mismatch where the debugger points to the same assignment line. the
rstclone says "=nothing" when I hilight it.

What am I missing here? Do I have to set any special reference that I
haven't done? should this simple code be working like it is? Not that my
life depends on learning this, but I think it would be very handy to know
how to do it.

Thanks to all in advance
Steve

 

[JohnFol]

I put this exact code onto a nWind form and it works 100% so it's not
syntax. To rule out your form / linked table, add a button to say Customers
and put your code in there.


If you step through the button click, what do you get if you check
? me.Recordset.recordcount
or even
? me.Recordset.name

Could be the form does not have a valid recordset

 

[Steven Greenberg]

I put this exact code onto a nWind form and it works 100% so it's not
syntax. To rule out your form / linked table, add a button to say
Customers and put your code in there.


If you step through the button click, what do you get if you check
? me.Recordset.recordcount
or even
? me.Recordset.name

Could be the form does not have a valid recordset

I just tried that in the northwind database also and it too seemed to work.
Back to my project(s). I ran the button and got the type mismatch again.
this time in the immediate window (a great tool that I am just starting to
use more often) ?me.recordset.recordsource returns the correct table that
the form is linked to. the recordsetcount returns the correct number of
records in the table. so it seems that the form does have a valid
recordsource. my set rstClone = me.recordsetclone just doesn't work. I
tried it with a new blank simple form. and in other databases that I have
created, with the same results. even on two different computers I get the
same result.

I'm still confused on this one. Do I have to set a reference to something
when I want to do something like this. such as the jet database or the ADO?
I'm stumped!

 

[JohnFol]

the recordsetclone is a member of a form, so no reference in necessary.

Might be worth checking for missing references .. .

One last thing. This is a linked table. Have you tried the same code on a
form within the same MDB as the table?

 

[Steven Greenberg]

the recordsetclone is a member of a form, so no reference in
necessary.

Might be worth checking for missing references .. .

One last thing. This is a linked table. Have you tried the same code
on a form within the same MDB as the table?

Yes it is just a regular simple table in the same database with the form in
question set to the table as its recordsource.
Which missing references might you suggest be necessary there. I checked
the northwind and set mine to the same references and still the type
mismatch problem. I'm NOT giving up though. I convinced myself that I can
do this.

 

[Stephen Lebans]

You need to have a Reference set to DAO that sits higher in your list
then ADO. In fact, you should explicitly declare your variables as
referencing the DAO object.

There is also a set of Custom Nav buttons here:
http://www.lebans.com/recnavbuttons.htm
RecordNavigationButtons is an MDB containing code to replace the
standard Navigation Buttons. The custom buttons exactly emulate the
standard navigation bar including the autorepeat property.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Steven Greenberg]

You need to have a Reference set to DAO that sits higher in your list
then ADO. In fact, you should explicitly declare your variables as
referencing the DAO object.

There is also a set of Custom Nav buttons here:
http://www.lebans.com/recnavbuttons.htm
RecordNavigationButtons is an MDB containing code to replace the
standard Navigation Buttons. The custom buttons exactly emulate the
standard navigation bar including the autorepeat property.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

Yes Stephen is correct. I found out today from searching google for this
topic that when you do a me.recordsetclone, it creates a DAO type recordset
which means that if you set a reference to the MS DAO object 3.x and
specifically declare your variable as a DAO.Recordset, then it seems to
work perfectly. Will be trying it out in my application tomorrow, for now,
it seems to work perfectly in the simple test version I set up.
I thank everyone yet again for their help, and look forward to contributing
my own knowledge when needed.
Steve

 

[Steven Greenberg]

the recordsetclone is a member of a form, so no reference in
necessary.

Might be worth checking for missing references .. .

One last thing. This is a linked table. Have you tried the same code
on a form within the same MDB as the table?

have already figured out how to do it. it seems that the recordset clone is
returned as a DAO recordset. I set a reference to the MS DAO 3.6 and
declared the recordset specifically as a DAO.Recordset. and it works
perfectly now.
Thanks
Steve