Paulywally
New member
- Local time
- Today, 14:00
- Joined
- Oct 25, 2019
- Messages
- 3
Good morning.
I am attempting to use Access again after several years away. Many thanks in advance for any help you are able to give (and apologies in advance if my explanation below lacks sufficient information or is confusing).
I would like to export the results of a filtered form to an Existing Excel spreadsheet and then, ideally, save it as a new workbook with a file name derived from one or more of the fields in that form.
I have used the code outlined at the top of this thread (#1). It works but it doesn’t meet my needs as it creates a new spreadsheet from scratch (rather than putting the data into an existing spreadsheet).
I have tried various solutions that I have seen posted around the internet that involve creating a new temporary query based on the filtered form and using transferspreadsheet to transfer the data. None of these have managed to create a new query from the filtered form.
If it helps, my database is structured as follows:
Tables: tblOrganisation, tblService, tblMonitoring
tblOrganisation has a one to many relationship with tblService, using the Autonumber primary key from tblOrganisation.
tblService has a one to many relationship with tblMonitoring, using the Autonumber primary key from tblMonitoring
Querys: qryMonitoringSheet.
qryMonitoringSheet was designed using simple query wizard. It has fields from all three tables, including ‘MonID’ (the Autonumber primary key for the Monitoring table).
When open the query it displays, as I would expect an unfiltered list of entries in from tblMonitoring alongside the associated fields from tblService and tblOrganisation.
Forms: frmOrganisation,subfrmService,subfrmMonitoring, frmMonitoringSheet
frmOrganisation is used for entering new records and has tblOrganisation as its source.
subfrmService is a data entry form that sits within frmOrganisation and has tblService as its source.
subfrmMonitoring is a ‘mutiple items’ form, used for data entry that sits within subfrmService and has tblService as it’s source. On each row of subfrmMonitoring there is a hidden text box ‘txtMonID’ that has the primary key for tblMonitoring (MonID) as it’s control source. Next to that there is a button. On click, the button executes the following:
DoCmd.OpenForm "frmMonitoringSheet", _
WhereCondition:="MonID=" & Me.txtMonID
frmMonitoringSheet has qryMonitoringSheet as its source and displays the fields from that query. When it is opened using the button on subfrmMonitoring, it displays one filtered result. There is a button on frmMonitoringSheet. I would like that button to trigger a process that exports the filtered result to an existing or template excel spreadsheet and then saves a copy with a new file name based on the data from the filtered result in the same location.
Many thanks in advance for any help.
I am attempting to use Access again after several years away. Many thanks in advance for any help you are able to give (and apologies in advance if my explanation below lacks sufficient information or is confusing).
I would like to export the results of a filtered form to an Existing Excel spreadsheet and then, ideally, save it as a new workbook with a file name derived from one or more of the fields in that form.
I have used the code outlined at the top of this thread (#1). It works but it doesn’t meet my needs as it creates a new spreadsheet from scratch (rather than putting the data into an existing spreadsheet).
I have tried various solutions that I have seen posted around the internet that involve creating a new temporary query based on the filtered form and using transferspreadsheet to transfer the data. None of these have managed to create a new query from the filtered form.
If it helps, my database is structured as follows:
Tables: tblOrganisation, tblService, tblMonitoring
tblOrganisation has a one to many relationship with tblService, using the Autonumber primary key from tblOrganisation.
tblService has a one to many relationship with tblMonitoring, using the Autonumber primary key from tblMonitoring
Querys: qryMonitoringSheet.
qryMonitoringSheet was designed using simple query wizard. It has fields from all three tables, including ‘MonID’ (the Autonumber primary key for the Monitoring table).
When open the query it displays, as I would expect an unfiltered list of entries in from tblMonitoring alongside the associated fields from tblService and tblOrganisation.
Forms: frmOrganisation,subfrmService,subfrmMonitoring, frmMonitoringSheet
frmOrganisation is used for entering new records and has tblOrganisation as its source.
subfrmService is a data entry form that sits within frmOrganisation and has tblService as its source.
subfrmMonitoring is a ‘mutiple items’ form, used for data entry that sits within subfrmService and has tblService as it’s source. On each row of subfrmMonitoring there is a hidden text box ‘txtMonID’ that has the primary key for tblMonitoring (MonID) as it’s control source. Next to that there is a button. On click, the button executes the following:
DoCmd.OpenForm "frmMonitoringSheet", _
WhereCondition:="MonID=" & Me.txtMonID
frmMonitoringSheet has qryMonitoringSheet as its source and displays the fields from that query. When it is opened using the button on subfrmMonitoring, it displays one filtered result. There is a button on frmMonitoringSheet. I would like that button to trigger a process that exports the filtered result to an existing or template excel spreadsheet and then saves a copy with a new file name based on the data from the filtered result in the same location.
Many thanks in advance for any help.
Last edited:
