Export Access into multiple excel files based on field value

[turntabl1st]

I have a service database that when a button is pressed I would like multiple excel spreadsheets to auto-generate to a defined location based on 2 feild values in 2 separate tables

Requirements:
1. the first value will be territory ID and I would like every unique value in this field to produce and new excel file


2. the second value Instrument Type is a lookup feild on a separate table and i would like each unique value to be a separate tab in each of the workbooks that was previously produced


here is an example SQL statement for one tab on one workbook:

SELECT Instruments.Institution, Instruments.Territory, Instruments.LastName, Instruments.FirstName, Instruments.SerialNum, Instruments.PurchaseDate, Instruments.WarrantyPeriodStart, Instruments.WarrantyPeriodEnd, Instruments.WarrantyQuoted, Instruments.AnnualPM, Instruments.AnnualPMStatus, Instruments.[6MonthPMMonth], Instruments.[6MonthPMStatus], Instruments.[2013Service], Instruments.[2012Service], Instruments.[2011Service], Instruments.[2010Service], Instruments.[2009Service], Instruments.Notes, Instruments.City, Instruments.State, Instruments.Zip, Instruments.Instruments, Instruments.WarrantyType, IntrumentTypes.Instruments
FROM IntrumentTypes INNER JOIN Instruments ON IntrumentTypes.ID = Instruments.Instruments
WHERE (((Instruments.Territory)=103) AND ((IntrumentTypes.Instruments)="autoMACSClassic"));

Here is how i have previously generated the reports before many more territories were added and automation became a necessity

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"qryautoMACSclassic", strPath, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"qryautoMACSpro", strPath, True
End If

I did find this but do not know how relevant to my situation it is http://www.accessmvp.com/KDSnell/EXC...ExportSepFiles

any help would be greatly appreciated

[June7]

Looks very relevant.

Modifying query with QueryDefs collection is one approach, maybe the easiest.

[turntabl1st]

I am not going to lie, I am still extremely confused on how to get this up and running. I keep getting various errors.

My Tables are: Instruments, InstrumentTypes
My Fields are: Instruments.Territory, Instruments.Instruments, IntrumentTypes.Instruments

[June7]

What errors? Cite the exact message.

Post your code or provide db for analysis. Follow instructions at bottom of my post.

[turntabl1st]

Here is the database. I would like this to happen on a button press and ideally export depending if the chkbox is pressed for that territory. Thanks again for all the help as I have been pretty lost !

[June7]

Don't see that code in your db. I want to help but not build for you. Judging by the code already in the db you have enough understanding of VBA to adapt a given example. Attempt code and when you have specific issue, post question on it.

[turntabl1st]

Sorry I removed as much as of the database as possible(tables, queries, and forms) and removed the code that did not work from the button. I merely left the most necessary tables and the new form that I wanted to use for the button. I did not want my company's information out there like that. Thank you either way

[June7]

I understand removing real data as structure is what we are analyzing (a few dummy records for testing is helpful). Can't analyze your efforts if you remove it.

Learn debug techniques. Review link at bottom of my post.