Solved

Problems with VBA code in Access 2000 database while running under Access 2007 in Windows 7

Posted on 2010-11-17
16
403 Views
Last Modified: 2012-06-21
I am having a problem with an Access database on some new computers.  The database was written in Access 2000.  

It is located on a network drive.  The clients had installed some new computers and contacted me for help when the database wouldn't work.  

At first, all computers were getting the same error.  Any 'button' on their switchboard triggered the following error:

The expression On Click you entered as the event property setting produced the following error:  The expression you entered has a function name that Microsoft Access can't find.

The expression is a VBA function, so I assumed the security was an issue.  

I was able to fix the XP computers running Access 2002 and 2003 by setting the Macro Security level to Low.   They all work now.

But I have not been able to do figure out anything on the windows 7 computers.  I downloaded the files to my own network (this is for a client) and I get the same errors.  The files are located on a network drive mapped to N:\

There are actually 2 mdb files.  A front end and a back end.  All of the tables are in the back end and all of the queries, code, macros, reports are in the front end.  They are on the same folder.

I have made the folder a trusted location, I've run Access using XP compatibility mode, logged in as administrator, Run as administrator, removed the password they had on the vba modules.

I added autoexec macro to show if the current project has content enabled and it IS enabled.  

I have also tried running the database on my local drives only by  mapping N:\ to C:\

So far nothing works.  I know it must be something related to the code not being available because the error is the same exact error I received on XP when the Macro Security settings are set to Med or High.

Any thoughts?


It runs fine from XP computers using Access 2002 and Access 2003.  

0
Comment
Question by:lthames
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
16 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 34156802
Please post the code from the switchboard button On_Click event.
OM Gang
0
 
LVL 84
ID: 34156977
Are you installing the FE on the local user machine, or are they running it from the network share? Not the BE (which should be on the share), but the FE?

The correct way to manage this is to move a COPY of the FE to each workstation, and to then launch that copy. All copies of the FE would be linked to the same BE file.

Was the machine hosting the BE file changed to a new OS? If so, which OS?

On the BE machine, where is it located (i.e. which folder)?

0
 

Author Comment

by:lthames
ID: 34156993
In the On Click event, the value is:
=MV(1)

MV(1) is a function in a module form_SB  (SB is the switchboard form)

Sub MV(Y%)
    'SET TOGGLE-BUTTON VALUES TO FALSE
    Dim c As Control
    Dim x$, Q, b%
    Dim i, MN%, MX%
    Dim RS As Recordset
    Dim DB As Database
    If ActiveControl.Name = "B34" Then
        Q = MsgBox("Are you sure?", vbYesNo, "DELETE")
        If Q = vbYes Then
            Set DB = CurrentDb
            Set RS = DB.OpenRecordset(RDQ("FAVS2") & UserName & "'")
            b = RS.Fields(0)
            If b <= 0 Then
                MsgBox "NOTHING TO RESET", &H30, "ERROR..."
                CLOSEDB RS, DB
                B33.SetFocus
                Exit Sub
            End If
            DoCmd.SetWarnings False
            DoCmd.RunSQL RDQ("FAVS3")
            DoCmd.SetWarnings True
            ActiveControl.Value = 1
            CK1.Visible = True
            B33.SetFocus    'RETURN FOCUS TO SET BACK TO BASE
            B33 = &H1       'SET BACK TO BASE
            Exit Sub
        Else
            B33.SetFocus
        End If
    End If
    For i = 1 To 36
        If Not Me.Controls("B" & i).Name = ActiveControl.Name Then
            Me.Controls("B" & i) = 0
        End If
    Next
    For Each c In Me.Controls
        If TypeOf c Is TextBox Then c.Visible = False
        If TypeOf c Is CommandButton Then
            If c.Name Like "BTT*" Then c.Visible = False
        End If
        If TypeOf c Is Label Then c.Visible = False
    Next c
    Set c = Nothing
    LB4.Visible = True
    ActiveControl = &H1
    DoEvents
    FOPT Y
End Sub

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lthames
ID: 34157025
But let me add that it isn't anything IN MV sub because I have changed the sub to have only a msgbox statement and it gets the error before getting into MV
0
 

Author Comment

by:lthames
ID: 34157118
LSM Consulting,

Both files are on the sale network shared folder.  However, I downloaded both files from my client and tried them in the following configurations in my office and still get the same errors:

I put both files on the same folder on a network drive and ran Access from  my computer.
I put both files on my local drive on the same folder.
I put the front end on my local drive with the back end on my network drive.

I was also able to run it from another machine in my office that has XP and Access 2000.  I don't have any other local machines that aren't running windows 7 and Access 2007.

0
 
LVL 28

Expert Comment

by:omgang
ID: 34157150
<<In the On Click event, the value is:
=MV(1)
>>

What happens if you actually create a button On_Click procedure for the button like

Private Sub MyButton On_Click()
    Call MV(1)
End Sub

??

OM Gang
0
 

Author Comment

by:lthames
ID: 34157158
Update . . .  I just removed all of the linked tables from the database that point to the back end.  I get the same error anyway.  So I don't think it has anything to do with the back end database.

I am going to import all of the tables from the backend file into the front end database just be make sure.
0
 
LVL 28

Expert Comment

by:omgang
ID: 34157174
How big is your application?  Is it possible to upload a copy?  You'd want to be sure there was no confidential data included.  If possible it'd be best to only upload sample data in each of the tables.  I have Access 2007 running on a Win7 Pro machine I could test with.
OM Gang
0
 

Author Comment

by:lthames
ID: 34157226
omGang:

Very interesting!

When I do what you suggested, I get a message box with  INFO  as the caption and the message is "Unable to execute command".

Before the call MV(1) statement I added my own message box (like I had done before at the beginning of sub MV) and I get the message box but then get the error on the Call MV(1) line.

What do you think?
0
 

Author Comment

by:lthames
ID: 34157245
omGang,

It is 12 MG without the back end database, and since I get the error regardless I guess the back end isn't needed.  is 12 MG too big to upload?
0
 
LVL 28

Expert Comment

by:omgang
ID: 34157267
Try peforming a compact and repair on the front end (make a copy) and then zip it.
OM Gang
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 34157351
I've attempted to duplicate your situation.

When I set the OnClick action for the command button to =MV(1) I receive the same error message you are seeing.
When I set the OnClick action to [Event Procedure] and set the following it works correctly.

Private Sub MV(Y%)
On Error GoTo Err_MV

    MsgBox "This is the message", , "Message"

Exit_MV:
    Exit Sub

Err_MV:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub MV of VBA Document Form_fLGrp3"
    Resume Exit_MV

End Sub

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

    Call MV(1)

Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Command0_Click of VBA Document Form_fLGrp3"
    Resume Exit_Command0_Click
End Sub


I'm thinking the earlier versions of Access must have allowed you to call a private form Sub Procedure by simply setting the OnClick action to =ProcedureName(input param).  In general we can call a function in this manner but not a sub.  To call a Sub Procedure we normally do so by using the Call command.

If you were to change the MV sub to a function it should resolve the issue as it is allowable to call a function in the manner you are doing (set the OnClick action for the button to =MV(1)).  I have tested in Access 2007 on Win 7.

Private Function MV(Y%)
    'SET TOGGLE-BUTTON VALUES TO FALSE
    Dim c As Control
    Dim x$, Q, b%
    Dim i, MN%, MX%
    Dim RS As Recordset
    Dim DB As Database
    If ActiveControl.Name = "B34" Then
        Q = MsgBox("Are you sure?", vbYesNo, "DELETE")
        If Q = vbYes Then
            Set DB = CurrentDb
            Set RS = DB.OpenRecordset(RDQ("FAVS2") & UserName & "'")
            b = RS.Fields(0)
            If b <= 0 Then
                MsgBox "NOTHING TO RESET", &H30, "ERROR..."
                CLOSEDB RS, DB
                B33.SetFocus
                Exit Sub
            End If
            DoCmd.SetWarnings False
            DoCmd.RunSQL RDQ("FAVS3")
            DoCmd.SetWarnings True
            ActiveControl.Value = 1
            CK1.Visible = True
            B33.SetFocus    'RETURN FOCUS TO SET BACK TO BASE
            B33 = &H1       'SET BACK TO BASE
            Exit Sub
        Else
            B33.SetFocus
        End If
    End If
    For i = 1 To 36
        If Not Me.Controls("B" & i).Name = ActiveControl.Name Then
            Me.Controls("B" & i) = 0
        End If
    Next
    For Each c In Me.Controls
        If TypeOf c Is TextBox Then c.Visible = False
        If TypeOf c Is CommandButton Then
            If c.Name Like "BTT*" Then c.Visible = False
        End If
        If TypeOf c Is Label Then c.Visible = False
    Next c
    Set c = Nothing
    LB4.Visible = True
    ActiveControl = &H1
    DoEvents
    FOPT Y
End Function

OM Gang
0
 

Author Comment

by:lthames
ID: 34157363
om Gang,

Scrap everything I said about what happened when I tried your suggestion.  I forgot that I had previously deleted all code in Sub MV() and replaced it with

MsgBox "Unable to execute command.", vbCritical, "INFO"

So the message I got was just my own code.  

So my deduction is that it works with your suggestion, adding the on click event to the forms modules instead of putting the procedure in the event property for On Click.  

And I double checked . . windows 7 works if it is not Access 2007.  So I guess it is just an Access 2007 issue.

Can you think of any workarounds or do you think they are stuck with changing the code?  It would be fine for me . . . I get to bill them by the hour . . . but I hate to do that to them if there is another option!

By the way, THANKS for your help!!!!!!
0
 

Author Comment

by:lthames
ID: 34157372
OMGang,  I just saw your other response.

I will see how many they have and see which is better, changing to a function or changing the way they are called.

Thanks so much!!!!!!!!
0
 

Author Closing Comment

by:lthames
ID: 34157385
Very fast solution and om Gang was awesome!
0
 
LVL 28

Expert Comment

by:omgang
ID: 34157750
lthames, glad it's working for you and was an easy fix.
OM Gang
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will teach you the basics of configuring your computer to improve its speed. It will also teach you how to disable programs that are running in the background simultaneously. This will be demonstrated using Windows 7 operating…
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

752 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