Stuck on code

[JoJo6205]

Hi All,

After more digging, I found this code that does what I need.

All data from FBKO_Query1 should be exported into separate workbooks - it works. The query contains only active suppliers but when the workbooks are exported, all suppliers are exported (606 to be exact) so I'm ending up with a lot of empty workbooks.

All weekend I've been working on exporting only those suppliers that are current but cannot do so. How do I update the code to export only those suppliers in the FBKO_Query1?



Option Explicit
Option Compare Database


Sub ExportCustData()
Dim blnReachedEnd As Boolean
Dim strCurrCust As String, strFilePath As String
Dim strQueryDefName As String, strSummaryQuery As String
Dim strDetailQuery As String, strSummaryField As String


Dim rstCustomers As Recordset
Dim qdfCustData As QueryDef


'***** The file path for where you want data exported goes here - make sure to put a backslash at the end! *****
strFilePath = "N:\Merch-Global_Supply\Inventory_Planning\Supply_Planning\R egional Purchasing\Buyer Folders\Staci\suppliers\"

'************************************************* ************************************
'* You should have a query that summarizes the value you want to base your export on *
'* for example, if you want a spreadsheet per customer id you will build a query *
'* that groups by the customer id field. This can be a basic single-field query. *
'************************************************* ************************************
strSummaryQuery = "SuppliersQuery1"

'***** This is where you specify the query that is summarized in the summary query *****
strDetailQuery = "FBKO_query1"

'***** This is where you tell the code what field you are summarizing on *****
strSummaryField = "Vendor Name"

'***** The code adds a query which will be deleted at the end. Enter the name here. *****
strQueryDefName = "Query3"


Set qdfCustData = CurrentDb.CreateQueryDef(strQueryDefName)

Set rstCustomers = CurrentDb.OpenRecordset(strSummaryQuery)
blnReachedEnd = rstCustomers.EOF
Do While blnReachedEnd = False
strCurrCust = rstCustomers!Supplier


qdfCustData.SQL = "SELECT * FROM [" & strDetailQuery & "] WHERE ([" & strSummaryField & "] = " _
& Chr$(34) & strCurrCust & Chr$(34) & ");"

DoCmd.OutputTo acOutputQuery, qdfCustData.name, acFormatXLS, strFilePath _
& strCurrCust & ".xls", False

rstCustomers.MoveNext






blnReachedEnd = rstCustomers.EOF
If blnReachedEnd Then rstCustomers.MovePrevious
Loop

rstCustomers.Close

CurrentDb.QueryDefs(strQueryDefName).Close
CurrentDb.QueryDefs.Delete strQueryDefName
End Sub

[June7]

I would not modify QueryDef.

Consider using DoCmd.RunCommand acCmdExportXML
Review http://bytes.com/topic/access/answer...eter-query-xml

[John_G]

Your problem is not is the code you have here but in the number of records in strSummaryQuery.

The loop count, and hence the number of worksheets created, controlled by the number of records in that query.

You have two options -

One is to fix strSummaryQuery to return only customer records you want
The other is to put in a check to see how many records qdfCustData.SQL will identify in FBKO_query1, and only create a worksheet if there are some records to output. (DCount function should work)

[JoJo6205]

I've been working on the Dcount but have realized there is another issue. The Dcount is running through strDetailQuery as it should but that query has many lines related to each supplier. In other words, one supplier may have 30 entries, another supplier may have 20 entries, etc. and the DCount is running through each line.

I assigned an ID to each supplier in the strSummaryQuery thinking I could to a group by but still can't get it to work. Not sure what I'm doing wrong. Any ideas?

[John_G]

The Dcount is running through strDetailQuery as it should but that query has many lines related to each supplier. In other words, one supplier may have 30 entries, another supplier may have 20 entries, etc

The exact number of entries doesn't really matter - all you need to know is that there is at least one, i.e. the DCount is > 0.