Macros to email reports breaking with other users

[gump]

I created a database that has 8 different macros to email a report in
snapshot format when a user has completed filling out a form and clicks a
button. The database is saved in a shared folder on our network that all
users have rights to. Everything works fine from my computer, but when
another user tries it, the first couple reports email no problem, then
nothing happens when the “Email†button is clicked and all macros stop
working. It is never the same macro that stops things from working, and no
order that I can figure out. The reports can still be run from the database
window but the email macros sometimes give an “Action Failed†window, others
a “Reserved Error†message box, but no error number is given. Please help!

 

[a a r o n _ k e m p f]

you don't need macros, and you don't need to email snapshots.

you need Reporting Services-- it does all of this without needing to
write any code.
Or if you're really stuck using an obsolete engine-- pretend for a
second that you don't have the mental capacity to learn SQL Server..
then you could look at www.rptsoftware.com

Sending emails from each desktop is a pain-- because it is a security
risk. BY DEFINITION.
Send them from a secure server instead.

For small simple queries; I just use XP_SENDMAIL most of the time

 

[gump]

This wasn't really the answer I was looking for...we've done it this way with
other simple databases (Access 2000) without issue, the system is already
built and in use, and the emails will all be sent internally only. I will
check ou the website for Reporting Services, but if anyone has any other
suggestions I'd love to hear them.

 

[Arvin Meyer [MVP]]

Perhaps it would be better to run this from VBA code so that you could do
the appropriate error handling. If you cannot write the code, perhaps
listing the macros with their actions, conditions, and Action Arguments, and
one of us might help.

 

[gump]

The only Action is to SendObject, which is a report, in Snapshot format,
based on a query that looks at the number on the form currently displayed as
the only criteria. The button was added to the form using the Wizard and
the OnClick code is run the macro called "SendResolvedNCR" (or others
depending on which form you are in). Only additional line added is to make
sure any changes added through the form are saved prior to the query being
run (see below). I'm not a code writer but can understand what is going on
if given an example. The query runs fine, report looks fine, but the macros
stop working after running 2 or 3 times on another users computer.

Private Sub EmailResNCR_Click()
On Error GoTo Err_EmailResNCR_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim stDocName As String

stDocName = "SendResolvedNCR"
DoCmd.RunMacro stDocName

Exit_EmailResNCR_Click:
Exit Sub

Err_EmailResNCR_Click:
MsgBox Err.Description
Resume Exit_EmailResNCR_Click

End Sub

 

[gump]

I forgot to mention that if the user closes the database and reopens it they
are able to email a few more times before it breaks again. Seems like I need
some line to refresh / clear "something" before the next macro is run.

 

[DENIS]

This wasn't really the answer I was looking for...we've done it this way
with
other simple databases (Access 2000) without issue, the system is already
built and in use, and the emails will all be sent internally only. I will
check ou the website for Reporting Services, but if anyone has any other
suggestions I'd love to hear them.

 

[gump]

Arvin,
I converted my macros to modules, added a line in the module to save the
record, then use a macro to call the module. After about 4 uses either
nothing happens or a "reserved error" might appear. I added a msgbox to
come up on error within the module but I can't get to that point from
clicking the button, but if I run the macro to call the module from the
background I get to my error message - but still cannot email. Maybe I
shouldn't be using a macro to run the module but I don't know how else to do
it, or even if this will solve the issue. Please help.

 

[a a r o n _ k e m p f]

trying to enable programmatic email on 20 different machines is
literally 20 times more complex than trying to enable programmatic
email on 1 server.

you really should pick up a book on client-server; it is a much much
better architecture than client-nothing