Error 13: Type MisMatch 1

[kopy]

I'm trying to copy the Start Date from one form to the Start Date of each record on another form. In other words I want to change all of the Start Dates at once. Likewise for the /End Date.

I'm currently getting Error 13 Type Mismatch.

Here's code:

Dim rs As Recordset

Set rs = Forms!frmControlOutputs.Recordset

rs.MoveFirst

Do Until rs.EOF

Forms!frmControlOutputs.Form!txtStartDate = Me.txtNewStartDate.Value
Forms!frmControlOutputs.Form!txtEndDate = Me.txtNewEndDate.Value

rs.MoveNext

Loop

Set rs = Nothing

As always, any help will be greatly appreciated.

Thanks, Kopy

 

[LaurieHamlin]

I'm guessing either Me.txtNewStartDate.Value or Me.txtNewEndDate.Value is null. Try stepping through the code to see where the error comes up.

 

[Blorf]

replace
Dim rs As Recordset

with

Dim rs As Object

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

http://www.thelegacyreturns.com/

 

[kopy]

Thanks. It did the trick.

 

[strongm]

> It did the trick

Which did? Stepping through the code, or declaring rs as Object?

If the former, where did the error arise?

 

[kopy]

Declaring as an Object resolved the issue.

 

[strongm]

No, it masked it. If that 'solved' the issue, then the error was occurring on the line:

[tt]Set rs = Forms!frmControlOutputs.Recordset[/tt]

having declared

[tt]Dim rs as Recordset[/tt]

But, if they are both recordsets, why a type mismatch (and why does [tt]dim rs as Object[/tt] appear to fix things)?

The answer is that Access knows about 2 different recordset objects, one belonging to the ADO library, and one belonging to the DAO library, but bound controls only know about DAO recordsets (as DAO is the library considered native to Access)

When you declare

[tt]Dim rs as Recordset[/tt]

then Access uses a default recordset object, and which one it uses depends on the order of the two libraries in question, DAO Object Library and the ActiveX Data Object library, in the references dialog in the VBA IDE. If the ADO version is listed first, then you get the ADO recordset object, and thus you get the type mismatch you are seeing. And why does declaring it as Object seem to fix the problem? Well, because variables declared as Object take on class interface of whatever they are subsequentally set to - but, along with losing Intellisense on the variable, this leads to potential problems now that the object variable can now represent any COM object whatsoever.

In my opinion you would be better off being explicit in your declaration:

dim rs as DAO.Recordset

(alternatively you can change the order libraries are listed in the References dialog to ensure the DAO library is listed first.)