Rob Kraft's Software Development Blog

Software Development Insights

Type Mismatch Error in VBA for Microsoft Access Parameters Collection

Posted by robkraft on January 8, 2021

I ran into a strange problem today with an project that I took over where code in Microsoft Access VBA is being converted. The original database, with untouched source code, has the following lines of code to cycle over all of the parameters in a querydef. The code below has been working for 15 years:

Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs(strQuery)

For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next

However, our current Access database project (*.mdb) that has the exact same code, but we have modified “something” now gets a “Type Mismatch” error number 13 when we try to “For Each” over the results of qdf.Parameters.

If I open a watch on qdf.Parameters I can view the parameters without a problem. There is only one parameter and I even confirmed that all properties of the parameter are identical when they run in the old version of our Access database app.

I could not figure out why the code no longer works. Even the query that runs is identical. However, I was able to fix the code to work again for all cases by replacing the For Each block with the following:

Dim i As Integer
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i).Value = Eval(qdf.Parameters(i).Name)
Next i

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: