Return macro name from a table in VBA

[Buckmaster]

I am builing a reporting database where I have a form(Report Dashboard) that has an unbound control where the control source is a table (Reports) and reflects the report name from the table. The table has the report name and another field called Trigger that has the name of the macro to run. When user selects the Report Name from the unbound control (ie on click) I am trying to build a VBA code that runs the macro in the field Trigger from the Reports table. Here is what I have cobbled together but I get an compile error stating "object required" on my set command for strMacro.

Private Sub lstReports_Click()
Dim myDb As DAO.Database
Dim strMacro As String

Set myDb = CurrentDb()
Set strMacro = myDb.OpenRecordset("SELECT Trigger FROM Reports")


DoCmd.RunMacro "strMacro"

On Error GoTo strTrigger_Error


strTrigger_Done:
Exit Sub

strTrigger_Error:
MsgBox "An error has occured - no associated macro."
Resume strTrigger_Done

End Sub

 

[boblarson]

Take off the word SET for setting a value to a string variable. It isn't needed.

 

[boblarson]

And

 

[Buckmaster]

When I remove the "set" I now get a type mismatch error on strMacro. The field Trigger is a text data type. Not sure why it still isn't working? Thanks

 

[c_smithwick]

That's because:

myDb.OpenRecordset("SELECT Trigger FROM Reports")

is returning a recordset, not a string. Try using a DLookup function instead.

 

[Buckmaster]

I think I am gettting closer but it still isn't working. The control on my form (Report Dashboard) is a list box - would that be causing this not to work? Here is my revised code:

Private Sub lstReports_Click()
Dim myDb As DAO.Database
Dim strMacro As String

Set myDb = CurrentDb()
strMacro = DLookup("[Trigger]", "Reports", "Name = " & Forms![Report Dashboard]!lstReports)


DoCmd.RunMacro "strMacro"

On Error GoTo strTrigger_Error


strTrigger_Done:
Exit Sub

strTrigger_Error:
MsgBox "An error has occured - no associated macro."
Resume strTrigger_Done

End Sub


This returns the following error:
Run-time error '3075':
Syntax error (missing operator) in query expression 'Name = Transfer Notes'.

Transfer Notes is the Name field in the table where I am trying to return the Trigger value which has the macro name.

 

[boblarson]

I believe you are also struggling with using one of the worst names for a field or object - NAME. That is an Access Reserved Word and it is a real pain using that one as everything in Access has a .Name property. So, I would suggest using a different name for that field in the table and then modifying the code appropriately.

If you insist on using it, then you have to enclose it in square brackets AND because it is text you need delimiters.

strMacro = DLookup("[Trigger]", "Reports", "[Name]= " & Chr(34) & Forms![Report Dashboard]!lstReports & Chr(34))

 

[Buckmaster]

Well... I did both - changed the name of the field and added the delimiters:
strMacro = DLookup("[Trigger]", "Reports", "[ReportName] = " & Chr(34) & Forms![Report Dashboard]!lstReports & Chr(34))

Now the compile error is that it cannot find the object 'strMacro' on the part DoCmd.RunMacro "strMacro".

 

[Buckmaster]

I fixed it! I removed the quotes around strMacro and it works - new version has...

DoCmd.RunMacro strMacro

Thanks for all your help.