error 2950 running code from a macro (1 Viewer)

hubcap750

Registered User.
Local time
Today, 00:32
Joined
Jun 27, 2013
Messages
34
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?
 

MarkK

bit cruncher
Local time
Today, 00:32
Joined
Mar 17, 2004
Messages
8,177
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:32
Joined
Aug 30, 2003
Messages
36,115
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.
 

hubcap750

Registered User.
Local time
Today, 00:32
Joined
Jun 27, 2013
Messages
34
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

Registered User.
Local time
Today, 00:32
Joined
Jun 27, 2013
Messages
34
the function does run correctly, the macro just won't run anything after the code is called. I get the error code instead.
 

MarkK

bit cruncher
Local time
Today, 00:32
Joined
Mar 17, 2004
Messages
8,177
Has this procedure ever completed successfully?
 

hubcap750

Registered User.
Local time
Today, 00:32
Joined
Jun 27, 2013
Messages
34
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.
 

MarkK

bit cruncher
Local time
Today, 00:32
Joined
Mar 17, 2004
Messages
8,177
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.
 

hubcap750

Registered User.
Local time
Today, 00:32
Joined
Jun 27, 2013
Messages
34
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

Registered User.
Local time
Today, 00:32
Joined
Jun 27, 2013
Messages
34
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.
 

MarkK

bit cruncher
Local time
Today, 00:32
Joined
Mar 17, 2004
Messages
8,177
Here's what I don't get: what specific steps do you take to run this process? Be explicit. You said . . .
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.
 

hubcap750

Registered User.
Local time
Today, 00:32
Joined
Jun 27, 2013
Messages
34
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

Registered User.
Local time
Today, 00:32
Joined
Jun 27, 2013
Messages
34
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!
 

MarkK

bit cruncher
Local time
Today, 00:32
Joined
Mar 17, 2004
Messages
8,177
Glad you found a solution and thanks for posting back about it.
Cheers,
 

Users who are viewing this thread

Top Bottom