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