emailing a form

[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_AfterUp date()
' 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.microso ft.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.openrecordse t("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.openrecordse t("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!WhatIsTheFie ldName

Instead of:

Forms![JOB TRACKING form]!WhatIsTheField Name


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?