Export filtered records from a form with multiple filter criteria

[GreenLil]

Hello,

I'm a bit stuck on an export of a query and I feel like what I'm missing is probably obvious, but since I'm here I clearly can't see what it is I've missed. I have a form to export records based on two sets of combo boxes. One drop down chooses the field, the second allows you to input the search criteria. You can then select AND/OR check boxes to further filter the data. Then there is an Export button. I'm able to get the first filter to work based on the 1st combo and text box, but it doesn't include records using the second combo and text box. Help would greatly be appreciated Thanks

Private Sub cmdExport_Click()

Dim strOrder As String
Dim strFile As String
Dim strWhere As String
Dim strJoinType As String
Dim strFilter As String
Dim strInputFileName As String
Dim i As Integer
Dim j As Integer
Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb

On Error GoTo ErrorHandler
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:="Save as ...", _
Flags:=ahtOFN_HIDEREADONLY)



Const conMAXCONTROLS = 2
strSQL = "Select * "
For i = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
If Me("cbxFld" & i).Enabled Then
If i > 0 Then
If Me("opgClauseType" & i) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If
End If


strOrder = "ORDER BY " & Me.cbxOrder & ";"
strWhere = strWhere & strJoinType & Me("cbxFld" & i) & " LIKE '" & Me("txtSearch" & i) & "'"
strSQL = "select * from qry015T010GenotypeSearch where " & strWhere & strOrder



strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strQDF, strInputFileName, True, "Sheet1"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

Next
ErrorHandler:
If err = cdlCancel Then
DoCmd.CancelEvent
End If

End Sub

 

[boblarson]

If you filter the form itself then you can just use my code here

http://www.btabdevelopment.com/ts/default.aspx?PageId=47

to send the form's currently filtered recordset to Excel. Quick and painless.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[GreenLil]

Thank you for the code.
I'm trying it and not sure how to make it work properly. I copy and pasted to create the Module. The only thing I changed frm to my subform name.

Then I put the following in an onClick button on the main form (the filtered records have been displayed on a subform):
Call Send2Excel(Me.SubformControl.Form, "Sheet1")

It is giving me a compile error: method or data member not found and is referring to the .subformcontrol

I've never used this before so I'm not sure if there is something else I'm supposed to include here?

Thank you so much for your help

 

[boblarson]

Okay, first of all do NOT change any of the code I provided (except the formatting stuff inside if you want different formatting). It is set up the way it works.

Second, you would call it like you have it but you use the NAME Of the subform control (control on the main form which HOUSES the subform, not the name of the subform itself unless they share the exact same name. See the first screenshot here:

http://www.btabdevelopment.com/ts/ewtrhtrts

which shows what the subform control and its name are.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[GreenLil]

Sorry I was confused by the statement
'frm is the name of the form you want to send to Excel
I thought I was to change frm to my subform name.

Now it is working perfectly, thank you so much for your help it is greatly appreciated!

 

[boblarson]

Cool, glad I could help.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com