Send an Email with parameters from two different tables

[clloyd]

I am trying to generate an email that is gathering information from two different tables. I keep getting an error message that it can not find my CustomerNo. Does anyone have any ideas?

 
Private Sub cmdEmail_Click()
    Dim CustomerNo As Parameter
    Dim ClientID As Parameter
 
On Error GoTo Err_Email_Click
 
    stEmailTo = DLookup("[CustomerMailingsEMail]", "[Customer]", "[CustomerNo] = '" & Me![ CustomerNo] & ";" & " " _
    And DLookup("[ReportMailingCC]", "[Clients]", "[ClientID] = '" & Me![ClientID]) & "'")
    DoCmd.SendObject acReport, stDocName, acFormatRTF, stEmailTo, , , _
    "Orders" & "," & " " & DLookup("[Customer Name]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'") & "," & " " & _
    DLookup("[Physical City]", "[Customer]", "[ CustomerNo] = '" & Me![ Customer No] & "'") & "," & " " & _
    DLookup("[Physical State]", "[ Customer]", "[ CustomerNo] = '" & Me![ CustomerNo] & "'") & "," & " " & _
    DLookup("[Physical Country]", "[ Customer]", "[ CustomerNo] = '" & Me![ CustomerNo] & "'") & " " & " CustomerNo" & " " & Me![ CustomerNo], _
    DLookup("[ReportMailingInstructions]", "[Clients]", "[ClientID] = '" & Me![ClientID]) _
    & vbCr & DLookup("[ReportMailingEmailText]", "[Clients]", "[ClientID] = '" & Me![ClientID])
 
 
Exit_Email_Click:
    Exit Sub
 
Err_Email_Click:
    MsgBox Err.Description
    Resume Exit_Email_Click
 
End Sub
 
 

[Stewart Ross]

If you look at lines 7 onwards in your post you will see that the field and control names vary. Many have spaces at the start of the name (e.g. [ Customer] when [Customer] would be expected) and some change from no space between words [CustomerNo] to one space
[Customer No].

I can't tell which ones are correct - but some of them cannot be right

-Stewart

[clloyd]

Thanks. I fixed those errors but it still doesn't find the Customer Number. I believe it is in the code calling on the two different tables for all the information needed. Here is my revised code.

 
Private Sub cmdEmail_Click()
Dim CustomerNo As Parameter
Dim ClientID As Parameter
 
On Error GoTo Err_Email_Click
 
stEmailTo = DLookup("[CustomerMailingsEMail]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & ";" & " " _
And DLookup("[ReportMailingCC]", "[Clients]", "[ClientID] = '" & Me![ClientID]) & "'")
DoCmd.SendObject acReport, stDocName, acFormatRTF, stEmailTo, , , _
"Orders" & "," & " " & DLookup("[CustomerName]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'") & "," & " " & _
DLookup("[Physical City]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'") & "," & " " & _
DLookup("[Physical State]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'") & "," & " " & _
DLookup("[Physical Country]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'") & " " & " CustomerNo" & " " & Me![CustomerNo], _
DLookup("[ReportMailingInstructions]", "[Clients]", "[ClientID] = '" & Me![ClientID]) _
& vbCr & DLookup("[ReportMailingEmailText]", "[Clients]", "[ClientID] = '" & Me![ClientID])
 
 
Exit_Email_Click:
Exit Sub
 
Err_Email_Click:
MsgBox Err.Description
Resume Exit_Email_Click
 
End Sub
 
 

[Stewart Ross]

Hi again. In line 7 of post 3 you use a semicolon ";" instead of a single quote "'" as the delimiter immediately after referring to your Customer No control. Change it to a single quote and the reference to the Customer No should be correctly recognised.

You are also missing some closing single quotes from your DLookups - see lines 14 and 15 for examples where there is an opening quote but no closing quote used (at the end of the statement).

I cannot untangle the nesting of the Dlookups, but some of them do not look closed to me - I can only say that I think the set of Dlookups is not quite right (look at line 13, where there is a reference to Customer No outside the Lookup but with no comparator).

Given the number of typos that have occurred I would ask you to check carefully that all DLookups are opened and closed correctly, and where they are nested (if that is the intention) that the nesting is correctly done, or you will never sort out the errors that arise. I would also check and recheck that there is a closing quote for every opening quote, and that comparators are used when you are actually comparing values (cf line 13 and the lack of a comparator).

As a stylistic matter I note that you add spaces onto the string like this:

"previous part of string" & " " & "next part of string" & " "

I would suggest for clarity declaring a string constant and using it as follows:

Const SingleSpace = " "
"previous part of string" & SingleSpace & "next part of string" & SingleSpace

There is nothing wrong at all with using discrete space strings the way you do at present, but declaring them explicitly makes reading the code (and hence maintaining it in future) easier.

-Stewart

[clloyd]

I am sorry. I am new at this. I don't understand what you

[clloyd]

Ignore my last message I hit the button to soon. I didn't understand something but figured it out. OK I reworked it and I think I am almost there. See if this makes better sence. I decided to attach the report because I have another program that I do this for and it works fine. The problem I truly believe with this one is it is pulling in information from two different tables as I mentioned before. Here is the reworked code:

 
Private Sub cmdEmail_Click()
On Error GoTo Err_Email_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "rptReportDistribution"
    stEmailTo = DLookup("[CustomerMailingsEMail]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'")
    'stLinkCriteria = ("[CustomerNo]=" & "'" & ("[CustomerNo]=Reports![rptReportDistribution]![CustomerNo]" AND ""[ClientID]=" & "'" & ("[ClientID]=Reports![rptReportDistribution]![ClientID]")& "'"
    DoCmd.SendObject acReport, stDocName, acFormatRTF, stEmailTo, DLookup("[ReportMailingCC]", "[Clients]", "[ClientID] = '" & Me![ClientID]), , _
    "Loss Prevention Survey" & "," & " " & DLookup("[Loc Name]&','&' '& 'CustomerNo.' & ' ' & [CustomerNo]&','&' '& [Physical City]&','&' '& [Physical State]&','&' '& [Physical Country]", "[Customer]", "[CustomerNo]=Reports![rptReportDistribution]![CustomerNo]"), _
    "Report Mailing Instructions:" & "," & " " & DLookup("[ReportMailingInstructions]", "[Clients]", "[ClientID] = '" & Me![ClientID] & "'") _
    & vbCr & DLookup("[ReportMailingEmailText]", "[Clients]", "[ClientID] = '" & Me![ClientID])
 
 
Exit_Email_Click:
    Exit Sub
 
Err_Email_Click:
    MsgBox Err.Description
    Resume Exit_Email_Click
 
End Sub
 
 

[Stewart Ross]

Hi. I am not sure what you mean when you mention "pulling in information from two different tables". Dlookup is intended to look up a value from any table or query you require. However, I notice you are referring to controls on a report within your DLookup - this will not work for you. Firstly, it could only work at all if the report is open at the time. Secondly, including the name of the control as part of the string will lead to problems interpreting what you want to lookup - the norm is to include the current value of such a control within the Dlookup (as you are doing for the Customer No value in other DLookups).

I am unable to comment further on whether or not what you are trying to do is sound, because I do not and cannot know the details you know of what you are trying to achieve. What I can advise is as follows:

* DLookup can be used to return values from tables or queries, just as you are doing
* If in the DLookup criteria you include comparisons of fields to controls use a reference to the value of the control, not its name (see line 11 post 6 for an example of including the name of a report control as part of the criteria string)

That is, for numeric values use

DLookup("somevalue", "sometable", "somefield = " & acontrolname)

instead of

DLookup("somevalue", "sometable", "somefield = acontrolname")

or for strings

DLookup("somevalue", "sometable", "somefield = '" & acontrolname & "'")

instead of

DLookup("somevalue", "sometable", "somefield = acontrolname")

* If the value you are referring to is on another form or report that form or report must be open at the time you refer to it. You cannot refer to controls on forms or reports that are not open at the time.

I hope this gives you enough in the way of pointers to solve any remaining issues. I think I have gone as far as is possible in interpreting what could be wrong with your code without having the database in front of me at the time.

-Stewart

[clloyd]

It definatetly does help. Thank you for all your help. I am going to keep plugging away on it.