Hi,
I have an Access DB that has 4 queries and one form. Depending on what the user does in the form will determine which query is run. This is controlled by a macro that looks at the state of three tick boxes and runs a query accordingly.
What I would like to know is, can I have a single button that will export only the query that has been run? I don't mind if it's a two step process (e.g. run the query from the form then go back to the form and export the open query).
I'm a bit of an Access noob and I'm not sure how to do this. I have found a way to export based on query name but this isn't suitable as it will export the named query regardless of which one has been opened/run.
Here's my (mostly borrowed) code so far:
This will export any query with Supplement and Extra in the name which isn't quite what I'm looking for.
Any help would be greatly appreciated.
Thanks,
Haydn
I have an Access DB that has 4 queries and one form. Depending on what the user does in the form will determine which query is run. This is controlled by a macro that looks at the state of three tick boxes and runs a query accordingly.
What I would like to know is, can I have a single button that will export only the query that has been run? I don't mind if it's a two step process (e.g. run the query from the form then go back to the form and export the open query).
I'm a bit of an Access noob and I'm not sure how to do this. I have found a way to export based on query name but this isn't suitable as it will export the named query regardless of which one has been opened/run.
Here's my (mostly borrowed) code so far:
Code:
Public Sub testexport_click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim dlgSaveAs As FileDialog 'Create A FileDialog Object
Dim strFileSaveName As String
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs) 'Set it to be the SaveAs FileDialog
With dlgSaveAs
.InitialFileName = CurrentProject.Path & "\" & "Direct Booking Search " & Format(Now, "dd-mm-yyyy") & ".xlsx" 'Set the default filename and directory
.InitialView = msoFileDialogViewDetails 'Set the default folder view
.Title = "Choose A File Name" 'Set your own dialog title
End With
Set db = CurrentDb()
For Each qdf In db.QueryDefs
If InStr(qdf.Name, "Supplement") <> 0 Then 'If query name contains Supplement
DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLSX, strFileSaveName, False
End If
If InStr(qdf.Name, "Extra") <> 0 Then 'If query name contains Extra
DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLSX, strFileSaveName, False
End If
Next qdf
Set qdf = Nothing
Set db = Nothing
MsgBox "Your data has been exported", vbOKOnly
End Sub
This will export any query with Supplement and Extra in the name which isn't quite what I'm looking for.
Any help would be greatly appreciated.
Thanks,
Haydn