Exporting Records to Excel 1

[FrankMars]

Hello - I have several fields on a main form (asfAddressAll) that filter records to a continuous subform (asfAddressAllSubform). I would like to send the filtered records on the subform to Excel. How can I accomplish this? The following is the code used on buttons in the main form for filtering. Thank you in advance.

Dim mySearch As String

mySearch = "Select * from asqAddressAll where 1=1 "

If Not IsNull(Me.cboxStreetFilter) Then
  mySearch = mySearch & " and [Street Name] = '" & Me.cboxStreetFilter & "' "
End If

If Not IsNull(Me.cboxTownFilter) Then
  mySearch = mySearch & " and Town = '" & Me.cboxTownFilter & "' "
End If

If Not IsNull(Me.cboxSectionFilter) Then
  mySearch = mySearch & " and Section = '" & Me.cboxSectionFilter & "' "
End If

If Not IsNull(Me.cboxNSFilter) Then
  mySearch = mySearch & " and [North or South] = '" & Me.cboxNSFilter & "' "
End If

If Not IsNull(Me.cboxPRCFilter) Then
  mySearch = mySearch & " and PRC = '" & Me.cboxPRCFilter & "' "
End If

Me.asfAddressAllSubform.Form.RecordSource = mySearch
Me.asfAddressAllSubform.Form.Requery

 

[dhookom]

You can update the SQL property of a saved query with mysearch and export the query.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[FrankMars]

Thanks Duane. I understand what you're suggesting to do but I'm unable to write the code in vba on a button to update the SQL property of a saved query with "mySearch". How do I do that? I am able to write the code to export the saved query to Excel as shown below. Thanks again.

Dim FilePath As String
Dim FileName As String

FilePath = Environ("userprofile") & "\Desktop\Results"
FileName = FilePath & ".xls"

DoCmd.OutputTo acOutputQuery, "asqAddressAllmySearch", acFormatXLS, FileName, , , , acExportQualityPrint

 

[Andrzejek]

This link may be helpful: DoCmd.OutputTo + With a SQL Command + VBA

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

This FAQ shows how to update the SQL property.

https://www.tek-tips.com/faqs.cfm?fid=7433

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[FrankMars]

Thanks Andy and thanks Duane. Duane, your code worked great. I wouldn't have gotten there without it. Thanks again.

 

[Andrzejek]

You could post here your code so others can benefit.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson