Create an Access Macro using Code

[JONBOY74]

Hi All

What I have is a table with a list of Function, what I'm trying to do is loop through the table an CALL each Function in turn if it is marked as "Active".

My idea was instead of Function names, have Macro names, these macros would contain the Function I want to run. I could then pass the Macro name into the run code & use docmd.run macro.

So my questions are....
is there a better way to do the above?
and
is it possible to build an Access Macro using VB?

Hope this makes sense

Thanks

Jon

 

[dyarwood]

You can write code in a VBA module and then in the macro screen asign a macro to run the code. Create this macro an in the code there you can call the other functions from the module. No need for loads of macros. Not sure if that helps at all but let me know.

 

[mrf1xa]

Assuming your table contains the names of Public of Global functions, I swould just loop through the table in code, check for the Active status and call the function from there.

Top of head air code:

Dim proc As String
Dim rst As DAO.recordset
Set rst = ("Select * From yourtable&quot

If rst.RecordCount > 0 Then
Do Unitl EOF(rst)
If rst.Status = "Active" Then
proc = rst.procedurename
Call proc
proc = ""
End If
rst.Movenext
Loop
End If

rst.Close
Set rst = Nothing

Will need tweaking but should give a start point....



Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[JONBOY74]

Nigel

I to get a "Compile error" when I try this, any thoughts

 

[mrf1xa]

Hi, I did say it would need tweaking! Couple of obvious errors, so try this

Dim proc As String
Dim rst As DAO.recordset
Set rst = CurrentDb.OpenRecordset("Select * From yourtable&quot

If rst.RecordCount > 0 Then
Do Unitl EOF(rst)
If rst![Status] = "Active" Then
proc = rst![procedurename]
Call proc
proc = ""
End If
rst.Movenext
Loop
End If

rst.Close
Set rst = Nothing

Can't see anything else obvious as long as you've changed names etc. If it still fails could you post the code as you have it and any error message

Cheers


Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....