Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 16 votes, 5.00 average. Display Modes
Old 06-27-2013, 10:14 AM   #1
hubcap750
Newly Registered User
 
Join Date: Jun 2013
Posts: 34
Thanks: 7
Thanked 0 Times in 0 Posts
hubcap750 is on a distinguished road
error 2950 running code from a macro

Most of what I do can be done with queries, but I have a spot that I need a bit of code for. I've written the code as a public function so I can call it from a macro where I run the rest of the queries. The code runs fine on its own, and so does the macro until it hits where I call the code from, then it crashes with a "2950" error. The database is in a trusted folder, so that isn't the problem. I'm wondering if I am missing some statements at the end of the code that would return it to the macro. The code is rather lengthy and does involve several nested if statements in a loop, so I didn't want to post the whole code. Like I said, the code is working, it's just crashing the macro. What am I missing?

hubcap750 is offline   Reply With Quote
Old 06-27-2013, 10:23 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,287 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Welcome to the forum

What's the error description. When I raise 2950 here I get "Application-defined or object-defined error," which is not that revealing.
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 06-27-2013, 10:23 AM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,087
Thanks: 10
Thanked 3,873 Times in 3,816 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: error 2950 running code from a macro

What is the error description? Is the function running at all that you can tell? You shouldn't need anything special; once the function finishes, control should pass back to the macro.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 06-27-2013, 10:27 AM   #4
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,287 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
lol, Paul. Timing . . .
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 06-27-2013, 10:27 AM   #5
hubcap750
Newly Registered User
 
Join Date: Jun 2013
Posts: 34
Thanks: 7
Thanked 0 Times in 0 Posts
hubcap750 is on a distinguished road
Re: error 2950 running code from a macro

All I have is the code "2950" which Microsoft says is "This issue occurs if the database is not trusted by Access 2007. By default, Access 2007 opens databases that are not trusted in Disabled mode. In Disabled mode, executable content is disabled." But I checked that, and it is in a trusted location. It does run on it's own, just not when called from a macro.
hubcap750 is offline   Reply With Quote
Old 06-27-2013, 10:28 AM   #6
hubcap750
Newly Registered User
 
Join Date: Jun 2013
Posts: 34
Thanks: 7
Thanked 0 Times in 0 Posts
hubcap750 is on a distinguished road
Re: error 2950 running code from a macro

the function does run correctly, the macro just won't run anything after the code is called. I get the error code instead.
hubcap750 is offline   Reply With Quote
Old 06-27-2013, 10:31 AM   #7
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,287 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Has this procedure ever completed successfully?

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 06-27-2013, 10:33 AM   #8
hubcap750
Newly Registered User
 
Join Date: Jun 2013
Posts: 34
Thanks: 7
Thanked 0 Times in 0 Posts
hubcap750 is on a distinguished road
Re: error 2950 running code from a macro

Not when called from a macro, even if it is the only thing the macro is doing. I suspect I am missing a command at the end of the function that would tell it to return to the macro. I've tried adding "Exit Function" and "Return" but they don't seem to have any effect at all. It runs just fine on it's own.
hubcap750 is offline   Reply With Quote
Old 06-27-2013, 10:36 AM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,087
Thanks: 10
Thanked 3,873 Times in 3,816 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: error 2950 running code from a macro

Quote:
Originally Posted by lagbolt View Post
lol, Paul. Timing . . .
It's time for lunch and I'm getting hungry, which slowed me down.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 06-27-2013, 10:49 AM   #10
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,287 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
So don't run it from a macro. How do you start the macro?

Code that completes normally returns control to any calling code. There is no special instruction required for this. It is the default behavior.
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 06-27-2013, 10:51 AM   #11
hubcap750
Newly Registered User
 
Join Date: Jun 2013
Posts: 34
Thanks: 7
Thanked 0 Times in 0 Posts
hubcap750 is on a distinguished road
Re: error 2950 running code from a macro

I have to admit there is a real possibility that I am just doing something stupid here, so I might as well post the code. What it does is divide up groups of 8 sampling units up among stations as close to each other as possible, so if I have a total of 15 sampling units, one will get 8 and another 7. I've converted all this from some really old code written in FoxPro in the early 1990's.

Code:
Public Function tapespread()

Dim db As Database
Dim rs As Recordset

Dim i As Integer
Dim MaxTape As Integer
Dim TTapes As Integer
Dim RemTape As Integer
MaxTape = (8)
TTapes = (8)

Set db = CurrentDb
Set rs = db.OpenRecordset("QQVIEW1")

For i = 0 To rs.RecordCount - 1

        rs.Edit
        rs.Fields("STATIONS1") = Int(rs.Fields("tapes") / MaxTape)
        rs.Fields("TAPES1") = 8
 ' set stations2 to 1 for later spreading out of uneven number of sampling units
        rs.Fields("STATIONS2") = 1
        rs.Fields("TAPES2") = 0
        rs.Update
        TTapes = (8)
'if tapes divide evenly, set stations2 to 0

        If rs.Fields("stations1") * rs.Fields("tapes1") = rs.Fields("tapes") Then
            rs.Edit
            rs.Fields("stations2") = 0
            rs.Update
        End If
        
Do While (rs.Fields("stations1") * rs.Fields("tapes1")) + (rs.Fields("stations2") * rs.Fields("tapes2")) <> rs.Fields("tapes")
' for regions with 8 tapes or less, 1 station will be assigned all SUs

    If rs.Fields("TAPES") <= 8 Then
        rs.Edit
        rs.Fields("STATIONS1") = 1
        rs.Fields("TAPES1") = rs.Fields("TAPES")
        rs.Fields("STATIONS2") = 0
        rs.Update
    Else

' for regions with more than 8 tapes
' if no remainder, continue
 RemTape = rs.Fields("TAPES") Mod TTapes
 ' probably not needed here
 
        If RemTape = 0 Then
            rs.Edit
            rs.Fields("STATIONS2") = 0
            rs.Update
        End If
        
'if remainder is within 1 of tapes1, assign to one station

            If TTapes - RemTape = 1 Then
                rs.Edit
                rs.Fields("Tapes2") = RemTape
                rs.Update
            End If
                
'if remainder is 2 less than tapes1, spread out remainder

                If TTapes - RemTape = 2 Then
                    rs.Edit
                    rs.Fields("STATIONS1") = rs.Fields("STATIONS1") - 1
                    rs.Fields("Stations2") = rs.Fields("Stations2") + 1
                    rs.Fields("Tapes2") = (RemTape + 1)
                        If rs.Fields("Stations1") = 0 Then
                        rs.Fields("Stations1") = rs.Fields("Stations2")
                        rs.Fields("Tapes1") = rs.Fields("Tapes2")
                        rs.Fields("Stations2") = 0
                        rs.Fields("Tapes2") = 0
                        End If
                    rs.Update
                End If
rs.Edit
rs.Fields("tapes1") = TTapes
rs.Update
TTapes = TTapes - 1
'if remainer greater than 2 loop again

    End If


Loop
rs.MoveNext
Next i
End

rs.Close
Set rs = Nothing
Exit Function
Return
End Function
hubcap750 is offline   Reply With Quote
Old 06-27-2013, 10:59 AM   #12
hubcap750
Newly Registered User
 
Join Date: Jun 2013
Posts: 34
Thanks: 7
Thanked 0 Times in 0 Posts
hubcap750 is on a distinguished road
Re: error 2950 running code from a macro

Quote:
Originally Posted by lagbolt View Post
So don't run it from a macro. How do you start the macro?
I have about 20 queries to run before the code, and another 15 or so afterwords. I just tried to "convert macro to code" and while it doesn't crash with the error, it also doesn't run any queries after the error either. I could just break the whole thing up and deal with the crash if that's the only solution, I'm just not experienced enough to know if it's me or the machine yet.
hubcap750 is offline   Reply With Quote
Old 06-27-2013, 11:11 AM   #13
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,287 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Here's what I don't get: what specific steps do you take to run this process? Be explicit. You said . . .
Quote:
The code runs fine on its own, and so does the macro until it hits where I call the code from
Code doesn't run on it's own. You start it somehow. How?

So the macro runs fine until it hits where you call the code from. What does that mean? Do you step through the macro? How do you know that the macro runs fine up till a certain point?

The function you posted. Does it run without error if you start it directly? If not, where does it fail and with what error? If it runs OK, it is not the problem and we can rule it out.
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 06-27-2013, 11:27 AM   #14
hubcap750
Newly Registered User
 
Join Date: Jun 2013
Posts: 34
Thanks: 7
Thanked 0 Times in 0 Posts
hubcap750 is on a distinguished road
Re: error 2950 running code from a macro

If I hit the play button at the top of the code directly from the VB editor, it runs fine. No stops, does what it is suppose to do. If I use "RunCode" in a macro, the macro runs up to that point creating and updating tables but then stops when the error message box pops up. If I convert the macro to VB and run it from the editor, it doesn't stop, but it also doesn't run the query after the code which would create another table. So might it be that I am not calling the code correctly from the macro? I made it a public function in order to get adding it as an option.
hubcap750 is offline   Reply With Quote
Old 06-27-2013, 12:58 PM   #15
hubcap750
Newly Registered User
 
Join Date: Jun 2013
Posts: 34
Thanks: 7
Thanked 0 Times in 0 Posts
hubcap750 is on a distinguished road
Re: error 2950 running code from a macro

After doing some further research, I found a solution. Instead of running the code from a module, I created a blank form and copied it to its OnLoad event. Then I opened and closed the form on my macro and it all worked fine. Someone in my office suggested that the reason the code wasn't running using the RunCode command in the macro has to do with the latest Java security update, but I haven't seen that mentioned elsewhere. Anyway, thanks for listening!

hubcap750 is offline   Reply With Quote
Reply

Tags
error 2950

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error Message when running 'On Load' VBA code / Macro ersocia Modules & VBA 6 08-30-2012 05:44 AM
Access 7 Macro error 2950 deeranwith Macros 6 05-26-2011 12:51 PM
error 2950 in macro nawaray General 6 04-05-2011 07:15 AM
Getting 2950 when running Macro bjreb Macros 1 02-16-2011 09:31 PM
Error 2950 when running setvalue macro scottshr Macros 1 05-24-2009 05:16 PM




All times are GMT -8. The time now is 03:45 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World