I only set up the one query for you as an example, you have several queries in there that need the same adjustments.
emailing a form
Collapse
X
-
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
Comment
-
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")
Comment
-
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")
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
-
Originally posted by NeoPaJayme,
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")
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
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
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], etcComment
-
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
-
Originally posted by NeoPaLet 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.
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
-
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
-
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
"Too Few Parameters. Expected 1"
so im looking into this one now...Comment
-
Originally posted by DenburtCan you find the field [001 - Electrical] in your form?Comment
-
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
Comment