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?
error 2950 running code from a macro
- Thread starter hubcap750
- Start date
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.
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.
- Local time
- Today, 02:46
- Joined
- Aug 30, 2003
- Messages
- 36,270
lol, Paul. Timing . . .
It's time for lunch and I'm getting hungry, which slowed me down.
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 FunctionI 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, 02:46
- Joined
- Mar 17, 2004
- Messages
- 8,440
Here's what I don't get: what specific steps do you take to run this process? Be explicit. You said . . .
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.
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.
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.
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!
