• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 814
  • Last Modified:

Option Compare Database vs. Explicit

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.

  • 4
  • 4
1 Solution
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.
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.
CPKGDevTeamAuthor Commented:
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,
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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?
CPKGDevTeamAuthor Commented:
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,
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.
CPKGDevTeamAuthor Commented:
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,
CPKGDevTeamAuthor Commented:
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,

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now