Saving contents of a filtered datasheet

[GooGoocluster]

I have a subform in data view that displays a list of filtered records based on user criteria. I want to make a command button that emails these results to who ever they choose. I know to start with the send object to email. But I don’t know how to get the filtered results into the body of an email or even saved as an attachment.


The forms record source is base off of a sql string but then it has filters that narrow down the search.

Do I use some sort of querydef to get results into a temp table or is there a ways to just get the form results and paste them in a email if you will.

Thanks in advance

 

[Zion7]

You can send the table, via SendObject

DoCmd.SendObject acSendTable, "tblHours", acRTF, "joebloe@xxx.com", , , "Yo Joe!", "Wuz Up?", True

but, I would make a report, with your subform table, as the underlying table.

Open Report with same criteria, you filter subform with, then,

DoCmd.SendObject acSendReport, "rptHours", acRTF, "joebloe@xxx.com", , , "Yo Joe!", "Wuz Up?", True

 

[GooGoocluster]

I have made the report, but now I am trying to make the filter for the form but I cant pass the varribles to the report from the form. here is the filter for the form

Forms!Shipping!sf.Form.Filter = strAccount + strAnd1 + strZIP + strAnd2 + strPKID + strAnd3 + strCarrier

The command button is a public sub on both the button I set the varribles and where I need to retive them. I dont remmeber how to set global varibles. I thought that is in the () of public sub when i tried this it fails and says It cant find my form.

Thanks again.

 

[Zion7]

FROM ANY Standard Module, in Declarations section,
Public strFilter As String

You could try this

DoCmd.OpenReport "rptShipping", acViewDesign, , , acHidden
Reports!rptShipping.RecordSource = _
"SELECT * FROM tblSHipping WHERE " & strFilter
DoCmd.SendObject acSendTable, "tblHours", acFormatRTF, "joebloe@xxx.com", , , "Yo Joe!", "Wuz Up?", True

DoCmd.Close acReport, "rptShipping", acSaveNo

 

[GooGoocluster]

Public Sub Command48_Click()
Public strAccount As String
Public strFrmDate As String
Public strToDate As String
Public strZIP As String
Public strPKID As String
Public strCarrier As String
Public strSQL As String
Public strSQL2 As String
Public strAnd1 As String

This is what I have but it returns with a compile error. I dont think I know what you mean. sorry about the confusion

 

[PHV]

And the compile error wasn't explicit enough for you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GooGoocluster]

You see I am trying to difine a global varible but they way I saw zion7 difine it this way. I know this is not the right way. becuase it thinks its suppossed to be a sub. but it is not. its suppossed to be a varible

I have tried the following

dim strAccount as global
global strAccount as string

I know this would work

global const strAccount As string "account"

But I dont need a constant. I am really lost any help would be good. or even somthing to search to lead me on the right track thanks again

 

[Zion7]

Public Sub Command48_Click()????

 

[PHV]

What the compile error said to you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GooGoocluster]

Its the command button to a find button here is my entire code.

Public Sub Command48_Click() 'strAccount, strFrmDate, strToDate, strZIP, strPKID, strCarrier, strSQL, strSQL2, strAnd1, strAnd2, strAnd3 As String)
Public strAccount  As String
Public strFrmDate As String
Public strToDate As String
Public strZIP As String
Public strPKID As String
Public strCarrier As String
Public strSQL As String
Public strSQL2 As String
Public strAnd1 As String


If Me.cboAccount <> "" Then
strAccount = "[dept] Like '" & Me.cboAccount & "%'"
Else: strAccount = ""
End If

If Me.txtZip <> "" Then
strZIP = "[zip] Like '" & Me.txtZip & "%'"
    If Me.cboAccount <> "" Then
    strAnd1 = "AND"
    Else: strAnd1 = ""
    End If
Else: strZIP = ""
End If

If Me.cboPKID <> "" Then
strPKID = "[pkgid] Like '" & Me.cboPKID & "%'"
    If Me.cboAccount <> "" Or Me.txtZip <> "" Then
    strAnd2 = "AND"
    Else: strAnd2 = ""
    End If
Else: strPKID = ""
End If

If Me.cboCarrier <> "" Then
strCarrier = "[shipper] Like '" & Me.cboCarrier & "%'"
    If Me.cboAccount <> "" Or Me.txtZip <> "" Or Me.cboPKID <> "" Then
    strAnd3 = "AND"
    Else: strAnd3 = ""
    End If
Else: strCarrier = ""
End If

If Me.txtFromDate And Me.txtToDate <> "" Then
strFrmDate = Me.txtFromDate
strToDate = Me.txtToDate
strSQL = "SELECT * FROM shipping WHERE (cal1 BETWEEN '" & strFrmDate & "' AND '" & strToDate & "')"
Else: strSQL = "SELECT * FROM shipping"
End If

'strPKID = Me.cboPKID.Value
'strCarrier = Me.cboCarrier.Value


Forms!Shipping!sf.Form.FilterOn = False



Forms!Shipping!sf.Form.RecordSource = strSQL

Forms!Shipping!sf.Form.Filter = strAccount + strAnd1 + strZIP + strAnd2 + strPKID + strAnd3 + strCarrier
Forms!Shipping!sf.Form.FilterOn = True
End Sub

Then this is the command button to send the email (dont have it sending yet I want to see the results first.)

Public Sub Command51_Click()
DoCmd.OpenReport "Tracking Report", acViewDesign, , , acHidden
Reports![Tracking Report].Report.RecordSource = strSQL
'Reports![Tracking Report].Report.Filter = strAccount + strAnd1 + strZIP + strAnd2 + strPKID + strAnd3 + strCarrier
'Reports![Tracking Report].Report.FilterOn = True
DoCmd.OpenReport "Tracking Report", acViewPreview

End Sub

 

[GooGoocluster]

What the compile error said to you ?

it stoped on

 [COLOR=red][B]Public[/B][/color] strAccount  As String

returned the error:
Invalid attribute in Sub or Function

 

[PHV]

So, reread Zion7's posts and try to make sense with that the compiler say to you.

 

[Zion7]

Thank-you PHV!

First of all GGC, You're in a Class or Form Module, not a Standard module.
Secondly, you are not declaring your variables, in the declarations section, of a standard, or ANY module.

Option Compare Database
Option Explicit

Public strAccount As String
Public strFrmDate As String
Public strToDate As String
Public strZIP As String
Public strPKID As String
Public strCarrier As String
Public strSQL As String
Public strSQL2 As String
Public strAnd1 As String

___________________________________________________

Public Function Carrier()As String

Carrier = strCarrier
End Function

From VBE window, find menu icon "Insert", Then click "Module".

NOw you are in a standard module.

 

[GooGoocluster]

Thanks for clearing that up. After rereading your post I put it in a module. But I still was not putting them in the declarations section. Sorry I did not know what that was at the time thanks again for your help guys works like a charm