469,280 Members | 1,790 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,280 developers. It's quick & easy.

emailing a form

jayme
83
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!!
Mar 29 '07 #1
80 4901
Denburt
1,356 Expert 1GB
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.
Mar 30 '07 #2
developing
110 100+
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
Mar 30 '07 #3
Denburt
1,356 Expert 1GB
I am so out of touch with the wizards I guess I should work with them more. :)
Mar 30 '07 #4
jayme
83
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 -
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailReport_Click()
  2. On Error GoTo cmdEMailReport_Click_Err
  3.  
  4. Dim strMsg As String, strTitle As String
  5. Dim intStyle As Integer
  6. Dim StrCriterion As String
  7. Dim strMailto As String
  8. Dim strSubject As String
  9. Dim strDocName As String
  10.  
  11.  
  12. 'This forces the record to be saved.
  13.  
  14. DoCmd.RunCommand acCmdSaveRecord
  15.  
  16.  
  17. 'There is no need to check if the form is blank (as with report and preview) as the cmdEmailReport button will
  18. 'only be enabled if there is and e-mail address in CustomerEMailAddress field.
  19.  
  20.  
  21.  
  22. 'If there is nothing in the subject control (CustomerEMailSubject) then the subject field in the e-mail client
  23. 'will display the name of the report.
  24.  
  25. If IsNull(Me!CustomerEMailSubject) Or Me!CustomerEMailSubject = "" Then
  26.  
  27.  
  28.     strMailto = Me.CustomerEMailAddress
  29.     strDocName = "Rpt_Customer"
  30.     StrCriterion = " [CustomerID]=" & Forms![Frm_Customer].[CustomerID]
  31.  
  32. 'This will hide the customer form
  33.  
  34.  
  35. Me.Visible = False
  36.  
  37.  
  38.  ' This will open the report with the same primary key as the form on the screen.In preview mode
  39.  
  40.     DoCmd.OpenReport "Rpt_Customer", acPreview, , StrCriterion
  41.  
  42.  
  43.  
  44. 'This will minimize the report, whilst the e-mail in being prepared, I have tried not to have it open in preview but
  45. 'cannot get it to work.
  46.  
  47.     DoCmd.Minimize
  48.  
  49.  
  50.  
  51.  
  52.  
  53. 'This will create the e-mail
  54.  
  55. DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strDocName, , True, ""
  56.  
  57.  
  58.  
  59.    'Tbis explains all the section of the e-mail see SendObject in help
  60.       'Explanation DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]
  61.  
  62.  
  63.  
  64. Else
  65.  
  66. 'If there is something in the subject control (CustomerEMailSubject) then the subject field in the e-mail client
  67. 'will display that data.
  68.  
  69.     strMailto = Me.CustomerEMailAddress
  70.     strSubject = Me.CustomerEMailSubject
  71.     strDocName = "Rpt_Customer"
  72.     StrCriterion = " [CustomerID]=" & Forms![Frm_Customer].[CustomerID]
  73.  
  74.  
  75. 'This will hide the customer form
  76.  
  77.  
  78. Me.Visible = False
  79.  
  80.  
  81.  
  82.  
  83.  DoCmd.OpenReport "Rpt_Customer", acPreview, , StrCriterion
  84.  
  85.  
  86.  
  87. 'This will minimize the report, whilst the e-mail in being prepared, I have tried not to have it open in preview but
  88. 'cannot get it to work.
  89.  
  90.     DoCmd.Minimize
  91.  
  92.  
  93.  
  94.  
  95.  
  96. 'This will create the e-mail
  97.  
  98. DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strSubject, , True, ""
  99.  
  100.    'Tbis explains all the section of the e-mail see SendObject in help
  101.       'Explanation DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]
  102. End If
  103.  
  104. 'This will close the report when you either cancel or send the e-mail. The Frm_Customer will open
  105. DoCmd.Close acReport, "Rpt_Customer"
  106.  
  107.  
  108. cmdEMailReport_Click_Exit:
  109.     Exit Sub
  110.  
  111. cmdEMailReport_Click_Err:
  112.     MsgBox Error$
  113.     Resume cmdEMailReport_Click_Exit
  114.  
  115.     End Sub
  116.  
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!
Mar 30 '07 #5
Denburt
1,356 Expert 1GB
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.
Mar 30 '07 #6
jayme
83
i have been playing with it and was erasing the parts i didnt need and came up with this...
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailReport_Click()
  2. On Error GoTo cmdEMailReport_Click_Err
  3.  
  4. Dim strMsg As String, strTitle As String
  5. Dim intStyle As Integer
  6. Dim StrCriterion As String
  7. Dim strMailto As String
  8. Dim strSubject As String
  9. Dim strDocName As String
  10.  
  11.  
  12. 'This forces the record to be saved.
  13.  
  14. DoCmd.RunCommand acCmdSaveRecord
  15.  
  16. 'If there is something in the subject control (CustomerEMailSubject) then the subject field in the e-mail client
  17. 'will display that data.
  18.  
  19.     strMailto = "jayme.kuenkel@viox-services.com"
  20.     strSubject = ":: NEW INQUIRY ::"
  21.     strDocName = "JOB TRACKING"
  22.     StrCriterion = " [Inquiry No]=" & Forms![JOB TRACKING form].[Inquiry No]
  23.  
  24.  
  25. 'This will hide the customer form
  26.  
  27.  
  28. Me.Visible = False
  29.  
  30.  
  31.  
  32.  
  33.  DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
  34.  
  35.  
  36.  
  37. 'This will minimize the report, whilst the e-mail in being prepared, I have tried not to have it open in preview but
  38. 'cannot get it to work.
  39.  
  40.     DoCmd.Minimize
  41.  
  42.  
  43.  
  44.  
  45.  
  46. 'This will create the e-mail
  47.  
  48. DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strSubject, , True, ""
  49.  
  50.    'Tbis explains all the section of the e-mail see SendObject in help
  51.       'Explanation DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]
  52.  
  53.  
  54. 'This will close the report when you either cancel or send the e-mail. The Frm_Customer will open
  55. DoCmd.Close acReport, "JOB TRACKING"
  56.  
  57.  
  58. cmdEMailReport_Click_Exit:
  59. Exit Sub
  60.  
  61. cmdEMailReport_Click_Err:
  62.     MsgBox Error$
  63.     Resume cmdEMailReport_Click_Exit
  64.  
  65. End Sub
  66.  
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?
Mar 30 '07 #7
NeoPa
32,173 Expert Mod 16PB
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.
Mar 30 '07 #8
Denburt
1,356 Expert 1GB
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.
Mar 30 '07 #9
Denburt
1,356 Expert 1GB
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?
Expand|Select|Wrap|Line Numbers
  1. rs =Select MyEmailField from MyTable where myCheckBox = true
  2. 'Then just loop through the recordset
  3. if not rs.eof then
  4. rs.movefirst
  5. Do until rs.eof
  6. if len(strMailto) >0 then 
  7. strMailto = strMailto & ";" &  rs!MyEmailField
  8. else
  9. strMailto = rs!MyEmailField
  10. rs.movenext
  11. loop
  12. end if
  13.  
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.
Mar 31 '07 #10
jayme
83
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...??
Apr 2 '07 #11
Denburt
1,356 Expert 1GB
Verify the fact that this line has not changed:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strSubject, , True, ""
  2.  
Apr 2 '07 #12
jayme
83
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??
Apr 2 '07 #13
Denburt
1,356 Expert 1GB
Try the following:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  2.  
Apr 2 '07 #14
jayme
83
still attaches as an xls...so weird!!
Apr 2 '07 #15
Denburt
1,356 Expert 1GB
In that module did you do a search for xls? Try that and see if you find anything.
Apr 2 '07 #16
jayme
83
i searched for anything with xls and nothing comes up.
Apr 3 '07 #17
Denburt
1,356 Expert 1GB
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.
Apr 3 '07 #18
jayme
83
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?
Apr 3 '07 #19
jayme
83
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!!
Apr 3 '07 #20
Denburt
1,356 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. set rs =db.openrecordset("Select MyEmailField from MyTable where myCheckBox = true")
  2.  
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.

Expand|Select|Wrap|Line Numbers
  1. set rs1 =db.openrecordset("Select WorkerType from MyTable")
  2. if not rs1.eof then
  3. rs1.movefirst
  4. Do until rs1.eof
  5. set rs =db.openrecordset("Select MyEmailField from MyTable where" &  rs1!WorkerType & " = true")
  6. rs.requery
  7.  
  8. BuildTo = BuildTo & rs!MyEmailField & ";"
  9.  
  10. rs1.movenext
  11. loop
  12. end if
  13. rs1.close
  14. rs.close
  15.  
Apr 3 '07 #21
NeoPa
32,173 Expert Mod 16PB
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.
Apr 3 '07 #22
jayme
83
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.
Apr 3 '07 #23
Denburt
1,356 Expert 1GB
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 :)
Apr 3 '07 #24
jayme
83
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! :-)
Apr 3 '07 #25
jayme
83
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.

Expand|Select|Wrap|Line Numbers
  1. set rs1 =db.openrecordset("Select WorkerType from MyTable")
  2. if not rs1.eof then
  3. rs1.movefirst
  4. Do until rs1.eof
  5. set rs =db.openrecordset("Select MyEmailField from MyTable where" &  rs1!WorkerType & " = true")
  6. rs.requery
  7.  
  8. BuildTo = BuildTo & rs!MyEmailField & ";"
  9.  
  10. rs1.movenext
  11. loop
  12. end if
  13. rs1.close
  14. rs.close
  15.  
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 -
Expand|Select|Wrap|Line Numbers
  1. Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where [001 - Electrical] from Forms![JOB TRACKING form] = true")
  2. If Not rs1.EOF Then
  3. rs1.MoveFirst
  4. Do Until rs1.EOF
  5. Set rs2 = db.openrecordset("Select PLUMBING from EmailAddresses where [002 - Plumbing] from Forms![JOB TRACKING form]" & rs1!Electrical & " = true")
  6. rs2.Requery
  7. Set rs3 = db.openrecordset("Select HVAC from EmailAddresses where [012 - HVAC] from Forms![JOB TRACKING form]" & rs1!Electrical & rs2!Plumbing & " = true")
  8. rs3.Requery
  9.  
  10. BuildTo = BuildTo & rs1!Electrical & rs2!Plumbing & rs3!HVAC ";"
  11.  
  12. rs1.MoveNext
  13. Loop
  14. End If
  15. rs1.Close
  16. rs.Close
  17.  
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?
Apr 4 '07 #26
Denburt
1,356 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strMailto as string
  2. Dim db as database
  3. Dim rs as recordset
  4. Dim rs1 as recordset
  5. Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where [001 - Electrical] from Forms![JOB TRACKING form] = true")
  6. If Not rs1.EOF Then
  7. rs1.MoveFirst
  8. Do Until rs1.EOF
  9. Set rs2 = db.openrecordset("Select PLUMBING from EmailAddresses where [002 - Plumbing] from Forms![JOB TRACKING form]" & rs1!Electrical & " = true")
  10. rs2.Requery
  11. Set rs3 = db.openrecordset("Select HVAC from EmailAddresses where [012 - HVAC] from Forms![JOB TRACKING form]" & rs1!Electrical & rs2!Plumbing & " = true")
  12. rs3.Requery
  13. if not isnull(rs1!Electrical) and len(rs1!Electrical)>0 then
  14. strMailto = strMailto & rs1!Electrical & ";" 
  15. end if
  16. if not isnull(rs2!Plumbing) and len(rs2!Plumbing)>0 then
  17. strMailto = strMailto &  rs2!Plumbing & ";"
  18. end if
  19. if not isnull(rs3!HVAC) and len(rs3!HVAC)>0 then
  20. strMailto = strMailto & rs3!HVAC & ";"
  21. end if
  22. rs1.MoveNext
  23. Loop
  24. End If
  25. rs1.Close
  26. rs.Close
  27. Set rs1 = nothing
  28. Set rs = nothing
  29. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  30.  
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.
Apr 4 '07 #27
jayme
83
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. ??
Apr 4 '07 #28
Denburt
1,356 Expert 1GB
Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where [001 - Electrical] from Forms![JOB TRACKING form] = true")
Lets adjust this to read:

Expand|Select|Wrap|Line Numbers
  1. Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where " & Forms![JOB TRACKING form]!WhatIsTheFieldName & " = true")
  2.  
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 :)
Apr 4 '07 #29
jayme
83
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?
Apr 4 '07 #30
Denburt
1,356 Expert 1GB
I only set up the one query for you as an example, you have several queries in there that need the same adjustments.
Apr 4 '07 #31
jayme
83
i did change the rest of them accordingly... this is what they look like now...

Expand|Select|Wrap|Line Numbers
  1. Dim strMsg As String, strTitle As String
  2. Dim intStyle As Integer
  3. Dim StrCriterion As String
  4. Dim strMailto As String
  5. Dim strSubject As String
  6. Dim strDocName As String
  7. Dim db As database
  8. Dim rs As Recordset
  9. Dim rs1 As Recordset
  10. Set db = CurrentDb([Copy of Copy of JOB TRACKINGbackup 3-29-07])
  11.  
  12. DoCmd.RunCommand acCmdSaveRecord
  13.  
  14. Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where " & Forms![JOB TRACKING form]![001 - Electrical] & " = true")
  15. If Not rs1.EOF Then
  16. rs1.MoveFirst
  17. Do Until rs1.EOF
  18. Set rs2 = db.openrecordset("Select PLUMBING from EmailAddresses where " & Forms![JOB TRACKING form]![002 - Plumbing] & rs1!Electrical & " = true")
  19. rs2.Requery
  20. Set rs3 = db.openrecordset("Select HVAC from EmailAddresses where " & Forms![JOB TRACKING form]![012 - HVAC] & rs1!Electrical & rs2!Plumbing & " = true")
  21. rs3.Requery
  22. If Not IsNull(rs1!Electrical) And Len(rs1!Electrical) > 0 Then
  23. strMailto = strMailto & rs1!Electrical & ";"
  24. End If
  25. If Not IsNull(rs2!Plumbing) And Len(rs2!Plumbing) > 0 Then
  26. strMailto = strMailto & rs2!Plumbing & ";"
  27. End If
  28. If Not IsNull(rs3!HVAC) And Len(rs3!HVAC) > 0 Then
  29. strMailto = strMailto & rs3!HVAC & ";"
  30. End If
  31. rs1.MoveNext
  32. Loop
  33. End If
  34. rs1.Close
  35. rs2.Close
  36. rs3.Close
  37. Set rs1 = Nothing
  38. Set rs2 = Nothing
  39. Set rs3 = Nothing
  40. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  41.  
  42.  
  43.     strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
  44.     strDocName = "JOB TRACKING"
  45.     StrCriterion = " [Inquiry No]=" & Forms![JOB TRACKING form].[Inquiry No]
  46.  
  47.  
  48. Me.Visible = False
  49.  
  50.  
  51. DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
  52.  
  53.  
  54. DoCmd.Minimize
  55.  
  56.  
  57. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  58.  
  59.  
  60. DoCmd.Close acReport, "JOB TRACKING"
  61.  
  62.  
  63. cmdEMailReport_Click_Exit:
  64. Exit Sub
  65.  
  66. cmdEMailReport_Click_Err:
  67.     MsgBox Error$
  68.     Resume cmdEMailReport_Click_Exit
  69.  
  70. End Sub
  71.  
are those correct?
Apr 4 '07 #32
NeoPa
32,173 Expert Mod 16PB
Do they work as expected?
Apr 5 '07 #33
jayme
83
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

Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 5 '07 #34
NeoPa
32,173 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Apr 5 '07 #35
jayme
83
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.
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. 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."

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailReport_Click()
  2. On Error GoTo cmdEMailReport_Click_Err
  3.  
  4. Dim strMsg As String, strTitle As String
  5. Dim intStyle As Integer
  6. Dim StrCriterion As String
  7. Dim strMailto As String
  8. Dim strSubject As String
  9. Dim strDocName As String
  10. Dim db As database
  11. Dim rs As Recordset
  12. Dim rs1 As Recordset
  13. Set db = CurrentDb([Copy of Copy of JOB TRACKINGbackup 3-29-07])
  14.  
  15. DoCmd.RunCommand acCmdSaveRecord
  16.  
  17. Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where " & Forms![JOB TRACKING form]![001 - Electrical] & " = true")
  18. If Not rs1.EOF Then
  19. rs1.MoveFirst
  20. Do Until rs1.EOF
  21. Set rs2 = db.openrecordset("Select PLUMBING from EmailAddresses where " & Forms![JOB TRACKING form]![002 - Plumbing] & rs1!Electrical & " = true")
  22. rs2.Requery
  23. Set rs3 = db.openrecordset("Select HVAC from EmailAddresses where " & Forms![JOB TRACKING form]![012 - HVAC] & rs1!Electrical & rs2!Plumbing & " = true")
  24. rs3.Requery
  25. If Not IsNull(rs1!Electrical) And Len(rs1!Electrical) > 0 Then
  26. strMailto = strMailto & rs1!Electrical & ";"
  27. End If
  28. If Not IsNull(rs2!Plumbing) And Len(rs2!Plumbing) > 0 Then
  29. strMailto = strMailto & rs2!Plumbing & ";"
  30. End If
  31. If Not IsNull(rs3!HVAC) And Len(rs3!HVAC) > 0 Then
  32. strMailto = strMailto & rs3!HVAC & ";"
  33. End If
  34. rs1.MoveNext
  35. Loop
  36. End If
  37. rs1.Close
  38. rs2.Close
  39. rs3.Close
  40. Set rs1 = Nothing
  41. Set rs2 = Nothing
  42. Set rs3 = Nothing
  43. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  44.  
  45.  
  46.     strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
  47.     strDocName = "JOB TRACKING"
  48.     StrCriterion = " [Inquiry No]=" & Forms![JOB TRACKING form].[Inquiry No]
  49.  
  50.  
  51. Me.Visible = False
  52.  
  53.  
  54. DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
  55.  
  56.  
  57. DoCmd.Minimize
  58.  
  59.  
  60. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  61.  
  62.  
  63. DoCmd.Close acReport, "JOB TRACKING"
  64.  
  65.  
  66. cmdEMailReport_Click_Exit:
  67. Exit Sub
  68.  
  69. cmdEMailReport_Click_Err:
  70.     MsgBox Error$
  71.     Resume cmdEMailReport_Click_Exit
  72.  
  73. End Sub
  74.  
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
Apr 5 '07 #36
NeoPa
32,173 Expert Mod 16PB
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.
Apr 5 '07 #37
jayme
83
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.
Apr 5 '07 #38
NeoPa
32,173 Expert Mod 16PB
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.
Apr 5 '07 #39
Denburt
1,356 Expert 1GB
I did just catch this:

Expand|Select|Wrap|Line Numbers
  1. 'Remove this
  2. Set db = CurrentDb([Copy of Copy of JOB TRACKINGbackup 3-29-07])
  3. 'Change to read:
  4. Set db = CurrentDb
  5.  
Thats about the only thing I can see let me know how it goes.
Apr 5 '07 #40
jayme
83
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?
Apr 5 '07 #41
Denburt
1,356 Expert 1GB
Can you find the field [001 - Electrical] in your form?
Apr 5 '07 #42
jayme
83
i changed a few things in this part of the code
Expand|Select|Wrap|Line Numbers
  1. Set rs1 = db.openrecordset("Select ELECTRICAL from EmailAddresses where [Forms]![JOB TRACKING form]![001 - Electrical] = true")
  2. If Not rs1.EOF Then
  3. rs1.MoveFirst
  4. Do Until rs1.EOF
  5. Set rs2 = db.openrecordset("Select PLUMBING from EmailAddresses where [Forms]![JOB TRACKING form]![002 - Plumbing] & rs1!Electrical & = true")
  6. rs2.Requery
  7. Set rs3 = db.openrecordset("Select HVAC from EmailAddresses where [Forms]![JOB TRACKING form]![012 - HVAC] & rs1!Electrical & rs2!Plumbing & = true")
  8. rs3.Requery
  9.  
and it moved on from the previous error to this one-
"Too Few Parameters. Expected 1"
so im looking into this one now...
Apr 5 '07 #43
jayme
83
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.
Apr 5 '07 #44
Denburt
1,356 Expert 1GB
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.
Apr 5 '07 #45
jayme
83
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?
Apr 5 '07 #46
Denburt
1,356 Expert 1GB
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.
Apr 5 '07 #47
jayme
83
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo105_AfterUpdate()
  2.     ' Find the record that matches the control.
  3.     Dim rs As Object
  4.     Set rs = Me!rs.Clone
  5.     rs.FindFirst "[Inquiry No] = " & Str(Me![Combo105])
  6.     Me.Bookmark = rs.Bookmark
  7. End Sub
  8.  
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!
Apr 5 '07 #48
jayme
83
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?

Expand|Select|Wrap|Line Numbers
  1. Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses where " & [Forms]![JOB TRACKING form]![001] & " = true")
  2. If Not rs1.EOF Then
  3. rs1.MoveFirst
  4. Do Until rs1.EOF
  5.  
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.
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(rs1!Electric) And Len(rs1!Electric) > 0 Then
  2. strMailto = strMailto & rs1!Electric & ";"
  3. End If
  4. rs1.MoveNext
  5. Loop
  6. End If
  7. rs1.Close
  8. Set rs1 = Nothing
  9. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  10.  
Apr 5 '07 #49
Denburt
1,356 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo105_AfterUpdate()
  2.     ' Find the record that matches the control.
  3.     Dim rs As Object
  4.     Set rs = Me!rs.Clone
  5.     rs.FindFirst "[Inquiry No] = " & Str(Me![Combo105])
  6.     Me.Bookmark = rs.Bookmark
  7. End Sub
  8.  
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo105_AfterUpdate()
  2.     Dim rs As Recordset
  3.     Set rs = Me.RecordsetClone
  4.     rs.FindFirst "[Inquiry No] = '" & Str(Me![Combo105]) & "'"
  5.     Me.Bookmark = rs.Bookmark
  6. rs.Close
  7. Set rs = Nothing
  8. Sub
  9.  
Apr 5 '07 #50

Post your reply

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

Similar topics

reply views Thread by Steven Scaife | last post: by
9 posts views Thread by Brendan MAther | last post: by
2 posts views Thread by Chuck | last post: by
1 post views Thread by Alfie | last post: by
2 posts views Thread by Paul Wilson | last post: by
4 posts views Thread by Mike Moore | last post: by
reply views Thread by paulhux174 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.