Question Open Macro in Design View Command Button

[Clownfish1980m]

I have created a database that uses macros to send reports out to specific locations. The issues I have is I have the database extension as .accdr with all short cut keys disabled to a point it prevents users from tampering with the programming and the data. What I need to do is create a button on the administrator’s page that will take the admin user to the macros section to allow them to update email addresses for reports as people come and go. Thanks Ken

 

[Pat Hartman]

The users should not be interacting directly with macros, tables, or queries. Create a form and add buttons to run the macros. Or, if you are using A2007 or earlier, you can use the switchboard wizard to create a switchboard. Or, with A2010, you can use its new menu feature or just import the Switchboard form and Switchboard Items tables from an old application but you'll have to update the Switchboard Items table manually since the wizard is gone.

 

[Clownfish1980m]

The users don't have access but the adminstrator (myself) does. What I need to do do is somehow have the ability to update the email addresses I have assigned to each macro. Running them is not problem as I have the reports being run from swhitchboard. I just need a way to update the email addresses once I convert it to an .accdr which eliminates the shortcuts and hides all the back ground forms, reprots etc. I tried a command button on a form but have no way to open the macros module to allow me to select the macro to update. I am researching on how to create a POC table containing the report location, name and email address but have not found a way change the SendObject TO field to look up the email addresses based on location. The report goes to several recipients at the same location. Ken

 

[Pat Hartman]

Changes to the database need to be made to the .accdb. Then generate the .accde and rename it .accdr.

If these addresses need to be maintained by folks other than the developer, then they need to be stored in a table and a form to maintain them needs to be created. The macros need to get the email value using the macro equivalent of DLookup().

 

[Clownfish1980m]

Thanks. I will research the DLookup() and see what I can create. Ken

 

[Clownfish1980m]

I created a table called POCtbl which contains the [Location], [POC Name], and fields. I also created a POCqry. Using DLookup I used the following code in the “To” field of the macro. Each macro sends a specific report to that location and a location may have more than one POC. [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]=DLookup (“[Email]”,”POCqry”)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]It sends out only one message to the first address in the table. What I need to figure out now is how to get multiple email addressed on the “To” line and how have it filter out only one location. Is this possible? I tried to set the criteria as “[Location] = “BAMC” but it doesn’t like that. Ken[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]

 

[Pat Hartman]

How was it sending out multiple emails previously?

If you need to modify macros, you're going to have to ask in the macro forum. If you want to switch to VBA, we can help you here.

 

[Clownfish1980m]

I created a macro using SendObject. I entered every email address. I built a button under switchboard that activated the macro to send the reports. I also made each report cancel on no data so blank reports would not go out. This has worked but I am trying to build this for other users in the future when I am no longer here. Ken

 

[Clownfish1980m]

I have now gotten half way to my goal. I have created a form with a list box that shows all the Macros available using the following code:

SELECT [Name] FROM MsysObjects
WHERE (([Type] = -32766) AND ([Name] Not Like "~*") AND ([Name] Not Like "MSys*"))
ORDER BY [Name];

Now I am working on a command button to open the macro in design view which will allow me to edit the email addresses in that macro. Any ideas how to do that? Ken

 

[spikepl]

You are in the process of expending a lot of energy on a quick-and-dirty solution that might bite your successor, because you are reopening the design elements of your DB and hence you have no control over what anybody types. A moment of distraction and a typo can then make it unrunnable, and your successor may or may not be able to recover.

Don't make any procedures relying on modifying macros. Make them read appropriate data - ask in the macro forum. Or write the proper VBA to do what you want, where the email addresses are in a table, where you can control and check the input via a form. There are thousands of threads about emailing dedicated reports to a list of recipients using VBA.

Besides, the MS* tables are not guaranteed to remain unchanged, so any service pack or upgrade could kill your procedure

 

[Clownfish1980m]

You are probably correct. I am trying to research how to create the email address table and how to get it to loop through the table and populate a macro with the addresses and send the report. DLookUp seemed to work but only captures the first name in the table and proceed to the second name with the same location. Not having much success with this. It may come down to just saying you have to have an administrator (could be a civilian job for me).