DLookup throws TypeMismatch after setting Option Explicit in module

[VicM]

Hi Folks,

I was attempting to cleanup code and make it more efficient. So I ran the Performance Analyzer on one of the forms which contains significant amount of code. The suggestion was to declare the class module as Option Explicit.

When trying to compile, I was directed to elements which weren't declared. So I declared them. Then when I tried to compile again, I got a Type Mismatch in a DLookup line which ran fine before the Option Explicit directive.

Code snippet follows:

Dim db as DAO.Database
Dim impmap As DAO.Recordset
Dim ImpFieldNum As Long 
Dim MemFieldNum As Long 

set db as CurrentDb

Set impmap = db.OpenRecordset("tblImportFieldMapTal", dbOpenDynaset, dbSeeChanges)

MemFieldNum = Nz(DLookup("MemFieldNum", impmap, "ImpFieldNum=" & ImpFieldNum), 0)

MemFiledNum and ImpFieldNum are both declared as Long as are their counterparts in the recordset impmap.
I've tried declaring app as Application and inserting it in front of DLookup and Nz. But that didn't help.

Now here's where I'm confused. When I substitute the actual table in the DLookup statement replacing the declared recordset (impmap), the code compiles.

MemFieldNum = Nz(DLookup("MemFieldNum", "tblImportFieldMapTal", "ImpFieldNum=" & ImpFieldNum), 0)

Does anyone know why this is so? Again without the Option Explicit statement, the original code compiled and ran.

Thanks,

Vic

 

[MajP]

Does anyone know why this is so?

Yes, of course. because the function was never meant to do that.

DLookup( expr, domain [, criteria] )

domain
Required. A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a paramete

You cannot make up your own parmater types. It is looking for a string name of table or query and you are passing in a recordset object.

Again without the Option Explicit statement, the original code compiled and ran

I can 100% guarantee you that code never ran. It compiled, but never executed. And that is why you always use option explicit.

 

[dhookom]

You might be able to use:

MemFieldNum = Nz(DLookup("MemFieldNum","tblImportFieldMapTal", "ImpFieldNum=" & ImpFieldNum), 0)

This assumes ImpFieldNum has a numeric value which seems to not be true since the variable is Dim'd but not assigned a value.

I was a bit surprised when I found out this works:

=DSum("NumberField",[RecordSource])

I'm not sure how this would differ from this more concise expression:

=Sum(NumberField)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[VicM]

MajP
I will generally bow to your expertise. However, I know my code ran and executed according to the way it was written based on the results. I understand what you said about what the DLookup was expecting; but at runtime, wouldn't I have gotten a runtime error?

Thanks for setting me straight. I am now employing the Option Explicit and will make sure any DLookups will be properly coded.

Vic

 

[MajP]

I understand what you said about what the DLookup was expecting; but at runtime, wouldn't I have gotten a runtime error?

It may have appeared that you compiled or ran this code, but as written it would never have compiled with or without option explicit. You may have had something run, but it was something else than what you are showing. Or you thought this code executed, but in fact it never executed. If you got correct results, it had nothing to do with that code. By trying to pass in a recordset object you would have gotten a type mismatch error. Option explicit has nothing to do with causing the error to occur. Option explicit has to do with requiring variable declaration.
My previous post incorrectly suggests that option explicit would have helped with this case. There are many other reasons to use option explicit. The type mismatch error would be found with or without option explicit.
The only thing similar that could have worked
MemFieldNum = Nz(DLookup("MemFieldNum", impmap.name, "ImpFieldNum=" & ImpFieldNum), 0)
because impmap.name would return the string "tblImportFieldMapTal"