Export to Excel using a combo box to filter the query

[rilkyn]

Hi

I am exporting a query from a form, via a button. The query exported is used to populate the open form but the query output is filtered by a combo box.
What I want to do is apply the same filter to the export so that what is seen in the form is also seen in the export.

The code I use for export is as follows:

'Export UK Application Health Check to Excel spreadsheet format '

DoCmd.OutputTo acQuery, "App Health Check FSL2 query", "MicrosoftExcel(*.xls)", "", False, ""

The combo box is Combo1.

Any advice would be appreciated.

Thanks

 

[Remou]

You can build the export query using the form recordset.

'Assuming the record sourec is a table or query
strSQL="Select * From " & Me.RecordSource & " Where " & Me.Filter
If DLookup("Name", "MSysObjects", "Name= 'tmpApp Health Check FSL2 query'") <> "" Then
    Set qdf = CurrentDb.QueryDefs("tmpApp Health Check FSL2 query")
    qdf.SQL = strSQL
Else
    Set qdf = CurrentDb.CreateQueryDef("tmpApp Health Check FSL2 query", strSQL)
End If

 

[rilkyn]

Thank for your help but I am still having problems. The full code currently looks like this:

Dim StrSQL As String
Dim qdf As String


'Create temporary query using Combo1 filter value
StrSQL = "Select * From " & Me.[App Health Check FSL2 query] & " Where " & Me.Combo1
If DLookup("Name", "MSysObjects", "Name= 'tmpApp Health Check FSL2 query'") <> "" Then
Set qdf = CurrentDb.QueryDefs("tmpApp Health Check FSL2 query")
qdf.SQL = StrSQL
Else
Set qdf = CurrentDb.CreateQueryDef("tmpApp Health Check FSL2 query", StrSQL)
End If

'Export UK Application Health Check to Excel spreadsheet format '


DoCmd.OutputTo acQuery, "tmpApp Health Check FSL2 query", "MicrosoftExcel(*.xls)", "", False, ""



I am getting a Compile error - 'Object required'
I am also not clear on how the code knows that the combo1 filter value is compared to the [APPL] column in the query.

 

[rilkyn]

Ah

Figured it out.

Changed to the following:

Dim StrSQL As String
Dim qdf As QueryDef

'Create temporary query using Combo1 filter value
StrSQL = "Select * From [App Health Check FSL2 query] Where [APPL] = Forms![App Health Check form FSL2]![Combo1]"

And works fine

Thanks Remou for showing me the right direction.