Solved

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

Posted on 2010-11-17
16
396 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
  • 9
  • 6
16 Comments
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Please post the code from the switchboard button On_Click event.
OM Gang
0
 
LVL 84
Comment Utility
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
Comment Utility
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
 

Author Comment

by:lthames
Comment Utility
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
Comment Utility
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
Comment Utility
<<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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:lthames
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Very fast solution and om Gang was awesome!
0
 
LVL 28

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup". After a while, you have entered a loop for Auto repair which does not fix anything and you will be in a  panic as all your work w…
This Micro Tutorial will give you a introduction in two parts how to utilize Windows Live Movie Maker to its maximum capability. This will be demonstrated using Windows Live Movie Maker on Windows 7 operating system.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

10 Experts available now in Live!

Get 1:1 Help Now