Select Case and String Comparisons not working on Server 2003

drexasaurus · May 23rd, 2011, 03:36 PM

First, thanks for the help here. I haven't thanked anyone yet.

Second, I have been building a new Front End for my office here with no problems. There are, however some people outside the office who work from a Virtual Terminal using Microsoft Server 2003. By all accounts (of the other tech guys here) it should have transfered over no problem. There were huge problems, however.

Select Case statements would result in a Runtime Error 5
And String Comparisons (such as If "dog" = "cat") would Also give the same error (this is not uncommon to produce an error in many languages, but it was working fine in VBA)

It has the same version of Access installed (2010). And uses VBA 7 like it shouuld (as far as I can tell).

I Actually fixed the problem by ridding myself of most of the Select Case statements and using StrComp() = 0. But I want to know if this problem can be fixed for the future, or if that's not the case simply learn more about this problem.

I might just avoid Case statements and direct string comparisons altogether in the future if this cannot be avoidws in some scenarios. (I love my Case statements tho... sigh)

HiTechCoach · May 23rd, 2011, 04:21 PM

The is not a issue with Select case statement, etc. I use them a lot.

Make sure that you don;t have any MISSING references. This will cause all kinds of issues.

I think the real issue is that you are using Access 2010. It still has lots of issues. The4re is a huge bug in the relinking. If yu relink tables it can corrupt all the stored queries.

I will NOT use Access 2010 in production. I do use Access 2007 without issues.

The good news is that the Office 2010 SP1 update is targeted to be released in late June 2011.

If it were mine I would use the Access 2007 runtime if your database is compatible until Office 2010 SP1 is released.

I would try installing the Access 2007 runtime on a test machine and see it that runs your database run properly.

See: Backward Compatibility between Access 2010 and Access 2007

Boyd Trimmell aka HiTechCoach
Microsoft MVP - Access Expert

gjgriffith · May 23rd, 2011, 11:32 PM

Hello drexasaurus,

I saw your posting here and I was curious to find out more about the issues you are having with Access 2010. You said:

Quote:

Select Case statements would result in a Runtime Error 5

And you said:

Quote:

And String Comparisons (such as If "dog" = "cat") would Also give the same error (this is not uncommon to produce an error in many languages, but it was working fine in VBA)

And you said this is ONLY happening when you run the Access database application across MSTSC (MS Terminal Server Console)? Would you be willing to post the specific code and queries that seem to cause problems for you? I'd be willing to take a look at least and report the problem if it is related to Access 2010. Please let me know what you think when you have a chance!

Thanks,

drexasaurus · May 24th, 2011, 02:10 PM

HiTechCoach: I did check the libraries to the best of my knowledge. All the right (same) libraries seem to be up and running and I didn't see any missing ones or errors. I didn't try to reregister anything however.

I am not the big guy and going back to 2007 isn't even in consideration. I will look foward to that June update.

gjgriffirth: That is the case. I can give you examples, but they are very basic examples. Something Like

Code:

        
Select Case control.ID
            Case "GroupImport"
                Enabled = Chk_(ugAdmins, ugOperations)
            Case "GroupExport"
                Enabled = Chk_(ugAdmins, ugAccOps, ugMgmt, ugOperations)
            Case "GroupCollectData"
                Enabled = OnlyAdmins
            Case "GroupSharepointLists"
                Enabled = OnlyAdmins
End Select

I would get the error thrown on the line: Case "GroupImport". This wasn't limited to only this SelectCase statement or control.ID objects.

The string comparisons are always an issue, it doesn't matter the context. Here is the most recent example I had to fix.

From this:

Code:

Public Function CheckNextTerm(termCheck As String, typeCheck As String) As Boolean
    CheckNextTerm = False
    If currentSearchType = typeCheck Then
(...)

To:

Code:

Public Function CheckNextTerm(termCheck As String, typeCheck As String) As Boolean
    CheckNextTerm = False
    If StrComp(currentSearchType, typeCheck) = 0 Then

I have to use the latter or I get Runtime error 5 on the Virtual Terminal. Hope this helps.

HiTechCoach · May 24th, 2011, 04:41 PM

<,I am not the big guy and going back to 2007 isn't even in consideration.>>

using the Access 2007 runtime was as a test to see if the issue was related to Access 2010 or not.

In the followi8ng code:

Code:

Select Case control.ID
            Case "GroupImport"
                Enabled = Chk_(ugAdmins, ugOperations)
            Case "GroupExport"
                Enabled = Chk_(ugAdmins, ugAccOps, ugMgmt, ugOperations)
            Case "GroupCollectData"
                Enabled = OnlyAdmins
            Case "GroupSharepointLists"
                Enabled = OnlyAdmins
End Select

Enabled is a reserved word. It is a property used by different object types. What is the Enabled in your code?

My suspicion is that you are possible using reserved words for your object names which is causing ambiguity. Which can cause errors.

Are you using Option Explicit at the top of every code module?

HiTechCoach · May 24th, 2011, 04:48 PM

By Virtual Terminal are you referring to Terminal services/Server and the Remote Desktop Client?

drexasaurus · May 25th, 2011, 01:30 PM

Using the Run Time is an interesting idea. I might try that when I have more down time. Right now I got it running with the ridculous changes to the code.

As for the technology, I am not big on networking yet so I'm behind on the best terminology, but yes it is a connection through the Remote Desktop Client.

I do use Option Explicit, and the code refered to uses enabled because it is a snippit from a function used by the custom ribbon that determines whether a control should be enabled or not. It is a required parameter of that function, so that's why it's there.

drexasaurus · May 25th, 2011, 01:38 PM

Okay, it's a bigger problem then I thought. I just opened a new accdb database on that computer and tried the same test in the immediate window:
?"dog" = "dog"
and it worked fine. Opened the development version of our Front End again and got the same error number 5. Already decompiled, already compressed and repaired.

This is awful. I think I might try to move all the objects from our front end into a new file--maybe even on that computer, and see if this can fix this issue.

HiTechCoach · May 25th, 2011, 03:03 PM

<<I think I might try to move all the objects from our front end into a new file--maybe even on that computer, and see if this can fix this issue.>>

Sounds like a good plan.

Let us know how it goes.

drexasaurus · May 26th, 2011, 03:44 PM

Found the solution. Exporting the objects to a new file wasn't the solution after all.

When I decompiled the file on the Server2003 machine, however, as opposed to my Windows 7 machine, it fixed whatever references were broken and allowed for Select statements and string comparisons. That's a little unnerving, but that is what did it. That will have some consequences I'll have to try to do some research into.