emailing a form

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Denburt
    Recognized Expert Top Contributor
    • Mar 2007
    • 1356

    #31
    I only set up the one query for you as an example, you have several queries in there that need the same adjustments.

    Comment

    • jayme
      New Member
      • Mar 2007
      • 83

      #32
      i did change the rest of them accordingly... this is what they look like now...

      Code:
      Dim strMsg As String, strTitle As String
      Dim intStyle As Integer
      Dim StrCriterion As String
      Dim strMailto As String
      Dim strSubject As String
      Dim strDocName As String
      Dim db As database
      Dim rs As Recordset
      Dim rs1 As Recordset
      Set db = CurrentDb([Copy of Copy of JOB TRACKINGbackup 3-29-07])
      
      DoCmd.RunCommand acCmdSaveRecord
      
      Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where " & Forms![JOB TRACKING form]![001 - Electrical] & " = true")
      If Not rs1.EOF Then
      rs1.MoveFirst
      Do Until rs1.EOF
      Set rs2 = db.openrecordset("Select PLUMBING from EmailAddresses where " & Forms![JOB TRACKING form]![002 - Plumbing] & rs1!Electrical & " = true")
      rs2.Requery
      Set rs3 = db.openrecordset("Select HVAC from EmailAddresses where " & Forms![JOB TRACKING form]![012 - HVAC] & rs1!Electrical & rs2!Plumbing & " = true")
      rs3.Requery
      If Not IsNull(rs1!Electrical) And Len(rs1!Electrical) > 0 Then
      strMailto = strMailto & rs1!Electrical & ";"
      End If
      If Not IsNull(rs2!Plumbing) And Len(rs2!Plumbing) > 0 Then
      strMailto = strMailto & rs2!Plumbing & ";"
      End If
      If Not IsNull(rs3!HVAC) And Len(rs3!HVAC) > 0 Then
      strMailto = strMailto & rs3!HVAC & ";"
      End If
      rs1.MoveNext
      Loop
      End If
      rs1.Close
      rs2.Close
      rs3.Close
      Set rs1 = Nothing
      Set rs2 = Nothing
      Set rs3 = Nothing
      DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
      
      
          strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
          strDocName = "JOB TRACKING"
          StrCriterion = " [Inquiry No]=" & Forms![JOB TRACKING form].[Inquiry No]
      
      
      Me.Visible = False
      
         
      DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
          
          
      DoCmd.Minimize
          
      
      DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
      
       
      DoCmd.Close acReport, "JOB TRACKING"
      
      
      cmdEMailReport_Click_Exit:
      Exit Sub
      
      cmdEMailReport_Click_Err:
          MsgBox Error$
          Resume cmdEMailReport_Click_Exit
          
      End Sub
      are those correct?

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32633

        #33
        Do they work as expected?

        Comment

        • jayme
          New Member
          • Mar 2007
          • 83

          #34
          no-they dont. we were trying to figure out why i kept getting the error message -
          "Job Tracking can't find the field 'Forms' referred to in your expression."

          Denburt said to try changing the code to

          Code:
          Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses here " & Forms![JOB TRACKING form]!WhatIsTheFieldName & " = true")
          so i did that and changed the other queries accordingly-i was just making sure i changed them all correctly since i am still getting that error message...i cant figure out why i would be getting that.

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32633

            #35
            Jayme,
            In that case you need to make that clear in your post. None of us is a mind-reader.
            You need to look more closely at what Denburt has tried to tell you.
            Code:
            Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses here " & Forms![JOB TRACKING form]!WhatIsTheFieldName & " = true")
            This means you must replace (EmailAddresses here) with the names of your EmailAddresses (Tables I expect).
            Likewise (Forms![JOB TRACKING form]!WhatIsTheField Name) indicates that he doesn't know the field name you should use (Presumably because you haven't shared your table MetaData) so you need to edit this to match.

            BTW: Here is an example of how to post table MetaData :
            Table Name=tblStudent
            Code:
            [i]Field; Type; IndexInfo[/i]
            StudentID; Autonumber; PK
            Family; String; FK
            Name; String
            University; String; FK
            Mark; Numeric
            LastAttendance; Date/Time

            Comment

            • jayme
              New Member
              • Mar 2007
              • 83

              #36
              Originally posted by NeoPa
              Jayme,
              In that case you need to make that clear in your post. None of us is a mind-reader.
              You need to look more closely at what Denburt has tried to tell you.
              Code:
              Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses here " & Forms![JOB TRACKING form]!WhatIsTheFieldName & " = true")
              This means you must replace (EmailAddresses here) with the names of your EmailAddresses (Tables I expect).
              Likewise (Forms![JOB TRACKING form]!WhatIsTheField Name) indicates that he doesn't know the field name you should use (Presumably because you haven't shared your table MetaData) so you need to edit this to match.

              BTW: Here is an example of how to post table MetaData :
              Table Name=tblStudent
              Code:
              [i]Field; Type; IndexInfo[/i]
              StudentID; Autonumber; PK
              Family; String; FK
              Name; String
              University; String; FK
              Mark; Numeric
              LastAttendance; Date/Time
              sorry. i did do that tho-EmailAddresses is the name of my table and as i posted my code before it does show i changed the WhatIsTheFieldN ame to what my field name actually is.
              this is the code i have in my db-so i was just wondering if anything sticks out that could be wrong with it to keep coming up with the error-
              Job Tracking can't find the field 'Forms' referred to in your expression."

              Code:
              Private Sub cmdEmailReport_Click()
              On Error GoTo cmdEMailReport_Click_Err
              
              Dim strMsg As String, strTitle As String
              Dim intStyle As Integer
              Dim StrCriterion As String
              Dim strMailto As String
              Dim strSubject As String
              Dim strDocName As String
              Dim db As database
              Dim rs As Recordset
              Dim rs1 As Recordset
              Set db = CurrentDb([Copy of Copy of JOB TRACKINGbackup 3-29-07])
              
              DoCmd.RunCommand acCmdSaveRecord
              
              Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where " & Forms![JOB TRACKING form]![001 - Electrical] & " = true")
              If Not rs1.EOF Then
              rs1.MoveFirst
              Do Until rs1.EOF
              Set rs2 = db.openrecordset("Select PLUMBING from EmailAddresses where " & Forms![JOB TRACKING form]![002 - Plumbing] & rs1!Electrical & " = true")
              rs2.Requery
              Set rs3 = db.openrecordset("Select HVAC from EmailAddresses where " & Forms![JOB TRACKING form]![012 - HVAC] & rs1!Electrical & rs2!Plumbing & " = true")
              rs3.Requery
              If Not IsNull(rs1!Electrical) And Len(rs1!Electrical) > 0 Then
              strMailto = strMailto & rs1!Electrical & ";"
              End If
              If Not IsNull(rs2!Plumbing) And Len(rs2!Plumbing) > 0 Then
              strMailto = strMailto & rs2!Plumbing & ";"
              End If
              If Not IsNull(rs3!HVAC) And Len(rs3!HVAC) > 0 Then
              strMailto = strMailto & rs3!HVAC & ";"
              End If
              rs1.MoveNext
              Loop
              End If
              rs1.Close
              rs2.Close
              rs3.Close
              Set rs1 = Nothing
              Set rs2 = Nothing
              Set rs3 = Nothing
              DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
              
              
                  strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
                  strDocName = "JOB TRACKING"
                  StrCriterion = " [Inquiry No]=" & Forms![JOB TRACKING form].[Inquiry No]
              
              
              Me.Visible = False
              
                 
              DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
                  
                  
              DoCmd.Minimize
                  
              
              DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
              
               
              DoCmd.Close acReport, "JOB TRACKING"
              
              
              cmdEMailReport_Click_Exit:
              Exit Sub
              
              cmdEMailReport_Click_Err:
                  MsgBox Error$
                  Resume cmdEMailReport_Click_Exit
                  
              End Sub
              table name=EmailAddre sses
              field names in the EmailAddresses table=ELECTRICA L, PLUMBING, ETC
              form that gets emailed=JOB TRACKING form
              check box on the form (determines what emails to use-whether true or not)=[001 - Electric], [002 - Plumbing], etc

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32633

                #37
                Let me be clear about this.
                I am not going to do all your work for you because you can't be bothered to make sure what you post is accurate.
                If I ask a question, I expect an accurate answer and not 'Well you can find that in my previous post if you look'. If I choose to offer up some of my time to try to assist you I do not appreciate having to chase around after you to make sure what you've posted is consistent with everything else you've posted.
                I'd be surprised if you can find anyone to help you while you're displaying such a cavalier attitude to the help that is being attempted for you.

                Comment

                • jayme
                  New Member
                  • Mar 2007
                  • 83

                  #38
                  Originally posted by NeoPa
                  Let me be clear about this.
                  I am not going to do all your work for you because you can't be bothered to make sure what you post is accurate.
                  If I ask a question, I expect an accurate answer and not 'Well you can find that in my previous post if you look'. If I choose to offer up some of my time to try to assist you I do not appreciate having to chase around after you to make sure what you've posted is consistent with everything else you've posted.
                  I'd be surprised if you can find anyone to help you while you're displaying such a cavalier attitude to the help that is being attempted for you.
                  NeoPa-
                  I wasn't trying to have an attitude about this. I appreciate your offering help. I did think I was answering your question tho-I reposted what I had posted before and explained what my field names, table names, and such were. I don't expect you to do all my work-I have been doing a lot of work on my own as well-my question was just to see if anything was missing or incorrect with my code for me to be getting the error. Anyways-I am not forcing you or chasing you to help me with this-so if you feel that way-that's fine. Thanks anyways.
                  Have a great day.

                  Comment

                  • NeoPa
                    Recognized Expert Moderator MVP
                    • Oct 2006
                    • 32633

                    #39
                    Jayme,
                    I hear you, and am partially mollified. I still don't appreciate your approach much, but I understand you intend no harm or disrespect and that's the important thing.
                    However, while I wish you good luck, I will give this thread a miss from now.

                    Regards NeoPa.

                    Comment

                    • Denburt
                      Recognized Expert Top Contributor
                      • Mar 2007
                      • 1356

                      #40
                      I did just catch this:

                      Code:
                      'Remove this
                      Set db = CurrentDb([Copy of Copy of JOB TRACKINGbackup 3-29-07])
                      'Change to read:
                      Set db = CurrentDb
                      Thats about the only thing I can see let me know how it goes.

                      Comment

                      • jayme
                        New Member
                        • Mar 2007
                        • 83

                        #41
                        Great thanks! That took care of that error-but it doesn't seem to ever be happy-now it has moved on to another error-pretty much the same thing but its saying it can't find the field '001 - Electrical' in my expression?

                        Comment

                        • Denburt
                          Recognized Expert Top Contributor
                          • Mar 2007
                          • 1356

                          #42
                          Can you find the field [001 - Electrical] in your form?

                          Comment

                          • jayme
                            New Member
                            • Mar 2007
                            • 83

                            #43
                            i changed a few things in this part of the code
                            Code:
                            Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where [Forms]![JOB TRACKING form]![001 - Electrical] = true")
                            If Not rs1.EOF Then
                            rs1.MoveFirst
                            Do Until rs1.EOF
                            Set rs2 = db.openrecordset("Select PLUMBING from EmailAddresses where [Forms]![JOB TRACKING form]![002 - Plumbing] & rs1!Electrical & = true")
                            rs2.Requery
                            Set rs3 = db.openrecordset("Select HVAC from EmailAddresses where [Forms]![JOB TRACKING form]![012 - HVAC] & rs1!Electrical & rs2!Plumbing & = true")
                            rs3.Requery
                            and it moved on from the previous error to this one-
                            "Too Few Parameters. Expected 1"
                            so im looking into this one now...

                            Comment

                            • jayme
                              New Member
                              • Mar 2007
                              • 83

                              #44
                              Originally posted by Denburt
                              Can you find the field [001 - Electrical] in your form?
                              yes - it is the checkbox on my form that if it is checked (so if it is 'true') then it will pull that managers email address and email him the form.. it is for a fact on my form.

                              Comment

                              • Denburt
                                Recognized Expert Top Contributor
                                • Mar 2007
                                • 1356

                                #45
                                I understand I just wanted you to be sure that it is spelled the same in both places.
                                The syntax on the original SQL statements were correct but for some reason the VBA code requesting the info from your form could not locate the control in question so until we can determine why the two don't correlate we will have an issue. Changing the syntax of the query isn't going to help. The issue lies with us calling the form and getting the info from the control.
                                Problem:
                                [Forms]![JOB TRACKING form]![001 - Electrical]

                                Try using:
                                Me![001 - Electrical]

                                If that doesn't work try the following:
                                Forms![JOB TRACKING form]![001 - Electrical]


                                FYI:
                                Just so you know I am not a big fan of spaces or any kind of special characters in table names or control names, Forms Etc. MS Access has plenty of issues and sometimes these things can cause it to hick up you should be O.K. for now so lets just see if we can work through this issue first.

                                Comment

                                Working...