Add email VBA to existing code

I will have to try tomorrow morning when im on the PC running XP... I will
get back to you in the morning. Thnaks!
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

Start by adding standard error handling.

At the top of the module (right after the "Private Sub Form_Open()"), put

On Error GoTo Err_Form_Open

At the end of the module (right before the "End Sub"), put

End_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & ": " & Err.Description
Resume End_Form_Open

Now when you run the code, you'll get a message box with a number, followed
by a colon, followed by your error message. Once you know the error number,
change the last bit of code to

End_Form_Open:
Exit Sub

Err_Form_Open:
Select Case Err.Number
Case xxx ' Whatever error number you got above
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_Form_Open
End Select

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
...
how would I find the number? Thanks!
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

You'll have to determine the error number, and put in error handling that
ignores that specific error.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
news Oh! I didnt see that... Well it works great except for it I deside not
to
send the email and close the email box I get an error and cant close
the
database

error:
The SendObject action was canceled.
You used a method of the DoCmd object to carry out an action in VB, but
hen
clicked cancel in a dialog box.
For example, you used the Close method to close a changed form, then
clicked
Cancel in the dialog box that asks if you want to save the changes you
made
to the form.


--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

It looks as though you missed Arvin's instruction

' add this
Dim strTo As String
Dim strSubject As String
Dim strMessage As String


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
...
Thanks for the reply! I get a compile error Variable not Defined and
it
highlights strTo. Here is the complete code im using if that helps.
Maybe
I
put it in the wrong place..Thanks!

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_LoginID", dbOpenDynaset)

If Not IsNull(Me.Text_Network_User) Then
rst.FindFirst "strEmployeeUserID = '" & Me.Text_Network_User
&
"'"
&
" And strEmployeeUserID = '" & Me.Text_Network_User & "'"

If rst.NoMatch Then
MsgBox "You do not have access to this database!!! " &
Chr(13)
& _
"Please contact the Database Adminstrator for
assistance.",
vbOKOnly + vbCritical, "Logon Denied"

strTo = "
strSubject = "Cant open database"
Str Message = "The body of the message"

DoCmd.SendObject acSendNoObject, , , strTo, , ,
strSubject,
strMessage
DoCmd.Quit

Else

stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End If

End Sub
--
Newbies need extra loven.........


"Arvin Meyer [MVP]" wrote:


"Chad" wrote in message
...
Hello, I am running this code in my WelcomeForm On Open event and
when
a
person is denyed access to the database and before OR after the
database
closes I would like a outlook email window to come up so they can
email
me.
what and where would I add the code to the code below? Thanks!

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String

' add this
Dim strTo As String
Dim strSubject As String
Dim strMessage As String


Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_LoginID", dbOpenDynaset)

If Not IsNull(Me.Text_Network_User) Then
rst.FindFirst "strEmployeeUserID = '" &
Me.Text_Network_User
&
"'"
&
" And strEmployeeUserID = '" & Me.Text_Network_User & "'"

If rst.NoMatch Then
MsgBox "You do not have access to this database!!! " &
Chr(13)
& _
"Please contact the Database Adminstrator for
assistance.",
vbOKOnly + vbCritical, "Logon Denied"

'add the code he

strTo= "
strSubject = "The subject you want"
str Message = "The body of the message"

DoCmd.SendObject acSendNoObject, , , strTo, , , strSubject,
strMessage


DoCmd.Quit

Else

stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End If

End Sub

--
Newbies need extra loven.........

Ok I used the error handler and got a number 2501 so I added it in the error
handler. I opened the database and got the message “Can’t enter blab bla bla�
and an email opened up “As expected� I didn’t send the email but instead I
close it and I get these errors:

SendObject action was canceled.
You used a method of the DoCmd object to carry out an action in VB, but then
clicked cancel in a dialog box.

I then click OK in the popup and another error comes up.

You cant exit “Database Name� now.
If your running a VB code module that is using OLE or DDE you may need to
interrupt the module.

I click OK then the database opens “Which its not supposed to�. I try to
close it and the last error message pops up. I have to end task to get out of
the database. What can I do to fix this problem? Here is the code I’m using.
Thanks!

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String
Dim strTo As String
Dim strSubject As String


Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_LoginID", dbOpenDynaset)

If Not IsNull(Me.Text_Network_User) Then
rst.FindFirst "strEmployeeUserID = '" & Me.Text_Network_User & "'" &
" And strEmployeeUserID = '" & Me.Text_Network_User & "'"

If rst.NoMatch Then
MsgBox "You do not have access to this database!!! " & Chr(13) & _
"Please contact the Database Adminstrator for assistance.",
vbOKOnly + vbCritical, "Logon Denied"

strTo = "
strSubject = "Access Database Denied"
DoCmd.SendObject acSendNoObject, , , strTo, , , strSubject


DoCmd.Quit

Else

stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End If

End_Form_Open:
Exit Sub

Err_Form_Open:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_Form_Open
End Select

End Sub


--
Newbies need extra loven.........

I hadn't notice the DoCmd.Quit there before. Try replacing that with

Cancel = True

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
...
Ok I used the error handler and got a number 2501 so I added it in the
error
handler. I opened the database and got the message "Can't enter blab bla
bla"
and an email opened up "As expected" I didn't send the email but instead I
close it and I get these errors:

SendObject action was canceled.
You used a method of the DoCmd object to carry out an action in VB, but
then
clicked cancel in a dialog box.

I then click OK in the popup and another error comes up.

You cant exit "Database Name" now.
If your running a VB code module that is using OLE or DDE you may need to
interrupt the module.

I click OK then the database opens "Which its not supposed to". I try to
close it and the last error message pops up. I have to end task to get out
of
the database. What can I do to fix this problem? Here is the code I'm
using.
Thanks!

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String
Dim strTo As String
Dim strSubject As String


Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_LoginID", dbOpenDynaset)

If Not IsNull(Me.Text_Network_User) Then
rst.FindFirst "strEmployeeUserID = '" & Me.Text_Network_User & "'"
&
" And strEmployeeUserID = '" & Me.Text_Network_User & "'"

If rst.NoMatch Then
MsgBox "You do not have access to this database!!! " & Chr(13)
& _
"Please contact the Database Adminstrator for assistance.",
vbOKOnly + vbCritical, "Logon Denied"

strTo = "
strSubject = "Access Database Denied"
DoCmd.SendObject acSendNoObject, , , strTo, , , strSubject


DoCmd.Quit

Else

stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End If

End_Form_Open:
Exit Sub

Err_Form_Open:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_Form_Open
End Select

End Sub


--
Newbies need extra loven.........

It didnt work. I get the same errors but the database wont open. Thats how
its suposed to work. before I added the Cancel =True it opened the
databese... Any other sugestions?
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

I hadn't notice the DoCmd.Quit there before. Try replacing that with

Cancel = True

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
...
Ok I used the error handler and got a number 2501 so I added it in the
error
handler. I opened the database and got the message "Can't enter blab bla
bla"
and an email opened up "As expected" I didn't send the email but instead I
close it and I get these errors:

SendObject action was canceled.
You used a method of the DoCmd object to carry out an action in VB, but
then
clicked cancel in a dialog box.

I then click OK in the popup and another error comes up.

You cant exit "Database Name" now.
If your running a VB code module that is using OLE or DDE you may need to
interrupt the module.

I click OK then the database opens "Which its not supposed to". I try to
close it and the last error message pops up. I have to end task to get out
of
the database. What can I do to fix this problem? Here is the code I'm
using.
Thanks!

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String
Dim strTo As String
Dim strSubject As String


Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_LoginID", dbOpenDynaset)

If Not IsNull(Me.Text_Network_User) Then
rst.FindFirst "strEmployeeUserID = '" & Me.Text_Network_User & "'"
&
" And strEmployeeUserID = '" & Me.Text_Network_User & "'"

If rst.NoMatch Then
MsgBox "You do not have access to this database!!! " & Chr(13)
& _
"Please contact the Database Adminstrator for assistance.",
vbOKOnly + vbCritical, "Logon Denied"

strTo = "
strSubject = "Access Database Denied"
DoCmd.SendObject acSendNoObject, , , strTo, , , strSubject


DoCmd.Quit

Else

stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End If

End_Form_Open:
Exit Sub

Err_Form_Open:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_Form_Open
End Select

End Sub


--
Newbies need extra loven.........

What do you mean by "it opened the database"?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
...
It didnt work. I get the same errors but the database wont open. Thats how
its suposed to work. before I added the Cancel =True it opened the
databese... Any other sugestions?
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

I hadn't notice the DoCmd.Quit there before. Try replacing that with

Cancel = True

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
...
Ok I used the error handler and got a number 2501 so I added it in the
error
handler. I opened the database and got the message "Can't enter blab
bla
bla"
and an email opened up "As expected" I didn't send the email but
instead I
close it and I get these errors:

SendObject action was canceled.
You used a method of the DoCmd object to carry out an action in VB, but
then
clicked cancel in a dialog box.

I then click OK in the popup and another error comes up.

You cant exit "Database Name" now.
If your running a VB code module that is using OLE or DDE you may need
to
interrupt the module.

I click OK then the database opens "Which its not supposed to". I try
to
close it and the last error message pops up. I have to end task to get
out
of
the database. What can I do to fix this problem? Here is the code I'm
using.
Thanks!

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String
Dim strTo As String
Dim strSubject As String


Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_LoginID", dbOpenDynaset)

If Not IsNull(Me.Text_Network_User) Then
rst.FindFirst "strEmployeeUserID = '" & Me.Text_Network_User &
"'"
&
" And strEmployeeUserID = '" & Me.Text_Network_User & "'"

If rst.NoMatch Then
MsgBox "You do not have access to this database!!! " &
Chr(13)
& _
"Please contact the Database Adminstrator for assistance.",
vbOKOnly + vbCritical, "Logon Denied"

strTo = "
strSubject = "Access Database Denied"
DoCmd.SendObject acSendNoObject, , , strTo, , , strSubject


DoCmd.Quit

Else

stDocName = "WelcomeForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End If

End_Form_Open:
Exit Sub

Err_Form_Open:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_Form_Open
End Select

End Sub


--
Newbies need extra loven.........

When I had the DoCmd it opened the database after the error prompts. Then it
wouldnt let me close the database it kept saying:
You cant exit “Database Name� now.
If your running a VB code module that is using OLE or DDE you may need to
interrupt the module.
Now that you had me take the DoCmd out and add Current = True I still get
the same errors and cant close the database but the database never opens my
start up "WelcomeForm" I still have to Ctl+Alt+Delete to end task to get out.
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

What do you mean by "it opened the database"?

Douglas, Is there a way I could just not have a close button on the email
prompt that comes up? This way they are forced to send the email... Also is
there a way I can add who tried to open the database by getting thier Login
name from the text box Text_Network_User. Thanks for all your help on this!
--
Newbies need extra loven.........


"Chad" wrote:

When I had the DoCmd it opened the database after the error prompts. Then it
wouldnt let me close the database it kept saying:
You cant exit “Database Name� now.
If your running a VB code module that is using OLE or DDE you may need to
interrupt the module.
Now that you had me take the DoCmd out and add Current = True I still get
the same errors and cant close the database but the database never opens my
start up "WelcomeForm" I still have to Ctl+Alt+Delete to end task to get out.
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

What do you mean by "it opened the database"?

It's possible that Tony Toews may have something in the Access EMail FAQ at
http://www.granite.ab.ca/access/email.htm

However, why bother sending the e-mail? Just log it in the database, and
have the administrator review the logs.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
...
Douglas, Is there a way I could just not have a close button on the email
prompt that comes up? This way they are forced to send the email... Also
is
there a way I can add who tried to open the database by getting thier
Login
name from the text box Text_Network_User. Thanks for all your help on
this!
--
Newbies need extra loven.........


"Chad" wrote:

When I had the DoCmd it opened the database after the error prompts. Then
it
wouldnt let me close the database it kept saying:
You cant exit "Database Name" now.
If your running a VB code module that is using OLE or DDE you may need to
interrupt the module.
Now that you had me take the DoCmd out and add Current = True I still get
the same errors and cant close the database but the database never opens
my
start up "WelcomeForm" I still have to Ctl+Alt+Delete to end task to get
out.
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

What do you mean by "it opened the database"?

How could I log who was denied access to the database? The only way it shows
who is logging in is by using the Text_Network_User text box.
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

It's possible that Tony Toews may have something in the Access EMail FAQ at
http://www.granite.ab.ca/access/email.htm

However, why bother sending the e-mail? Just log it in the database, and
have the administrator review the logs.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
...
Douglas, Is there a way I could just not have a close button on the email
prompt that comes up? This way they are forced to send the email... Also
is
there a way I can add who tried to open the database by getting thier
Login
name from the text box Text_Network_User. Thanks for all your help on
this!
--
Newbies need extra loven.........


"Chad" wrote:

When I had the DoCmd it opened the database after the error prompts. Then
it
wouldnt let me close the database it kept saying:
You cant exit "Database Name" now.
If your running a VB code module that is using OLE or DDE you may need to
interrupt the module.
Now that you had me take the DoCmd out and add Current = True I still get
the same errors and cant close the database but the database never opens
my
start up "WelcomeForm" I still have to Ctl+Alt+Delete to end task to get
out.
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

What do you mean by "it opened the database"?

You'll only sending the e-mail if something was put in text box and whatever
is in the box isn't in the tbl_LoginID. Isn't what's in Text_Network_User
what you'd log?

Incidentally, I don't know whether you're making them key their info into
Text_Network_User or if you're prefilling that control. If you aren't
prefilling the control for them, take a look at
http://www.mvps.org/access/api/api0008.htm at "The Access Web" for code to
retrieve the current user's Network ID.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
...
How could I log who was denied access to the database? The only way it
shows
who is logging in is by using the Text_Network_User text box.
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

It's possible that Tony Toews may have something in the Access EMail FAQ
at
http://www.granite.ab.ca/access/email.htm

However, why bother sending the e-mail? Just log it in the database, and
have the administrator review the logs.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Chad" wrote in message
...
Douglas, Is there a way I could just not have a close button on the
email
prompt that comes up? This way they are forced to send the email...
Also
is
there a way I can add who tried to open the database by getting thier
Login
name from the text box Text_Network_User. Thanks for all your help on
this!
--
Newbies need extra loven.........


"Chad" wrote:

When I had the DoCmd it opened the database after the error prompts.
Then
it
wouldnt let me close the database it kept saying:
You cant exit "Database Name" now.
If your running a VB code module that is using OLE or DDE you may need
to
interrupt the module.
Now that you had me take the DoCmd out and add Current = True I still
get
the same errors and cant close the database but the database never
opens
my
start up "WelcomeForm" I still have to Ctl+Alt+Delete to end task to
get
out.
--
Newbies need extra loven.........


"Douglas J. Steele" wrote:

What do you mean by "it opened the database"?