Compile Error but not understanding why this has happened....

Hi all,

I am trying to call some macros in Access 2010 using a loop, but get an error message before I can even step into it.

I could manually run all of the macros, but this seems tedious and I can't figure out why this isn't working!

I have macros names UpdateTableProcess5_ACD, UpdateTableProcess6_ACD, UpdateTableProcess7_ACD etc. up to UpdateTableProcess44_ACD.

I have written the following code, but this generates the following error...

Compile Error: Expected Sub, Function or Property

Sub CallAll()

Dim strMacro As String
Dim i As Integer


For i = 5 To 44

    strMacro = "UpdateTblProcess" & i & "_ACD"

    Call strMacro

Next i



End Sub

Open in new window



Does anyone have any ideas how to work around this?

Thanks,

Sarith
Sarith GadaData AnalystAsked:
Who is Participating?
 
Anders Ebro (Microsoft MVP)Connect With a Mentor Microsoft DeveloperCommented:
You should use
Application.run strMacro

Open in new window

for code procedures. RunMacro is only for stored macros (The ones created through the GUI).
1
 
NorieVBA ExpertCommented:
Sarith

You can't call macros like that in Access, try this.
Sub CallAll()

Dim strMacro As String
Dim i As Integer


For i = 5 To 44

    strMacro = "UpdateTblProcess" & i & "_ACD"

    DoCmd.RunMacro strMacro

Next i



End Sub

Open in new window

1
 
Sarith GadaData AnalystAuthor Commented:
Hello,

Thank you for your response.

I now have an error message: Run-time error '2485' with message : Microsoft Access cannot find the object 'UpdateTblProcess5_ACD.'
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
NorieVBA ExpertCommented:
Anders

I was assuming Sarith was referring to stored macros.:)
2
 
Sarith GadaData AnalystAuthor Commented:
Thank you! Worked perfectly well!
0
 
Gustav BrockCIOCommented:
Try with:

strMacro = "UpdateTblProcess" & i & "_ACD()"
Eval(strMacro)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.