emailing a form

[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[b].Bookmark [/b]= 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[b].Bookmark [/b]= 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

[jayme]

ok-i got it to compile-so thats good. one step closer! :-)

[Denburt]

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

Yes looks good.

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, ""

rs1!ELECTRIC is reffering to the field ELECTRIC in the EmailAddresses table. You are essentially communicating with the recordset you created.
EXAMPLE ONLY:
rs1!YourFieldIn TheRecordset
In your case you are using an SQL statement with one field ELECTRIC.

[jayme]

rs1!ELECTRIC is reffering to the field ELECTRIC in the EmailAddresses table. You are essentially communicating with the recordset you created.
EXAMPLE ONLY:
rs1!YourFieldIn TheRecordset
In your case you are using an SQL statement with one field ELECTRIC.

thats what i thought-but just wanted to make sure i had it right. i dont know what else it could be. i have checked and double checked. that doesnt mean i didnt miss anything but even when i used the "Find" function it didn't come up with anywhere that has a "|". So that error is still coming up-driving me nuts! ahh!
Obviously I have something wrong here-I just have no clue what...is there anywhere else you can think of that I should look for this error?
"job tracking can't find the field '|' referred to in your expression"

[Denburt]

No option to debug when you get this message?

Try this just to verify:

Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses where Electric = true")

[jayme]

No option to debug when you get this message?

No-you can just push OK then that's it-nothing else happens.

Try this just to verify:

Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses where Electric = true")

Tried this one too-same thing..
so weird!
Do you think I have my EmailAddresses table set up the wrong way or something set up wrong outside of the actual syntax?
My EmailAddresses table has
Electric Plumbing etc.. -going across the top
then I have the email addreses under those-some have more than one email address in it-others will just have one.
(just want to make sure thats not causing issues)
I am not really sure what else it could be...

[Denburt]

My EmailAddresses table has
Electric Plumbing etc.. -going across the top
then I have the email addreses under those-some have more than one email address in it-others will just have one.

Sounds like this is the problem. I thought you said that "Electric" was a yes/no field.

I would also like to make sure that if you are storing more than one address in a field that they are separated by a colon 1@2.com;2@3.com etc.

Now we need to determine which records to pull and how to pull them.

I have reread some of the posts and I think what needs to be done is to clarify what is happening here. On your form you choose whether you want to send it to Electric, plumbing etc. right so far? Now what does the check box tell us, you didn't mention it in your table so I am only guessing that it only exists on the form correct?

[jayme]

In my table with Electric, Plumbing, etc going across the top I have the email addresses under those, but I have each email address in a seperate row-same column, but seperate row.

As far as my form-
There are checkboxes on there labeled as 001 - Electric, 002 - Plumbing, etc (the 001, 002, etc is the group number we use here) Anyways-so those are the labels but I went back and named them just with the number so there aren't spaces and dashes and all that. So we will just be using the 001, 002, 003, etc for the checkboxes-yes/no fields. These checkboxes do only exist on the form.
So I want to be able to check the 001 box on the form then it go to the EmailAddresses table and find Elecric then pull the email address(es) under it.
Does that make sense?

[Denburt]

This is probably what you want:

if me!001 = true then
Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses")
elseif me!002 then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
elseif me!003 then

etc.
end if

[jayme]

cool-thanks.
do i still keep the other syntax from before?

If Me![001] = True Then
Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
ElseIf Me![002] = True Then
Set rs2 = db.openrecordset("Select Plumbing from EmailAddresses")
End If
If Not IsNull(rs1!Electric) And Len(rs1!Electric) > 0 Then
strMailto = strMailto & rs1!Electric & ";"
End If
If Not IsNull(rs2!Plumbing) And Len(rs2!Plumbing) > 0 Then
strMailto = strMailto & rs2!Plumbing & ";"
End If
rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing

[jayme]

i have been searching around to see what this error means but am unable to figure out why it is saying this...
"object doesn't support this property or method"
here is the code i have in the VBA:

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
Dim rs2 As Recordset
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord

If Me![001] = True Then
Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
	If Not rs1.EOF Then
	rs1.MoveFirst
	End If
End If
Do Until rs1.EOF
Loop
If Me![002] = True Then
Set rs2 = db.openrecordset("Select Plumbing from EmailAddresses")
	rs2.Requery
	If Not IsNull(rs1!Electric) And Len(rs1!Electric) > 0 Then
	strMailto = strMailto & rs1!Electric & ";"
End If
If Not IsNull(rs2!Plumbing) And Len(rs2!Plumbing) > 0 Then
strMailto = strMailto & rs2!Plumbing & ";"
End If
rs1.MoveNext
End If
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = 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

thank you again!