Option Compare Database vs. Explicit

Posted on 2012-09-18
Last Modified: 2012-09-18
Hello Experts:

I could use some help with regard to why some of our code is failing on certain machines. I'm getting a general error ‘5’ Null value when attempting to run the code on a machine that has Access 2010. Interestingly, the machine that I use also has Access 2010 and it works successfully on that machine. I've eliminated this from being a permissions issue by logging in as administrator on the user machine failing and the error still occurs. And finally, I checked the Reference Library to find that we are both using the same libraries and there are no “Missing” warnings in either.

The code fails at the simplistic IF THEN statement, which when researched makes me believe that there is an issue with the Option Compare Database versus and Option Explicit in the General Declarations. Could this be the case? We are using "Option Compare Database" in our (general) section.

This is where I need help, can someone please explain how these work? And why this would effect one machine differently than another.

I have included the code for review. And when debugging, the fails begins at line 9:

Dim strPorS As String
strPorS = cmb_PorSorB
Dim strPrm As String
Dim strSnd As String

Dim strdlt As String

'Build string to include both in the Where clause criteria...P and S
If strPorS = "B" Then strPorS = "P or S"
If strPorS = "B" Then strPrm = "P"
If strPorS = "B" Then strSnd = "S"

Open in new window

Thank you for your help and please let me know if you want more specific information regarding the libraries or anything else that may be relevant.

Question by:CPKGDevTeam
    LVL 77

    Expert Comment

    I can't say why you are getting the error but the code as posted doesn't make sense.
    You are testing a variable for value od 'B' and if it is 'B' you are chnaging the variable value.
    But the next two lines both test the same variable for 'B' which it can never be.
    LVL 77

    Expert Comment

    And just to say that Option Compare Database and Option Explicit are not alternatives.
    The first sets the sort sequence/comparison rules and the second requires variables to be declared before use.  You should ALWAYS have Option Explicit, whatever else you might have so that you detect when you mis-spell variable/object  names.

    Author Comment

    Hi peter57r:

    I realize the code is problematic. This is actually a code that was written by a team member. And while I concede it does have corrections to be made, that should not have kept the line 9 from working. If you strip out all the other code after that (which I did for troubleshooting) the code still halts at line 9 with an error.

    I am unsure of what you mean by setting the sort sequence/comparison rules...would you be able to give me a quick simple example of that?

    Would you then suggest I change the (general) to say Option Explicit instead of Option Compare Database? How would this affect forms that have already been created? Would a debug ---> Compile help to find issues when I do change the declarations?

    Sincere thanks for your time and your thoughts,
    LVL 77

    Accepted Solution

    Add this line as a new line 2 etc (push the rest down)...
    if isnull(cmb_PorSorB) then
    msgbox  "It's NULL"
    msgbox cmb_PorSorB
    end if
    and see what the msgbox says when you run it.

    If you read my previous response I say that you should always have Option Explicit.
    I doubt very much whether the Option Compare statement is relevent to your problem; what regional setting for country do you have?

    Author Comment

    I do see that you recommend always using the Option Explicit, thanks for that. How would this affect forms that have already been created? Would a debug ---> Compile help to find issues when I do change the declarations?

    Regional Settings: I was looking in the Options of the current database and we are using "general - legacy" - although this might not be the answer to your question about the regional setting. If you know where I can get this information in the application, I would be more than happy to supply it.

    I have included a screen shot of the details of the Option screen.

    Thanks for your continued help,
    LVL 77

    Expert Comment

    Yes; if you add Option Explicit to a module you shpuld use Debug>Compile to check there are no errors reported.  If there are , they need to be fixed.

    Use the code I posted before and report the results.

    Regional settings are settings for your computer not for Access. They are in the control panel.

    Author Comment

    Thanks, I did find each machine's region setting is the same: United States.

    I will run the debug and let you know as soon as I do if this should fix the problem. I will also repost the code once it has been cleaned up and is close to the final product.

    Just extra info - On the form, that combo box should never be null, as it is required to decide what happens to the data in that table.

    Again I appreciate your help and will report back soon.

    Thank you,

    Author Closing Comment

    Hi Peter57r:

    The change in declarations did the trick.

    When I was working on the users machine with the failure, I left in the "Option Compare Database" and then after it failed, as a test to the theory - I changed it to "Option Explicit".

    When I ran the code again, it worked. I would post the code (as I had promised above) but I have decided against it. It wouldn't really serve any purpose, as it was drastically modified and doesn't resemble the original code, except for the first IF statement.

    I was hoping for some information/reasoning re: the Compare/Explicit etc, but I will fumble through my own research when time allows.

    Thank you for your help,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now