×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

DLookup throws TypeMismatch after setting Option Explicit in module

DLookup throws TypeMismatch after setting Option Explicit in module

DLookup throws TypeMismatch after setting Option Explicit in module

(OP)
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:

CODE

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.

CODE

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

RE: DLookup throws TypeMismatch after setting Option Explicit in module

Quote:

Does anyone know why this is so?
Yes, of course. because the function was never meant to do that.

Quote:

DLookup( expr, domain [, criteria] )

Quote:


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.

Quote:

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.

RE: DLookup throws TypeMismatch after setting Option Explicit in module

You might be able to use:

CODE --> vba

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:

CODE --> ControlSource

=DSum("NumberField",[RecordSource]) 
I'm not sure how this would differ from this more concise expression:

CODE --> ControlSource

=Sum(NumberField) 

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

RE: DLookup throws TypeMismatch after setting Option Explicit in module

(OP)
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

RE: DLookup throws TypeMismatch after setting Option Explicit in module

Quote:

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"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close