Find and insert e-mail from other tables

[mafhobb]

The attached database has an "After Update" event on the "Project History" field that sends an automatic e-mail out when it is updated.

Each project has a history, a project name, and a PM person associated to it, as shown in the db.

Currently, the e-mail recipient is specified in the code. How do I make it so that the AfterUpdate event figures out who the PM associated with the project is (which is in the "Main" table), his e-mail (which is in the (e-mail table), an then it e-mails that person?

This db is a sample of a much larger one that is in use, so I really cannot change the layout of the tables...

Thanks to anyone who helps!

mafhobb

 

[John Big Booty]

When and how is the email event being fired?

 

[mafhobb]

Oh, It is an afterupdate event on the Project History field in the History form.

 

[John Big Booty]

Firstly, you need to have a look at naming conventions. Avoid using spaces minus sings and other special characters in object names. As these complicate the process of writing code where those names are used.

Also consider using identifying prefixes for objects ie. FRM for forms, TBL for tables QRY for queries etc. This makes it easier to identify what you are referring to when you read the code.

I've made the changes to your sample DB.

Now to answer your question if you replace the email address you currently have in the .To = in your code with;

SELECT TBL_Email.PM_Name, TBL_Email.e_mail FROM TBL_Email WHERE (((TBL_Email.PM_Name)=[Forms]![frm_main]![pm_name]));

That should identify the email address associated with the appropriate PM for that Project.

 

[mafhobb]

All right, I have modified my code and it seems like most of this is working, except that when using the code

email = SELECT TBL_Email.PM_Name, TBL_Email.e_mail FROM TBL_Email WHERE (((TBL_Email.PM_Name)=[Forms]![frm_main]![pm_name]));

email actually ends up having a value of "SELECT TBL_Email.PM_Name, TBL_Email.e_mail FROM TBL_Email WHERE (((TBL_Email.PM_Name)=[Forms]![frm_main]![pm_name]));" instead of email it is supposed to find....

What's up? I feel that this is a small typo-type error? is it?

mafhobb

 

[pbaldy]

John appears to be offline. I only have 2000 here so can't open John's file, but presumably the SQL statement was designed to be used with a recordset. You can't just use SQL anywhere to get a value. As an alternative to the recordset, you could also use a DLookup to get the address, using a similar criteria. More on the syntax for that here:

http://www.mvps.org/access/general/gen0018.htm

 

[jwhite]

Paul's answer will do perfectly fine. Not sure what JBB intended, but Paul is right again with the recordset theory. So that you can compare the simplicity of DLookup but be able to use a RecordSet if you need to iterate through a record collection, here is the code. It should be placed after the "Const..." line, but work-in appropriately for code readibility. I've noted what your ".To=..." line should be as well, and included the Web Reference so you can read more on SQL and Recordsets. I'm assuming, of course, that your form reference is correct.

'*************************************************************************************
'http://www.fmsinc.com/tpapers/queries/index.html#SQLstring

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strEmailAddress As String
    
    strSQL = "SELECT PM_Name, e_mail FROM TBL_Email WHERE PM_Name)='" & Forms!frm_main!pm_name & "';"
    
    ' Open pointer to current database
    Set dbs = CurrentDb()
    
    ' Create recordset based on SQL
    Set rst = dbs.OpenRecordset(strSQL)
    
    If Not rst.EOF And Not rst.BOF Then
        'Code Assumes only 1 match will be found.
        .MoveFirst
        strEmailAddress = rst!pm_name & " <" & rst!e_mail & ">"
    Else
        strEmailAddress = ""
    End If
    
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing

    'Later in your code:  .To = strEmailAddress
'*************************************************************************************

 

[mafhobb]

All right, thanks to your help I think I am very close to the desired results.

After adapting your code to my table names, this is the line I end up with:

strSQL = "SELECT Name, Work e-mail FROM Product Managers WHERE Name)='" & [Forms]![Project Main]![Product Manager] & "';"

"Product Managers" is the table where the e-mails are stored. The e-mail field is "work e-mail" and the product manager name field is "Name"

This code is an after update even on the field "Comment" which is in the "Project History" form. This is a subform of "Project Main" where the product manager name field is "Product Manager"

When I run the code, I get the error shown in the attached image. This tells me that the 2nd part of the line: [Forms]![Project Main]![Product Manager] is working, however, the first part does not. What is wrong?

Also, through all your comments, I realize of the importance of the naming conventions now. I am seriously thinking on creating a "V2" version of the database at this point, however, that will take months. If you could be so patient to throw me one final help line on this, it would be greatly appreciated.

mafhobb

 

[pbaldy]

You have a stray parentheses in there, plus the inadvisable spaces and symbols will require your field and table names to be bracketed:

[Work e-mail]

 

[mafhobb]

Ok, using this:

strSQL = "SELECT [Name], [Work e-mail] FROM [Product Managers] WHERE Name='" & [Forms]![Project Main]![Product Manager] & "';"

MsgBox strSQL

I still get the image below for strSQL

 

[mafhobb]

All right, some tweaking, and it is much closer. This is the code now:

strSQL = "SELECT [Work e-mail] FROM [Product Managers] WHERE Name='" & [Forms]![Project Main]![Product Manager] & "';"
MsgBox strSQL


' Open pointer to current database
Set dbs = CurrentDb()

' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL)

If Not rst.EOF And Not rst.BOF Then
'Code Assumes only 1 match will be found.
'.MoveFirst
strEmailAddress = rst![work e-mail]
Else
strEmailAddress = ""
End If

MsgBox strEmailAddress

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

It does not give me any error, and it picks up the correct e-mail but the value of the stremailaddress is: "cindy@gmail.com#http://cindy@gmail.com#"

The value of the string at that point (and what is stored in the table) should be: "cindy@gmail.com"

Where does the additional text get added?

 

[mafhobb]

Is it possibly because the e-mail field is a hyperlink?

 

[pbaldy]

Funny, I was about to ask if it was. That may be the cause; I never use them.

 

[mafhobb]

All right, It works! It was the hyperlink field that was causing the problem.

Now the only thing I need to figure out is what happens if there is no e-mail in the table. Defaulting on a specific e-mail is what I am shooting for...

 

[pbaldy]

This would be the spot:

...
Else
strEmailAddress = "DefaultAddressHere"
...

 

[mafhobb]

That is what I thought,

If I do this, I get "invalid use of null" error (error 94)

 

[pbaldy]

What is the full code now, and what line throws the error?

 

[mafhobb]

Here is the code:

Option Compare Database


Private Sub Form_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strEmailAddress As String
Dim mail
Dim TestInput As Variant
Dim PMName As Variant
Dim ProjectName As Variant
Dim email As String

'find the comment that is getting updated to send it
Me.Comment.SetFocus
TestInput = Me.Comment.Text

'Find the PM whose project this is
[Forms]![Project Main]![Product Manager].SetFocus
PMName = [Forms]![Project Main]![Product Manager].Text
'MsgBox PMName ' This would show a box with the PM Name

'Find the Project Name this e-mail refers to
[Forms]![Project Main]![Project Name].SetFocus
ProjectName = [Forms]![Project Main]![Project Name].Text

'Find the e-mail address of that PM
strSQL = "SELECT [Work e-mail] FROM [Product Managers] WHERE Name='" & [Forms]![Project Main]![Product Manager] & "';"
' MsgBox strSQL ' This would show a box with the code to find the PM

' Open pointer to current database
Set dbs = CurrentDb()

' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL)

If Not rst.EOF And Not rst.BOF Then
'Code Assumes only 1 match will be found.
'.MoveFirst
strEmailAddress = rst![work e-mail]
Else
strEmailAddress = "mfrontera@gmail.com"
End If

' MsgBox strEmailAddress 'This whould show the e-mail address the e-mail is being sent to

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

Set mail = Nothing

' Send by connecting to port 25 of the SMTP server.
Dim iMsg
Dim iConf
Dim Flds
Dim strHTML
Dim strEmailTo

Const cdoSendUsingPort = 2

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

Set Flds = iConf.Fields

' Set the CDOSYS configuration fields to use port 25 on the SMTP server.

With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
'ToDo: Enter name or IP address of remote SMTP server.
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.1.10.63"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
.Update
End With

' Build HTML for message body.
strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<b> </b></br>"
strHTML = strHTML & "</BODY>"
strHTML = strHTML & "</HTML>"

' Apply the settings to the message.
With iMsg
Set .Configuration = iConf
.To = strEmailAddress
.From = "Airplane R&D Database" 'MailFrom 'this should be OK
.Subject = ProjectName
.HTMLBody = TestInput 'this should be OK
.Send 'this should be OK
End With

' Clean up variables.
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing

MsgBox "Mail Sent!"

End Sub

The error line is : strEmailAddress = rst![work e-mail]

 

[pbaldy]

Ahh, I was thinking of when the recordset didn't return a record. You mean the email field is empty. Try

If Nz(rst![work e-mail], "") = "" Then
  strEmailAddress = "mfrontera@gmail.com"
Else
  strEmailAddress = rst![work e-mail]
End If

 

[mafhobb]

That does it!

Thank you!

mafhobb