Module Code won't run when executed by macro

[jallen919]

I have created a module that I can get to run when running it from VBA but when I reference the module in a macro using either the OpenModule AssignBrokers or RunCode AssignBrokers() -- the module doesn't work correctly. Any clues? Below is the module code that is being run.

Public Sub AssignBrokers()

Dim rstLeads As ADODB.Recordset
Dim rstBrokers As ADODB.Recordset

Set rstLeads = New ADODB.Recordset
Set rstLeads.ActiveConnection = CurrentProject.Connection
rstLeads.CursorType = adOpenKeyset
rstLeads.LockType = adLockOptimistic
rstLeads.Source = "tblImportedRecords"
rstLeads.Open Options:=adCmdTableDirect

Set rstBrokers = New ADODB.Recordset
rstBrokers.Open "SELECT BrokerCode FROM tblBrokers WHERE ActiveBroker=true", CurrentProject.Connection

While Not rstLeads.EOF
If (rstBrokers.EOF) Then
rstBrokers.MoveFirst
End If
rstLeads!BrokerCode = rstBrokers!BrokerCode
rstLeads.Update
rstBrokers.MoveNext
rstLeads.MoveNext
Wend

rstLeads.Close
rstBrokers.Close

Set rstLeads = Nothing
Set rstBrokers = Nothing

End Sub

 

[ghubbell]

Still at it J!
The sub probably doesn't appear on your list of "function" choices if you hit the builder "..." button. You could name it a public function to do it this way but! If you are running this from a form off of say, a command buttons click event, you should be able to just type in:
AssignBrokers
and it would go.
Gord
ghubbell@total.net

 

[jallen919]

Well I want it to be run as part of a macro because I have some append and update queries run, then I need the module to run. If I run is separate from the macro it works just fine but if I call it from within the macro it doesn't seem to work... should I be running all commands from behind a button or do you think using a macro is fine?

 

[ghubbell]

Good Morning JAllen,
I'm afraid I'm one of those people who will not use a macro for anything. Now there's reasons for this and the big one is "error handling". There's no way to control errors in macros.

Everything you do in macros may be easily done in modules. In 2000 and I seem to recall 97, you may click on a macro in the database window, go to File-Save As and choose Module. You'll even be offered to add comments and "error handling".

How and where you run subs or functions is entirely up to you...If this is a one shot thing (you're just using the sub to redo some records, you can open its module, click anywhere within the sub, hit the blue right arrow on the tool bar and it will go. If you want to run it as say, a monthly routine, you might make a "utilities" form and put the call to it behind a command button. (Maybe add a message box at the end that says "Done!" so you know it ran properly.

In the case of running with other queries your VB code might look like this:

On error goto Err1

DoCmd.Setwarnings False 'no "you are about to..." messages
DoCmd.OpenQuery "yourQueryName"
DoCmd.Setwarnings True 'turn them back on for your code
AssignBrokers
MsgBox "Done!"

Exit1:
Docmd.setwarnings True 'really only need this here
Exit sub

Err1:
MsgBox Err.Number & " " & Err.Description,vbinformation,"An error has occured!"
Resume Exit1

Hope that gives you some ideas! Gord
ghubbell@total.net

 

[RickSpr]

Sub procedures can not be called by macros. This is just a bizarre quirk of Access--even though the macro can't do anything with the function result, the called procedure must be a Function.

You can just change Sub to Function and it should work. You don't even have to add &quot;As <something>&quot; to the function declaration; it will default to As Variant, which doesn't matter because nothing is done with it anyway. If you already call AssignBrokers from elsewhere in your code, that will still work, too. Rick Sprague