emailing a form

[jayme]

I am not an advanced access user so I am in need of some suggestions here...
I work for a facility maintenance company that does a variety of jobs for many commercial companies with a several different trade groups we have (electrical, hvac, plumbing, painting, masonry, etc...) Our Trade Group Managers submit estimates and budgets for the jobs they are bidding to. I created a database to store all those jobs so we can track what manager has bid to any certain job, what trades are involved, if it has been accepted-rejected-in progress-and such. As of now the managers turn a written (paper) form into me to submit an estimating inquiry request and I put it in the database and print them out a receipt of all the information they submitted to me along with an Inquiry Number (which is the primary key in the database)-then that is how we keep track of that particular bid-with that unique Inquiry Number assigned to it.
Now we are wanting to be able to have this all electronically. So the managers can submit an inquiry request electronically and get a receipt back electronically-either by email or online or whatever. On the form they fill out there are several different trades that are able to be checked off by check boxes. After they are finished filling out the form I would like for there to be a button at the bottom to email that form to all the checked off trades. How do I go about doing that?


..any suggestions would be greatly appreciated.
Thank you so much!!

[Denburt]

Sounds like you have some work cut out for you. I have a vbs script that will generate a questionaire and they can fill it in and send me the results... Sounds like this might be a prt of something you might use to get started on. The code is indepth and you will need a fair amount of coding to accomplish this. I will be willing ot help if I can but you will have to be patient. Let me know if I might be on the right track.

[developing]

sounds like Denburt got your back; but if that gets too complicated, try the new command button wizard...it has built in report/form funtions that allow you to email form/report

[Denburt]

I am so out of touch with the wizards I guess I should work with them more. :)

[jayme]

developing - im not sure i will be able to go that route (button wizard) or not...we have ms access 2000 so i don't know if my version has that option or not? thanks for your help!!

denburt - thank you so much! i really appreciate your help. i found a code yesterday that i thought might be sending me on the right track but i am not familiar with ms access coding at all so i don't know if it will work or not.
this is what i found -

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
 
 
'This forces the record to be saved.
 
DoCmd.RunCommand acCmdSaveRecord
 
 
'There is no need to check if the form is blank (as with report and preview) as the cmdEmailReport button will
'only be enabled if there is and e-mail address in CustomerEMailAddress field.
 
 
 
'If there is nothing in the subject control (CustomerEMailSubject) then the subject field in the e-mail client
'will display the name of the report.
 
If IsNull(Me!CustomerEMailSubject) Or Me!CustomerEMailSubject = "" Then
 
 
    strMailto = Me.CustomerEMailAddress
    strDocName = "Rpt_Customer"
    StrCriterion = " [CustomerID]=" & Forms![Frm_Customer].[CustomerID]
 
'This will hide the customer form
 
 
Me.Visible = False
 
 
 ' This will open the report with the same primary key as the form on the screen.In preview mode
 
    DoCmd.OpenReport "Rpt_Customer", acPreview, , StrCriterion
 
 
 
'This will minimize the report, whilst the e-mail in being prepared, I have tried not to have it open in preview but
'cannot get it to work.
 
    DoCmd.Minimize
 
 
 
 
 
'This will create the e-mail
 
DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strDocName, , True, ""
 
 
 
   'Tbis explains all the section of the e-mail see SendObject in help
      'Explanation DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]
 
 
 
Else
 
'If there is something in the subject control (CustomerEMailSubject) then the subject field in the e-mail client
'will display that data.
 
    strMailto = Me.CustomerEMailAddress
    strSubject = Me.CustomerEMailSubject
    strDocName = "Rpt_Customer"
    StrCriterion = " [CustomerID]=" & Forms![Frm_Customer].[CustomerID]
 
 
'This will hide the customer form
 
 
Me.Visible = False
 
 
 
 
 DoCmd.OpenReport "Rpt_Customer", acPreview, , StrCriterion
 
 
 
'This will minimize the report, whilst the e-mail in being prepared, I have tried not to have it open in preview but
'cannot get it to work.
 
    DoCmd.Minimize
 
 
 
 
 
'This will create the e-mail
 
DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strSubject, , True, ""
 
   'Tbis explains all the section of the e-mail see SendObject in help
      'Explanation DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]
End If
 
'This will close the report when you either cancel or send the e-mail. The Frm_Customer will open
DoCmd.Close acReport, "Rpt_Customer"
 
 
cmdEMailReport_Click_Exit:
    Exit Sub
 
cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
 
    End Sub
 

i obviously need to change it a little to fit what my report/form is called but i thought maybe this might help a little. but i dont know...
they have given me a month or two to get this complete so i do have some time...well-let me know what you think!!
thanks again!

[Denburt]

The code looks good give it a try and let us know if you have any problems. A lot of the commands in the code can be highlighted then you hit F1 and you will see help on that topic.

[jayme]

i have been playing with it and was erasing the parts i didnt need and came up with this...

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
 
 
'This forces the record to be saved.
 
DoCmd.RunCommand acCmdSaveRecord
 
'If there is something in the subject control (CustomerEMailSubject) then the subject field in the e-mail client
'will display that data.
 
    strMailto = "[e-mail]"
    strSubject = ":: NEW INQUIRY ::"
    strDocName = "JOB TRACKING"
    StrCriterion = " [Inquiry No]=" & Forms![JOB TRACKING form].[Inquiry No]
 
 
'This will hide the customer form
 
 
Me.Visible = False
 
 
 
 
 DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
 
 
 
'This will minimize the report, whilst the e-mail in being prepared, I have tried not to have it open in preview but
'cannot get it to work.
 
    DoCmd.Minimize
 
 
 
 
 
'This will create the e-mail
 
DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strSubject, , True, ""
 
   'Tbis explains all the section of the e-mail see SendObject in help
      'Explanation DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]
 
 
'This will close the report when you either cancel or send the e-mail. The Frm_Customer will open
DoCmd.Close acReport, "JOB TRACKING"
 
 
cmdEMailReport_Click_Exit:
Exit Sub
 
cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
 
End Sub
 

it works as far as creating an email for me with that certain record on it - the thing is now-i had put in my email address(as a test to see what it would do), but i am going to need it to send it to several different emails and they won't be the same each time.
like i said before-there are checkboxes for the trade groups that get involved in the jobs. so if the electrical box is checked i want that record to go to the electrical manager-if the plumbing is checked then send it to the plumbing manager-and there could be when there are 2 or 3 boxes checked at one time. so is there a way to tell it that if that box is checked send it to this email address?
also-when it creates the file to put in the email, i opened it up to see what it looks like, and the checkboxes do not transfer over to the document it creates(opens up in word) so it has the lists of where the checkboxes are but there are no checks to see what information is checked off-not sure if there is a way around that?

[NeoPa]

Jayme,
Clearly you've put some work into this project, but please remember this forum is for answering relatively small, self-contained questions. Not about getting someone to take you through a whole project. If you are getting stuck, it's down to you to break it down to a level which is answerable by people with more experience than yourself, rather than expecting others to take you through the whole complicated process.

MODERATOR.

[Denburt]

Curious, what is your experience with VBA? I don't mind helping out, but one step at a time. You posted a lot of information about a lot of issues but we have to find a place to start.

[Denburt]

it works as far as creating an email for me with that certain record on it - the thing is now-i had put in my email address(as a test to see what it would do), but i am going to need it to send it to several different emails and they won't be the same each time.
like i said before-there are checkboxes for the trade groups that get involved in the jobs. so if the electrical box is checked i want that record to go to the electrical manager-if the plumbing is checked then send it to the plumbing manager-and there could be when there are 2 or 3 boxes checked at one time. so is there a way to tell it that if that box is checked send it to this email address?

rs =Select MyEmailField from MyTable where myCheckBox = true
'Then just loop through the recordset
if not rs.eof then
rs.movefirst
Do until rs.eof
if len(strMailto) >0 then 
strMailto = strMailto & ";" &  rs!MyEmailField
else
strMailto = rs!MyEmailField
rs.movenext
loop
end if
 

also-when it creates the file to put in the email, i opened it up to see what it looks like, and the checkboxes do not transfer over to the document it creates(opens up in word) so it has the lists of where the checkboxes are but there are no checks to see what information is checked off-not sure if there is a way around that?

Interesting, I will try and check this out when I get a chance. If anyone else wants to post on this matter feel free.

[jayme]

I do not have much experience with VBA-pretty much I am learning as I go here..so I'm just trying to find things here and there that will help me along the way...you are helping out a lot-and I do appreciate it. Sorry if I went into information overload here-breaking this down into steps sounds like a good way of going about this...thanks.
I will work with the code you posted before about emailing to the different email addresses today..and I'll see what I come up with.

I do have one quick question for anyone that may know what the deal is...
I was testing the "Email Report" button I have with the code I posted before and all of a sudden out of nowhere it starts creating an email and making the attachement an xls attachement and opening in excel instead of a rtf attachment and opening in word. The code is the same I was using before and it was working-just wasn't sure what might have changed...??

[Denburt]

Verify the fact that this line has not changed:

DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strSubject, , True, ""
 

[jayme]

i saw that line-where is tells it to create the rtf file..but it hasn't changed-that is still the same..that is why i was so confused that all of a sudden it changed. I dont remember doing anything else to it either..so if the code is exactly the same as before i wasn't sure what else it could be that was making it create an xls file??

[Denburt]

Try the following:

DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
 

[jayme]

still attaches as an xls...so weird!!

[Denburt]

In that module did you do a search for xls? Try that and see if you find anything.

[jayme]

i searched for anything with xls and nothing comes up.

[Denburt]

In the VBA window look at the menu Press Debug then Compile. if this doesnt help locate your problem i would suggest importing everything to a new BD and try it... I agree very strange.

[jayme]

it come up with this message:

compile error: method or data member not found

then this part of the VBA is highlighted(the part of it that is bold)

Private Sub Combo99_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Inquiry No] = " & Str(Me![Combo99])
Me.Bookmark = rs.Bookmark
End Sub

not sure what that means?

[jayme]

got it! i found this website that solved my problem -

http://support.microsoft.com/kb/555020

so i just got rid of some temp files that were on my computer and now it works. so that problem is solved! :-)
now for my next issue...ive been trying to play around with getting the code you sent me to be able to send the email to different managers based on the boxes that are checked off and i can get it to work out right. it keeps red lining and highlighting the word SELECT. i made a seperate table with the email addresses in them but i didnt know if i needed to do that or not or if i would be able to say
If [001 - Electric] = true then send to "email address"
If [002 - Plumbing] = true then send to "email address"
and so on-but I wasn't sure how to write that out or if that would even work.
Let me know what your thoughts are on that.
Thank you!!

[Denburt]

got it! i found this website that solved my problem -

http://support.microsoft.com/kb/555020

so i just got rid of some temp files that were on my computer and now it works. so that problem is solved! :-)
now for my next issue...ive been trying to play around with getting the code you sent me to be able to send the email to different managers based on the boxes that are checked off and i can get it to work out right. it keeps red lining and highlighting the word SELECT. i made a seperate table with the email addresses in them but i didnt know if i needed to do that or not or if i would be able to say
If [001 - Electric] = true then send to "email address"
If [002 - Plumbing] = true then send to "email address"
and so on-but I wasn't sure how to write that out or if that would even work.
Let me know what your thoughts are on that.
Thank you!!

Well if that isn't interesting...
OK

set rs =db.openrecordset("Select MyEmailField from MyTable where myCheckBox = true")
 

I am guessing that Electric, Plumbing etc is stored in your database somewhere so I will take this a bit further. This is in no way meant to be copy paste but should get you close.

set rs1 =db.openrecordset("Select WorkerType from MyTable")
if not rs1.eof then
rs1.movefirst
Do until rs1.eof
set rs =db.openrecordset("Select MyEmailField from MyTable where" &  rs1!WorkerType & " = true")
rs.requery
 
BuildTo = BuildTo & rs!MyEmailField & ";"
 
rs1.movenext
loop
end if
rs1.close
rs.close
 

[NeoPa]

now for my next issue...ive been trying to play around with getting the code you sent me to be able to send the email to different managers based on the boxes that are checked off and i can get it to work out right. it keeps red lining and highlighting the word SELECT. i made a seperate table with the email addresses in them but i didnt know if i needed to do that or not or if i would be able to say
If [001 - Electric] = true then send to "email address"
If [002 - Plumbing] = true then send to "email address"
and so on-but I wasn't sure how to write that out or if that would even work.
Let me know what your thoughts are on that.
Thank you!!

Jayme,
Again your question is quite broad in scope. It is also vague with no existing code posted, no indication of any effort put in on your part first. Please reread my earlier post and consider falling in line with the site requirements for posting questions.
(POSTING GUIDELINES: Please read carefully before posting to a forum)

MODERATOR.

[edit]
It seems that Denburt has already picked this question up. I have no problem with that, but please remember (in the future) that the guidelines are there for your benefit too, as well as for those 'Experts' who try, in the face of poorly asked questions, to help you.

[jayme]

Thanks for your help Denburt. I appreciate it-I will take what you have given me and work with it some more.
I hope you don't think I am not working on my end on this too-I don't want to seem like I am just using you to do all the work-I do a lot of research every day pretty much the whole time I'm at work here to try and figure this thing out-but like I said, I do not have experience with this kind of thing. I had enough knowlege to build the actual database but what they are asking now is over my head. I do appreciate you leading me along tremendously!!
NeoPost-I don't mean to be that broad with my questions. I am sorry...I guess I just don't know enough about it to narrow down what my issue is. I will try to do better..sorry.

[Denburt]

I do a lot of research every day pretty much the whole time I'm at work here

I wish I had that luxury when I was learning VBA. :) I spent years learning on my own time. Uh still do lol :)

[jayme]

It is nice - but they would have to pay me a lot more if I had to go learn this on my own time! I'm enjoying learning but I think this is one intense project to learn on...at least in my opinion! :-)

[jayme]

I am guessing that Electric, Plumbing etc is stored in your database somewhere so I will take this a bit further. This is in no way meant to be copy paste but should get you close.

set rs1 =db.openrecordset("Select WorkerType from MyTable")
if not rs1.eof then
rs1.movefirst
Do until rs1.eof
set rs =db.openrecordset("Select MyEmailField from MyTable where" &  rs1!WorkerType & " = true")
rs.requery
 
BuildTo = BuildTo & rs!MyEmailField & ";"
 
rs1.movenext
loop
end if
rs1.close
rs.close
 

quick question- ive been playing around with this all evening yesterday and all morning today-ive tried looking around to see what it might mean but i cant seem to find anything on it...
what does this line in the code do?

BuildTo = BuildTo & rs!MyEmailField & ";"

I put my information in to come up with this code -

Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where [001 - Electrical] from Forms![JOB TRACKING form] = true")
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
Set rs2 = db.openrecordset("Select PLUMBING from EmailAddresses where [002 - Plumbing] from Forms![JOB TRACKING form]" & rs1!Electrical & " = true")
rs2.Requery
Set rs3 = db.openrecordset("Select HVAC from EmailAddresses where [012 - HVAC] from Forms![JOB TRACKING form]" & rs1!Electrical & rs2!Plumbing & " = true")
rs3.Requery
 
BuildTo = BuildTo & rs1!Electrical & rs2!Plumbing & rs3!HVAC ";"
 
rs1.MoveNext
Loop
End If
rs1.Close
rs.Close
 

but it keeps redlining that certain line with the BuildTo =
and since i am not sure what it does I don't know how to go about changing it?

[Denburt]

BuildTo is merely a variable I used to hold the emails send to information I can use whatever word I want such as strMailto as a variable but you want to be careful that MS access doesn't already use that as a function or method. That is why I usually use a combination effect just to be sure. After each email address you will want to have a ; (colon) separating them. You want to make sure all of your variables are declared in the beginning of the statement as well.

Dim strMailto as string
Dim db as database
Dim rs as recordset
Dim rs1 as recordset
Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where [001 - Electrical] from Forms![JOB TRACKING form] = true")
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
Set rs2 = db.openrecordset("Select PLUMBING from EmailAddresses where [002 - Plumbing] from Forms![JOB TRACKING form]" & rs1!Electrical & " = true")
rs2.Requery
Set rs3 = db.openrecordset("Select HVAC from EmailAddresses where [012 - HVAC] from Forms![JOB TRACKING form]" & 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
rs.Close
Set rs1 = nothing
Set rs = nothing
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
 

Unless of course you are sure that they will all contain data. if you know they contain data and you have provided measures to make sure they are populated then you can use one string instead of breaking it up. i also agree that this is a big project to start on but I think you are managing it well.

[jayme]

Alright-been messing around with this code-a few errors have been coming up - I figured out some of them...

My first error was "User-defined type not found" but I got that take care of.

Then another error popped up "Object variable or With block variable not set" -
so i found something where it said I might need to add
Set db = CurrentDb(name of my database)

So i did that...then another error pops up saying that
"Job Tracking can't find the field 'Forms' referred to in your expression."
the only place i have Forms is in the lists I have here-
Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where [001 - Electrical] from Forms![JOB TRACKING form] = true")
i took out the s on those so it would just say Form![ but then it still said the same error. ??

[Denburt]

Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where [001 - Electrical] from Forms![JOB TRACKING form] = true")

Lets adjust this to read:

Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where " & Forms![JOB TRACKING form]!WhatIsTheFieldName & " = true")
 

If the above code resides in the [Job Tracking Form] Module then you can simply state it in the following manner.

Me!WhatIsTheFieldName

Instead of:

Forms![JOB TRACKING form]!WhatIsTheFieldName


Looking GOOD :)

[jayme]

Ok-I tried that but it is still saying the same error-
"Job Tracking can't find the field 'Forms' referred to in your expression."
I don't know what the deal is-just to see what it would do I took out the word Forms and it still was saying that...any other ideas what that error could be from?

[Denburt]

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

[jayme]

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

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?

[NeoPa]

Do they work as expected?

[jayme]

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

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.

[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.

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]!WhatIsTheFieldName) 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

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[jayme]

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.

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]!WhatIsTheFieldName) 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

Field; Type; IndexInfo
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 WhatIsTheFieldName 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."

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=EmailAddresses
field names in the EmailAddresses table=ELECTRICAL, 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

[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.

[jayme]

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.

[NeoPa]

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.

[Denburt]

I did just catch this:

'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.

[jayme]

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?

[Denburt]

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

[jayme]

i changed a few things in this part of the 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...

[jayme]

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.

[Denburt]

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.

[jayme]

Ok-I do have an issue now! Probably more mental than anything! :-) I looked VERY close this time to make sure everything was the same and I had in the syntax as [001 - Electrical] and on the form as [001 - Electric]!! SO-I went through and changed in the syntax to take out the -al's but then it still came up as saying it can't find that field! I went through and made sure they were ALL changed. Why would it still be saying it can't find that field when it is nowhere in the code now?

[Denburt]

Just so I have this straight, you are now using:

Me![001 - Electric]

and you are still getting an error stating that it can not find the field:
[001 - Electrical]


Is this correct? If so make sure you have saved the form, then make sure you compile your code, in the VBA window press the menu button Debug then press compile. Try again still the same? Check your code closely then check any and all queries. The recordsource and any and all combo boxes, it has to be there somewhere.

[jayme]

Just so I have this straight, you are now using:

Me![001 - Electric]

and you are still getting an error stating that it can not find the field:
[001 - Electrical]


Is this correct? If so make sure you have saved the form, then make sure you compile your code, in the VBA window press the menu button Debug then press compile. Try again still the same? Check your code closely then check any and all queries. The recordsource and any and all combo boxes, it has to be there somewhere.

That is correct.
When I go to compile the code it always comes up with "Method or data member not found" and has the bold part below highlighted

Private Sub Combo105_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me!rs.Clone
    rs.FindFirst "[Inquiry No] = " & Str(Me![Combo105])
    Me.Bookmark = rs.Bookmark
End Sub
 

Not sure what that has to do with it-but just in case.
I will continue looking through and making sure everything I can find matches up...
Thank you!

[jayme]

I took out a couple of the groups and just left in the Electrical group to see if I could get this one working before I start adding in other groups.
I have gone through it and can not find what is up.
It is giving me the error where it cant find the field "|" in my expression. I don't know what that means-I can't find anywhere where there is a "|". SO I just want to make sure I have this right...

ELECTRIC the field in the EmailAddresses table where the electrical managers email address is.
JOB TRACKING form is the form we are on
001=the checkbox we are using

Do I have them all in the correct places of the syntax?

Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses where " & [Forms]![JOB TRACKING form]![001] & " = true")
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
 

Also-where there is rs1!Electric-what is the "Electric" word to represent? Just wanted to make sure I have that labeled correctly as well.

If Not IsNull(rs1!Electric) And Len(rs1!Electric) > 0 Then
strMailto = strMailto & rs1!Electric & ";"
End If
rs1.MoveNext
Loop
End If
rs1.Close
Set rs1 = Nothing
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
 

[Denburt]

That is correct.
When I go to compile the code it always comes up with "Method or data member not found" and has the bold part below highlighted

Private Sub Combo105_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me!rs.Clone
    rs.FindFirst "[Inquiry No] = " & Str(Me![Combo105])
    Me.Bookmark = rs.Bookmark
End Sub
 

Not sure what that has to do with it-but just in case.
I will continue looking through and making sure everything I can find matches up...
Thank you!

looking at this Str(Me![Combo105]) it would appear that [Inquiry No] is a string and should have quotes around it. Use the following code it should compile once you do this. You need to make sure it compiles or you are liable to see all kinds of strange things happen.

Private Sub Combo105_AfterUpdate()
    Dim rs As Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[Inquiry No] = '" & Str(Me![Combo105]) & "'"
    Me.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing
Sub