By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

Send an Email with parameters from two different tables

P: 93
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?
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdEmail_Click()
  3.     Dim CustomerNo As Parameter
  4.     Dim ClientID As Parameter
  5.  
  6. On Error GoTo Err_Email_Click
  7.  
  8.     stEmailTo = DLookup("[CustomerMailingsEMail]", "[Customer]", "[CustomerNo] = '" & Me![ CustomerNo] & ";" & " " _
  9.     And DLookup("[ReportMailingCC]", "[Clients]", "[ClientID] = '" & Me![ClientID]) & "'")
  10.     DoCmd.SendObject acReport, stDocName, acFormatRTF, stEmailTo, , , _
  11.     "Orders" & "," & " " & DLookup("[Customer Name]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'") & "," & " " & _
  12.     DLookup("[Physical City]", "[Customer]", "[ CustomerNo] = '" & Me![ Customer No] & "'") & "," & " " & _
  13.     DLookup("[Physical State]", "[ Customer]", "[ CustomerNo] = '" & Me![ CustomerNo] & "'") & "," & " " & _
  14.     DLookup("[Physical Country]", "[ Customer]", "[ CustomerNo] = '" & Me![ CustomerNo] & "'") & " " & " CustomerNo" & " " & Me![ CustomerNo], _
  15.     DLookup("[ReportMailingInstructions]", "[Clients]", "[ClientID] = '" & Me![ClientID]) _
  16.     & vbCr & DLookup("[ReportMailingEmailText]", "[Clients]", "[ClientID] = '" & Me![ClientID])
  17.  
  18.  
  19. Exit_Email_Click:
  20.     Exit Sub
  21.  
  22. Err_Email_Click:
  23.     MsgBox Err.Description
  24.     Resume Exit_Email_Click
  25.  
  26. End Sub
  27.  
  28.  
Jun 4 '08 #1
Share this Question
Share on Google+
7 Replies


Expert Mod 2.5K+
P: 2,545
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
Jun 4 '08 #2

P: 93
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdEmail_Click()
  3. Dim CustomerNo As Parameter
  4. Dim ClientID As Parameter
  5.  
  6. On Error GoTo Err_Email_Click
  7.  
  8. stEmailTo = DLookup("[CustomerMailingsEMail]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & ";" & " " _
  9. And DLookup("[ReportMailingCC]", "[Clients]", "[ClientID] = '" & Me![ClientID]) & "'")
  10. DoCmd.SendObject acReport, stDocName, acFormatRTF, stEmailTo, , , _
  11. "Orders" & "," & " " & DLookup("[CustomerName]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'") & "," & " " & _
  12. DLookup("[Physical City]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'") & "," & " " & _
  13. DLookup("[Physical State]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'") & "," & " " & _
  14. DLookup("[Physical Country]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'") & " " & " CustomerNo" & " " & Me![CustomerNo], _
  15. DLookup("[ReportMailingInstructions]", "[Clients]", "[ClientID] = '" & Me![ClientID]) _
  16. & vbCr & DLookup("[ReportMailingEmailText]", "[Clients]", "[ClientID] = '" & Me![ClientID])
  17.  
  18.  
  19. Exit_Email_Click:
  20. Exit Sub
  21.  
  22. Err_Email_Click:
  23. MsgBox Err.Description
  24. Resume Exit_Email_Click
  25.  
  26. End Sub
  27.  
  28.  
Jun 4 '08 #3

Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. "previous part of string" & " " & "next part of string" & " "
I would suggest for clarity declaring a string constant and using it as follows:
Expand|Select|Wrap|Line Numbers
  1. Const SingleSpace = " "
  2. "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
Jun 4 '08 #4

P: 93
I am sorry. I am new at this. I don't understand what you
Jun 4 '08 #5

P: 93
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdEmail_Click()
  3. On Error GoTo Err_Email_Click
  4.  
  5.     Dim stDocName As String
  6.     Dim stLinkCriteria As String
  7.  
  8.     stDocName = "rptReportDistribution"
  9.     stEmailTo = DLookup("[CustomerMailingsEMail]", "[Customer]", "[CustomerNo] = '" & Me![CustomerNo] & "'")
  10.     'stLinkCriteria = ("[CustomerNo]=" & "'" & ("[CustomerNo]=Reports![rptReportDistribution]![CustomerNo]" AND ""[ClientID]=" & "'" & ("[ClientID]=Reports![rptReportDistribution]![ClientID]")& "'"
  11.     DoCmd.SendObject acReport, stDocName, acFormatRTF, stEmailTo, DLookup("[ReportMailingCC]", "[Clients]", "[ClientID] = '" & Me![ClientID]), , _
  12.     "Loss Prevention Survey" & "," & " " & DLookup("[Loc Name]&','&' '& 'CustomerNo.' & ' ' & [CustomerNo]&','&' '& [Physical City]&','&' '& [Physical State]&','&' '& [Physical Country]", "[Customer]", "[CustomerNo]=Reports![rptReportDistribution]![CustomerNo]"), _
  13.     "Report Mailing Instructions:" & "," & " " & DLookup("[ReportMailingInstructions]", "[Clients]", "[ClientID] = '" & Me![ClientID] & "'") _
  14.     & vbCr & DLookup("[ReportMailingEmailText]", "[Clients]", "[ClientID] = '" & Me![ClientID])
  15.  
  16.  
  17. Exit_Email_Click:
  18.     Exit Sub
  19.  
  20. Err_Email_Click:
  21.     MsgBox Err.Description
  22.     Resume Exit_Email_Click
  23.  
  24. End Sub
  25.  
  26.  
Jun 4 '08 #6

Expert Mod 2.5K+
P: 2,545
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
Expand|Select|Wrap|Line Numbers
  1. DLookup("somevalue", "sometable", "somefield = " & acontrolname)
instead of
Expand|Select|Wrap|Line Numbers
  1. DLookup("somevalue", "sometable", "somefield = acontrolname")
or for strings
Expand|Select|Wrap|Line Numbers
  1. DLookup("somevalue", "sometable", "somefield = '" & acontrolname & "'")
instead of
Expand|Select|Wrap|Line Numbers
  1. 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
Jun 4 '08 #7

P: 93
It definatetly does help. Thank you for all your help. I am going to keep plugging away on it.
Jun 5 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.