Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Exporting Records to Excel 1

Status
Not open for further replies.

FrankMars

Technical User
Dec 20, 2010
67
US
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.

Code:
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
 
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
 
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.

Code:
Dim FilePath As String
Dim FileName As String

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

DoCmd.OutputTo acOutputQuery, "asqAddressAllmySearch", acFormatXLS, FileName, , , , acExportQualityPrint
 
Thanks Andy and thanks Duane. Duane, your code worked great. I wouldn't have gotten there without it. Thanks again.
 
You could post here your code so others can benefit.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top