Sudden "Type Mismatch" Error with querydef parameter

[Mike Thomas]

In Access 2000 after having used this construct for a couple of years, I am
suddenly getting the error message "Runtime error 13 - Type Mismatch" on
line 5 below.

I have the same libraries checked as before -
Visual Basic for Applications
Microsoft Access 9.0 Object Librayy
Microsoft Excel 9.0 Object Library
Microsoft DAO 3.6 Object Library

I don't use this construct anymore, I've switched to qdf.parameters(0) =
"xxx", and it is working ok, but if I don't find the cause I will have to
change many such snippets throughout the app.

Does anyone know what might be causing this?

Many thanks,
Mike Thomas

1 Dim prm As Parameter
2 Dim qdf As QueryDef

3 Set qdf = CurrentDb.QueryDefs("EditComments")
4 intI = 1
5 For Each prm In qdf.Parameters
6 If intI = 1 Then
7 prm.Value = Me.txt_Date
8 Else
' prm.Value = lngResbonsibility
9 End If
10 intI = intI + 1
11 Next prm
12 qdf.Execute

 

[Allen Browne]

The type mismatch suggests there is a problem matching the value to the
parameter.

Your example suggests that each parameter is a date. If txt_Date is an
unbound text box, set its Format property to Short Date as a way of
identifying the data type. (Not needed for bound fields, where the
ControlSource identifies the data type).

Also, be sure to declare each of the parameters. With the query open in
design view, that's Parameters on the Query menu. Again, this eliminates all
chance of guesswork as to the data type.

If that does not solve the problem, be explicit with your declarations as
well:
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
and be sure to dereference the objects before existing:
Set prm = Nothing
Set qdf = Nothing

 

[Mike Thomas]

Thanks Allan,

What had happened (this is something that really bugs me about Access) is
the "Microsoft DAO 3.6 Object Library" had moved down a place in the
References list to 4th place after the "Excel 9.0" library (as per below).

When I moved it back to 3rd place, everything was fine.

The other fix, which worked but would have had to have been applied in too
many places, was to use Parameters(x) rather than the For Each prm
construct.

Thanks for your help.

Mike Thomas

 

[Allen Browne]

Great. Glad it's solved.

The order of references issue can be avoided by the last suggestion in the
previous post, i.e. disambiguate your declarations:
Dim prm As DAO.Parameter