Output To Naming problem

[Jeff C]

Every morning a large mainframe report is spooled to a text file. Using a
data extraction program, I have the report dropped into an Excel Spreadsheet.
Each morning it overwrites itself with a new report. I link to this
spreadsheet from Access. I built a macro that I will schedule to run every
morning after the report drops. The macro will open a form, close the form,
and quit the application.

I have a query built:

SELECT DISTINCT AncChgRpt.Dept
FROM AncChgRpt;

The On Open Event of the form:
Dim Dept As ADODB.Recordset
Set Dept = New ADODB.Recordset

Dept.Open "QDepts", CurrentProject.Connection, adOpenStatic

Do Until Dept.EOF

[Forms]![FRpts]![txtDept].Value = QDepts![Dept]

On Error Resume Next
DoCmd.OutputTo acQuery, "QReport", "MicrosoftExcelBiff8(*.xls)",
"U:\Directory1\Directory2\[Forms]![FRpts].[txtDept].xls", False, "", 0
On Error Resume Next
Dept.MoveNext
Loop
End Sub

I am attempting to get a separate spreadsheet for each department, named
with the name of each department but I cannot come up with the correct way to
pull the name of the department from the textbox on the form and use it as a
text string as the name of the spreadsheet.

Once I get this I have a VBS routine that will copy the sheet in each
workbook adding it as a new sheet in the departments workbook. Then I delete
everything so the sequence runs the next morning.

Can someone help me with this?

Thanks in advance.

 

[Ken Snell \(MVP\)]

Concatentate the value into the string:

DoCmd.OutputTo acQuery, "QReport", "MicrosoftExcelBiff8(*.xls)",
"U:\Directory1\Directory2\" & [Forms]![FRpts].[txtDept] & ".xls", False, "",
0


I suggest that you consider using DoCmd.TransferSpreadsheet for the output.
It supports newer versions of EXCEL in terms of number of records,
memo-length strings, etc.

 

[Douglas J. Steele]

Assuming you're trying to include whatever's in txtDept on the form as part
of the file name:

"U:\Directory1\Directory2\" & [Forms]![FRpts].[txtDept] & ".xls"

 

[Jeff C]

Thanks guys

I now have :

Dim Dept As ADODB.Recordset
Set Dept = New ADODB.Recordset

Dept.Open "QDepts", CurrentProject.Connection, adOpenStatic

Do Until Dept.EOF


Me.txtDept.Value = QDepts![Dept]

On Error Resume Next
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
"U:\DocDirectReports\PACPAJ01\" & [Forms]![FRpts].[txtDept] & ".xls"
On Error Resume Next
Dept.MoveNext
Loop
End Sub


I am hanging here now: Me.txtDept.Value = QDepts![Dept]

 

[Ken Snell \(MVP\)]

If you're opening a recordset, why bother to write the value of the Dept
field to the form just so that you can then use it in the
TransferSpreadsheet action? Do it directly:


DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
"U:\DocDirectReports\PACPAJ01\" & QDepts![Dept] & ".xls"



Now, define "hanging here" for us -- are you getting an error message from
VBE? is nothing happening? more details, please.

 

[Jeff C]

The Query "QReports" is using the textbox on the form as the criteria for the
department.

The code is placed in the onopen event of the form and I get a run time
error "Object Required" on that particular line.

I Do apprreciate your help.
--
Jeff C
Live Well .. Be Happy In All You Do

If you're opening a recordset, why bother to write the value of the Dept
field to the form just so that you can then use it in the
TransferSpreadsheet action? Do it directly:


DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
"U:\DocDirectReports\PACPAJ01\" & QDepts![Dept] & ".xls"



Now, define "hanging here" for us -- are you getting an error message from
VBE? is nothing happening? more details, please.
--

Ken Snell
<MS ACCESS MVP>

Thanks guys

I now have :

Dim Dept As ADODB.Recordset
Set Dept = New ADODB.Recordset

Dept.Open "QDepts", CurrentProject.Connection, adOpenStatic

Do Until Dept.EOF


Me.txtDept.Value = QDepts![Dept]

On Error Resume Next
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
"U:\DocDirectReports\PACPAJ01\" & [Forms]![FRpts].[txtDept] & ".xls"
On Error Resume Next
Dept.MoveNext
Loop
End Sub


I am hanging here now: Me.txtDept.Value = QDepts![Dept]

 

[Ken Snell \(MVP\)]

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

The Query "QReports" is using the textbox on the form as the criteria for
the
department.
OK.




The code is placed in the onopen event of the form and I get a run time
error "Object Required" on that particular line.

Open event of a form is way too soon to try to write data to and read data
from a form's controls. Most/all of the controls and data are not available
to the form during the Open event; hence, you're getting the Object Required
error because the object does not exist (has not been instantiated) yet. The
Load event is the soonest event where I'd try to do this.

But, why are you using the opening of a form to trigger this code? It
probably would be better to open the form and click a button on the form to
run the code -- that will ensure that everything is ready to go.

Or, let's back up and consider running the code in the procedure that is
opening the form. It's possible to avoid the need for the query to read a
value from the form's control, and instead provide the value directly to the
query -- all in VBA code. Tell us more about your process and setup, and we
can help you make this more efficient and useful, I believe.

I Do apprreciate your help.
--
Jeff C
Live Well .. Be Happy In All You Do

If you're opening a recordset, why bother to write the value of the Dept
field to the form just so that you can then use it in the
TransferSpreadsheet action? Do it directly:


DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
"U:\DocDirectReports\PACPAJ01\" & QDepts![Dept] & ".xls"



Now, define "hanging here" for us -- are you getting an error message
from
VBE? is nothing happening? more details, please.
--

Ken Snell
<MS ACCESS MVP>

Thanks guys

I now have :

Dim Dept As ADODB.Recordset
Set Dept = New ADODB.Recordset

Dept.Open "QDepts", CurrentProject.Connection, adOpenStatic

Do Until Dept.EOF


Me.txtDept.Value = QDepts![Dept]

On Error Resume Next
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
"U:\DocDirectReports\PACPAJ01\" & [Forms]![FRpts].[txtDept] & ".xls"
On Error Resume Next
Dept.MoveNext
Loop
End Sub


I am hanging here now: Me.txtDept.Value = QDepts![Dept]

 

[Jeff C]

Thanks Ken:

The data in the linked Spreadsheet is fresh every morning. I am using a
"Distinct" Query "QDepts" as the recordset because the depts reported every
day may change.
"QReport" is a select query simply returning every record in the linked table.

The form uses QDepts as it's control source and has a textbox for the dept
field.

QReport uses this textbox as the criteria for the dept.

The idea is to loop through every dept listed in Qdept returning matching
records from QReport and sending each set to it's own idividual workbook.
I am very open to learning the best way to do this sort of thing.

Thanks

--
Jeff C
Live Well .. Be Happy In All You Do

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

The Query "QReports" is using the textbox on the form as the criteria for
the
department.
OK.




The code is placed in the onopen event of the form and I get a run time
error "Object Required" on that particular line.

Open event of a form is way too soon to try to write data to and read data
from a form's controls. Most/all of the controls and data are not available
to the form during the Open event; hence, you're getting the Object Required
error because the object does not exist (has not been instantiated) yet. The
Load event is the soonest event where I'd try to do this.

But, why are you using the opening of a form to trigger this code? It
probably would be better to open the form and click a button on the form to
run the code -- that will ensure that everything is ready to go.

Or, let's back up and consider running the code in the procedure that is
opening the form. It's possible to avoid the need for the query to read a
value from the form's control, and instead provide the value directly to the
query -- all in VBA code. Tell us more about your process and setup, and we
can help you make this more efficient and useful, I believe.

I Do apprreciate your help.
--
Jeff C
Live Well .. Be Happy In All You Do

If you're opening a recordset, why bother to write the value of the Dept
field to the form just so that you can then use it in the
TransferSpreadsheet action? Do it directly:


DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
"U:\DocDirectReports\PACPAJ01\" & QDepts![Dept] & ".xls"



Now, define "hanging here" for us -- are you getting an error message
from
VBE? is nothing happening? more details, please.
--

Ken Snell
<MS ACCESS MVP>




Thanks guys

I now have :

Dim Dept As ADODB.Recordset
Set Dept = New ADODB.Recordset

Dept.Open "QDepts", CurrentProject.Connection, adOpenStatic

Do Until Dept.EOF


Me.txtDept.Value = QDepts![Dept]

On Error Resume Next
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "QReport",
"U:\DocDirectReports\PACPAJ01\" & [Forms]![FRpts].[txtDept] & ".xls"
On Error Resume Next
Dept.MoveNext
Loop
End Sub


I am hanging here now: Me.txtDept.Value = QDepts![Dept]

 

[Ken Snell \(MVP\)]

OK - before I suggest some code to do this, how are you opening the form
now? Is it a startup form? or is it being opened by the user from another
form/switchboard?

 

[Jeff C]

This process has no user interaction. It begins with the mainframe report
dropping down.

Because I can schedule macros to run with a simple command line, I used a
macro to open and close the simple form and running the code in the on open
event which you suggested I change earlier. When I do this sort of thing I
usually end up scheduling a batch file, a macro from Access, and then a vbs
script to finish the process.
--
Jeff C
Live Well .. Be Happy In All You Do

OK - before I suggest some code to do this, how are you opening the form
now? Is it a startup form? or is it being opened by the user from another
form/switchboard?

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell \(MVP\)]

OK. Here is what I suggest be done -- note that I am assuming that the Dept
field in QDepts query is numeric (if it's text, you'll need to delimit its
value with ' characters in the Replace statement -- and note that I am
assuming that you do not want to delete the parameter from your QReport
query because you may have need of it for other purposes -- and also note
that I have not tested this code, so it may need a bit of debugging:


(1) First, create a new, regular module (name the module "basExportData"),
and paste this VBA code in that module:

'Start of code
Public Function ExportDeptDataFromQReportQuery()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstDept As DAO.Recordset
Dim strSQL As String, strTemp As String, strQReportSQL As String
Dim strDeptValue As String

Const strQName As String = "zExportReportQuery"
Const strQReportParameterName As String = "[Forms]![FRpts].[txtDept]"

Set dbs = CurrentDb

' Get SQL string from QReport query
strQReportSQL = dbs.QueryDefs("QReport").SQL

' Create temporary query that will be used for exporting data
Set qdf = dbs.CreateQueryDef(strQName, strQReportSQL)
qdf.Close
strTemp = qdf.Name
Set qdf = Nothing

' Get list of Depts
Set rstDept = dbs.OpenRecordset("QDepts", dbOpenDynaset, dbReadOnly)

' Now loop through Depts and create a query for each value
' so that the data can be exported
If rstDept.EOF = False And rstDept.BOF = False Then
rstDept.MoveFirst
Do While rstDept.EOF = False
strDeptValue = CStr(rstDept![Dept])
' Replace "parameter string" from QReport query's SQL with actual value
' of Dept field
' use this code step if Dept is numeric (don't use the other one
below)
strSQL = Replace(strQReportSQL, strQReportParameterName, _
strDeptValue, 1, -1, vbTextCompare)
' use this code step if Dept is text (don't use the other one above)
' strSQL = Replace(strQReportSQL, strQReportParameterName, _
' "'" & strDeptValue & "'", 1, -1, vbTextCompare)
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strDeptValue
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp,
"U:\DocDirectReports\PACPAJ01\" & strDeptValue & ".xls"
rstDept.MoveNext
Loop
End If

rstDept.Close
Set rstDept = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code


(2) Second, in the macro that you're running when the .mdb file is first
opened, delete the action step for opening the current form. Replace that
action step with
Action: RunCode
Function Name: ExportDeptDataFromQReportQuery()


What the above setup will do is run the ExportDeptDataFromQReportQuery
function via your macro. This function creates a temporary query in your
file, giving it a temporary name. Then it opens the recordset of Dept
values. It loops through the Dept recordset. For each Dept value, it creates
a department-specific SQL statement for each Dept value, assigns that SQL
statement to the temporary query, renames the temporary query to include the
Dept value in the name, and then exports that temporary query to give you an
EXCEL file with just that Dept value's data. When all Dept values have been
used, it deletes the temporary query.

If you do not need to keep the parameter in QReport query for other
purposes, then you could delete the "[Forms]![FRpts].[txtDept]" parameter
from that query, let the VBA code create a new query based on QReport, and
add a WHERE clause that would filter the data for a Dept value, and export
the filtered data. It would be very similar to what I've outlined above,
except with a few tweaks.

Let me know if you find errors or problems with this code/setup.
--

Ken Snell
<MS ACCESS MVP>

This process has no user interaction. It begins with the mainframe report
dropping down.

Because I can schedule macros to run with a simple command line, I used a
macro to open and close the simple form and running the code in the on
open
event which you suggested I change earlier. When I do this sort of thing
I
usually end up scheduling a batch file, a macro from Access, and then a
vbs
script to finish the process.

 

[Jeff C]

Wow, thank you for this help. I will work with this trying to get it to run
and will let you know. I want to step through this slowly to follow what you
are doing here.
--
Jeff C
Live Well .. Be Happy In All You Do

OK. Here is what I suggest be done -- note that I am assuming that the Dept
field in QDepts query is numeric (if it's text, you'll need to delimit its
value with ' characters in the Replace statement -- and note that I am
assuming that you do not want to delete the parameter from your QReport
query because you may have need of it for other purposes -- and also note
that I have not tested this code, so it may need a bit of debugging:


(1) First, create a new, regular module (name the module "basExportData"),
and paste this VBA code in that module:

'Start of code
Public Function ExportDeptDataFromQReportQuery()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstDept As DAO.Recordset
Dim strSQL As String, strTemp As String, strQReportSQL As String
Dim strDeptValue As String

Const strQName As String = "zExportReportQuery"
Const strQReportParameterName As String = "[Forms]![FRpts].[txtDept]"

Set dbs = CurrentDb

' Get SQL string from QReport query
strQReportSQL = dbs.QueryDefs("QReport").SQL

' Create temporary query that will be used for exporting data
Set qdf = dbs.CreateQueryDef(strQName, strQReportSQL)
qdf.Close
strTemp = qdf.Name
Set qdf = Nothing

' Get list of Depts
Set rstDept = dbs.OpenRecordset("QDepts", dbOpenDynaset, dbReadOnly)

' Now loop through Depts and create a query for each value
' so that the data can be exported
If rstDept.EOF = False And rstDept.BOF = False Then
rstDept.MoveFirst
Do While rstDept.EOF = False
strDeptValue = CStr(rstDept![Dept])
' Replace "parameter string" from QReport query's SQL with actual value
' of Dept field
' use this code step if Dept is numeric (don't use the other one
below)
strSQL = Replace(strQReportSQL, strQReportParameterName, _
strDeptValue, 1, -1, vbTextCompare)
' use this code step if Dept is text (don't use the other one above)
' strSQL = Replace(strQReportSQL, strQReportParameterName, _
' "'" & strDeptValue & "'", 1, -1, vbTextCompare)
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strDeptValue
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp,
"U:\DocDirectReports\PACPAJ01\" & strDeptValue & ".xls"
rstDept.MoveNext
Loop
End If

rstDept.Close
Set rstDept = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code


(2) Second, in the macro that you're running when the .mdb file is first
opened, delete the action step for opening the current form. Replace that
action step with
Action: RunCode
Function Name: ExportDeptDataFromQReportQuery()


What the above setup will do is run the ExportDeptDataFromQReportQuery
function via your macro. This function creates a temporary query in your
file, giving it a temporary name. Then it opens the recordset of Dept
values. It loops through the Dept recordset. For each Dept value, it creates
a department-specific SQL statement for each Dept value, assigns that SQL
statement to the temporary query, renames the temporary query to include the
Dept value in the name, and then exports that temporary query to give you an
EXCEL file with just that Dept value's data. When all Dept values have been
used, it deletes the temporary query.

If you do not need to keep the parameter in QReport query for other
purposes, then you could delete the "[Forms]![FRpts].[txtDept]" parameter
from that query, let the VBA code create a new query based on QReport, and
add a WHERE clause that would filter the data for a Dept value, and export
the filtered data. It would be very similar to what I've outlined above,
except with a few tweaks.

Let me know if you find errors or problems with this code/setup.
--

Ken Snell
<MS ACCESS MVP>

 

[Jeff C]

Ken: I have questions in line below. The dept field was originally text, I
used queries to qualify the dept as CDbl.
--
Jeff C
Live Well .. Be Happy In All You Do

OK. Here is what I suggest be done -- note that I am assuming that the Dept
field in QDepts query is numeric (if it's text, you'll need to delimit its
value with ' characters in the Replace statement -- and note that I am
assuming that you do not want to delete the parameter from your QReport
query because you may have need of it for other purposes -- and also note
that I have not tested this code, so it may need a bit of debugging:


(1) First, create a new, regular module (name the module "basExportData"),
and paste this VBA code in that module:

'Start of code
Public Function ExportDeptDataFromQReportQuery()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstDept As DAO.Recordset
Dim strSQL As String, strTemp As String, strQReportSQL As String
Dim strDeptValue As String

Const strQName As String = "zExportReportQuery"
Const strQReportParameterName As String = "[Forms]![FRpts].[txtDept]"

I am prompted for parameter: We use the above string but the form is not
open and does not load with data to use for parameter. How do we do this?

The sequence produces correctly named workbooks but they are empty of data
when I click throught them. I do not need to keep the queries.

Set dbs = CurrentDb

' Get SQL string from QReport query
strQReportSQL = dbs.QueryDefs("QReport").SQL

' Create temporary query that will be used for exporting data
Set qdf = dbs.CreateQueryDef(strQName, strQReportSQL)
qdf.Close
strTemp = qdf.Name
Set qdf = Nothing

' Get list of Depts
Set rstDept = dbs.OpenRecordset("QDepts", dbOpenDynaset, dbReadOnly)

' Now loop through Depts and create a query for each value
' so that the data can be exported
If rstDept.EOF = False And rstDept.BOF = False Then

rstDept.MoveFirst

Should this be MoveNext to make the values loop through the form?

Do While rstDept.EOF = False
strDeptValue = CStr(rstDept![Dept])
' Replace "parameter string" from QReport query's SQL with actual value
' of Dept field
' use this code step if Dept is numeric (don't use the other one
below)
strSQL = Replace(strQReportSQL, strQReportParameterName, _
strDeptValue, 1, -1, vbTextCompare)
' use this code step if Dept is text (don't use the other one above)
' strSQL = Replace(strQReportSQL, strQReportParameterName, _
' "'" & strDeptValue & "'", 1, -1, vbTextCompare)
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strDeptValue
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp,
"U:\DocDirectReports\PACPAJ01\" & strDeptValue & ".xls"
rstDept.MoveNext
Loop
End If

rstDept.Close
Set rstDept = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code


(2) Second, in the macro that you're running when the .mdb file is first
opened, delete the action step for opening the current form. Replace that
action step with
Action: RunCode
Function Name: ExportDeptDataFromQReportQuery()


What the above setup will do is run the ExportDeptDataFromQReportQuery
function via your macro. This function creates a temporary query in your
file, giving it a temporary name. Then it opens the recordset of Dept
values. It loops through the Dept recordset. For each Dept value, it creates
a department-specific SQL statement for each Dept value, assigns that SQL
statement to the temporary query, renames the temporary query to include the
Dept value in the name, and then exports that temporary query to give you an
EXCEL file with just that Dept value's data. When all Dept values have been
used, it deletes the temporary query.

If you do not need to keep the parameter in QReport query for other
purposes, then you could delete the "[Forms]![FRpts].[txtDept]" parameter
from that query, let the VBA code create a new query based on QReport, and
add a WHERE clause that would filter the data for a Dept value, and export
the filtered data. It would be very similar to what I've outlined above,
except with a few tweaks.

Let me know if you find errors or problems with this code/setup.

 

[Jeff C]

Still working on this and I think my problem is that I am not correctly
defining the criteria for Dept in QReport as this is being dynamically
copied, I am missing the correct dynamic for the Where Clause.???
--
Jeff C
Live Well .. Be Happy In All You Do

OK. Here is what I suggest be done -- note that I am assuming that the Dept
field in QDepts query is numeric (if it's text, you'll need to delimit its
value with ' characters in the Replace statement -- and note that I am
assuming that you do not want to delete the parameter from your QReport
query because you may have need of it for other purposes -- and also note
that I have not tested this code, so it may need a bit of debugging:


(1) First, create a new, regular module (name the module "basExportData"),
and paste this VBA code in that module:

'Start of code
Public Function ExportDeptDataFromQReportQuery()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstDept As DAO.Recordset
Dim strSQL As String, strTemp As String, strQReportSQL As String
Dim strDeptValue As String

Const strQName As String = "zExportReportQuery"
Const strQReportParameterName As String = "[Forms]![FRpts].[txtDept]"

Set dbs = CurrentDb

' Get SQL string from QReport query
strQReportSQL = dbs.QueryDefs("QReport").SQL

' Create temporary query that will be used for exporting data
Set qdf = dbs.CreateQueryDef(strQName, strQReportSQL)
qdf.Close
strTemp = qdf.Name
Set qdf = Nothing

' Get list of Depts
Set rstDept = dbs.OpenRecordset("QDepts", dbOpenDynaset, dbReadOnly)

' Now loop through Depts and create a query for each value
' so that the data can be exported
If rstDept.EOF = False And rstDept.BOF = False Then
rstDept.MoveFirst
Do While rstDept.EOF = False
strDeptValue = CStr(rstDept![Dept])
' Replace "parameter string" from QReport query's SQL with actual value
' of Dept field
' use this code step if Dept is numeric (don't use the other one
below)
strSQL = Replace(strQReportSQL, strQReportParameterName, _
strDeptValue, 1, -1, vbTextCompare)
' use this code step if Dept is text (don't use the other one above)
' strSQL = Replace(strQReportSQL, strQReportParameterName, _
' "'" & strDeptValue & "'", 1, -1, vbTextCompare)
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strDeptValue
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp,
"U:\DocDirectReports\PACPAJ01\" & strDeptValue & ".xls"
rstDept.MoveNext
Loop
End If

rstDept.Close
Set rstDept = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code


(2) Second, in the macro that you're running when the .mdb file is first
opened, delete the action step for opening the current form. Replace that
action step with
Action: RunCode
Function Name: ExportDeptDataFromQReportQuery()


What the above setup will do is run the ExportDeptDataFromQReportQuery
function via your macro. This function creates a temporary query in your
file, giving it a temporary name. Then it opens the recordset of Dept
values. It loops through the Dept recordset. For each Dept value, it creates
a department-specific SQL statement for each Dept value, assigns that SQL
statement to the temporary query, renames the temporary query to include the
Dept value in the name, and then exports that temporary query to give you an
EXCEL file with just that Dept value's data. When all Dept values have been
used, it deletes the temporary query.

If you do not need to keep the parameter in QReport query for other
purposes, then you could delete the "[Forms]![FRpts].[txtDept]" parameter
from that query, let the VBA code create a new query based on QReport, and
add a WHERE clause that would filter the data for a Dept value, and export
the filtered data. It would be very similar to what I've outlined above,
except with a few tweaks.

Let me know if you find errors or problems with this code/setup.
--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell \(MVP\)]

Answers to your questions first:

(1)***
The dept field was originally text, I
used queries to qualify the dept as CDbl.

(Answer) It would be very helpful if you could post some examples of the
values that are in Dept field (or the field that is modifying that field --
by CDbl?) in the QReport query. Why are you using CDbl to "change" the Dept
field's values? CDbl is giving you a floating point number that may be
difficult to "match" (i.e., using = operator in a query with it may not
yield a match that you and I "by eye" might think is a match); perhaps it'd
be better to use a different casting function (e.g., CLng?) or no casting
function at all. There is no problem with using the Dept value as a text
string for that matter, either -- likely there's no need to change it to a
numeric value at all.



(2)***

Const strQReportParameterName As String = "[Forms]![FRpts].[txtDept]"

I am prompted for parameter: We use the above string but the form is not
open and does not load with data to use for parameter. How do we do this?

(Answer) You don't say when the parameter prompt is being displayed, but I
assume it's occurring for each export action. If this is true, then most
likely the QReport query's parameter is not
[Forms]![FRpts].[txtDept]
but instead is something else -- perhaps (which would be more normal syntax)
[Forms]![FRpts]![txtDept]

In order to confirm and fix the problem, please post the entire SQL
statement of the QReport query so that we can see the exact parameter name
and on which field it's being used. Also, please indicate if we can change
QReport query by deleting the parameter from it entirely (see my
comment/question in my previous post about this).



(3)***

rstDept.MoveFirst

Should this be MoveNext to make the values loop through the form?

(Answer) No, this step correctly is .MoveFirst. Although it's normal for a
recordset that contains at least one record to be positioned at the first
record, I have an old habit of including code steps that explicitly do what
I want to do, instead of relying on default behaviors/values (you just never
know when a default behavior might be changed to a different behavior in a
newer version of ACCESS). Therefore, I added the .MoveFirst step, although
many others will say it's redundant, just so that I am sure that the
recordset is starting on the first record.


Let's see the SQL statement of the QReport query, and for the QDept query.
In the QDept query, what is the data type of the Dept field that that query
contains? It's critical that we match data types for this code to work --
data values that are string must be "matched" to string values, numbers must
be "matched" to numbers, etc.
--

Ken Snell
<MS ACCESS MVP>

Ken: I have questions in line below. The dept field was originally
text, I
used queries to qualify the dept as CDbl.
--
Jeff C
Live Well .. Be Happy In All You Do

OK. Here is what I suggest be done -- note that I am assuming that the
Dept
field in QDepts query is numeric (if it's text, you'll need to delimit
its
value with ' characters in the Replace statement -- and note that I am
assuming that you do not want to delete the parameter from your QReport
query because you may have need of it for other purposes -- and also note
that I have not tested this code, so it may need a bit of debugging:


(1) First, create a new, regular module (name the module
"basExportData"),
and paste this VBA code in that module:

'Start of code
Public Function ExportDeptDataFromQReportQuery()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstDept As DAO.Recordset
Dim strSQL As String, strTemp As String, strQReportSQL As String
Dim strDeptValue As String

Const strQName As String = "zExportReportQuery"
Const strQReportParameterName As String = "[Forms]![FRpts].[txtDept]"

I am prompted for parameter: We use the above string but the form is not
open and does not load with data to use for parameter. How do we do this?

The sequence produces correctly named workbooks but they are empty of data
when I click throught them. I do not need to keep the queries.

Set dbs = CurrentDb

' Get SQL string from QReport query
strQReportSQL = dbs.QueryDefs("QReport").SQL

' Create temporary query that will be used for exporting data
Set qdf = dbs.CreateQueryDef(strQName, strQReportSQL)
qdf.Close
strTemp = qdf.Name
Set qdf = Nothing

' Get list of Depts
Set rstDept = dbs.OpenRecordset("QDepts", dbOpenDynaset, dbReadOnly)

' Now loop through Depts and create a query for each value
' so that the data can be exported
If rstDept.EOF = False And rstDept.BOF = False Then

rstDept.MoveFirst

Should this be MoveNext to make the values loop through the form?

Do While rstDept.EOF = False
strDeptValue = CStr(rstDept![Dept])
' Replace "parameter string" from QReport query's SQL with actual value
' of Dept field
' use this code step if Dept is numeric (don't use the other one
below)
strSQL = Replace(strQReportSQL, strQReportParameterName, _
strDeptValue, 1, -1, vbTextCompare)
' use this code step if Dept is text (don't use the other one
above)
' strSQL = Replace(strQReportSQL, strQReportParameterName, _
' "'" & strDeptValue & "'", 1, -1, vbTextCompare)
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strDeptValue
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp,
"U:\DocDirectReports\PACPAJ01\" & strDeptValue & ".xls"
rstDept.MoveNext
Loop
End If

rstDept.Close
Set rstDept = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code


(2) Second, in the macro that you're running when the .mdb file is first
opened, delete the action step for opening the current form. Replace that
action step with
Action: RunCode
Function Name: ExportDeptDataFromQReportQuery()


What the above setup will do is run the ExportDeptDataFromQReportQuery
function via your macro. This function creates a temporary query in your
file, giving it a temporary name. Then it opens the recordset of Dept
values. It loops through the Dept recordset. For each Dept value, it
creates
a department-specific SQL statement for each Dept value, assigns that SQL
statement to the temporary query, renames the temporary query to include
the
Dept value in the name, and then exports that temporary query to give you
an
EXCEL file with just that Dept value's data. When all Dept values have
been
used, it deletes the temporary query.

If you do not need to keep the parameter in QReport query for other
purposes, then you could delete the "[Forms]![FRpts].[txtDept]" parameter
from that query, let the VBA code create a new query based on QReport,
and
add a WHERE clause that would filter the data for a Dept value, and
export
the filtered data. It would be very similar to what I've outlined above,
except with a few tweaks.

Let me know if you find errors or problems with this code/setup.

 

[Jeff C]

Ken:

Small change, I got it to run just as you suggested for code. The change
required was to enclose the Where Statement .... [Forms]![FRpts].[txtDept] in
quotes . . . "[Forms]![FRpts].[txtDept]"

Works beautifully and I cannot say thank you enough.

I will keep studying the code.

THANK YOU
--
Jeff C
Live Well .. Be Happy In All You Do

Answers to your questions first:

(1)***
The dept field was originally text, I
used queries to qualify the dept as CDbl.

(Answer) It would be very helpful if you could post some examples of the
values that are in Dept field (or the field that is modifying that field --
by CDbl?) in the QReport query. Why are you using CDbl to "change" the Dept
field's values? CDbl is giving you a floating point number that may be
difficult to "match" (i.e., using = operator in a query with it may not
yield a match that you and I "by eye" might think is a match); perhaps it'd
be better to use a different casting function (e.g., CLng?) or no casting
function at all. There is no problem with using the Dept value as a text
string for that matter, either -- likely there's no need to change it to a
numeric value at all.



(2)***

Const strQReportParameterName As String = "[Forms]![FRpts].[txtDept]"

I am prompted for parameter: We use the above string but the form is not
open and does not load with data to use for parameter. How do we do this?

(Answer) You don't say when the parameter prompt is being displayed, but I
assume it's occurring for each export action. If this is true, then most
likely the QReport query's parameter is not
[Forms]![FRpts].[txtDept]
but instead is something else -- perhaps (which would be more normal syntax)
[Forms]![FRpts]![txtDept]

In order to confirm and fix the problem, please post the entire SQL
statement of the QReport query so that we can see the exact parameter name
and on which field it's being used. Also, please indicate if we can change
QReport query by deleting the parameter from it entirely (see my
comment/question in my previous post about this).



(3)***

rstDept.MoveFirst

Should this be MoveNext to make the values loop through the form?

(Answer) No, this step correctly is .MoveFirst. Although it's normal for a
recordset that contains at least one record to be positioned at the first
record, I have an old habit of including code steps that explicitly do what
I want to do, instead of relying on default behaviors/values (you just never
know when a default behavior might be changed to a different behavior in a
newer version of ACCESS). Therefore, I added the .MoveFirst step, although
many others will say it's redundant, just so that I am sure that the
recordset is starting on the first record.


Let's see the SQL statement of the QReport query, and for the QDept query.
In the QDept query, what is the data type of the Dept field that that query
contains? It's critical that we match data types for this code to work --
data values that are string must be "matched" to string values, numbers must
be "matched" to numbers, etc.
--

Ken Snell
<MS ACCESS MVP>

Ken: I have questions in line below. The dept field was originally
text, I
used queries to qualify the dept as CDbl.
--
Jeff C
Live Well .. Be Happy In All You Do

OK. Here is what I suggest be done -- note that I am assuming that the
Dept
field in QDepts query is numeric (if it's text, you'll need to delimit
its
value with ' characters in the Replace statement -- and note that I am
assuming that you do not want to delete the parameter from your QReport
query because you may have need of it for other purposes -- and also note
that I have not tested this code, so it may need a bit of debugging:


(1) First, create a new, regular module (name the module
"basExportData"),
and paste this VBA code in that module:

'Start of code
Public Function ExportDeptDataFromQReportQuery()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstDept As DAO.Recordset
Dim strSQL As String, strTemp As String, strQReportSQL As String
Dim strDeptValue As String

Const strQName As String = "zExportReportQuery"
Const strQReportParameterName As String = "[Forms]![FRpts].[txtDept]"

I am prompted for parameter: We use the above string but the form is not
open and does not load with data to use for parameter. How do we do this?

The sequence produces correctly named workbooks but they are empty of data
when I click throught them. I do not need to keep the queries.

Set dbs = CurrentDb

' Get SQL string from QReport query
strQReportSQL = dbs.QueryDefs("QReport").SQL

' Create temporary query that will be used for exporting data
Set qdf = dbs.CreateQueryDef(strQName, strQReportSQL)
qdf.Close
strTemp = qdf.Name
Set qdf = Nothing

' Get list of Depts
Set rstDept = dbs.OpenRecordset("QDepts", dbOpenDynaset, dbReadOnly)

' Now loop through Depts and create a query for each value
' so that the data can be exported
If rstDept.EOF = False And rstDept.BOF = False Then

rstDept.MoveFirst

Should this be MoveNext to make the values loop through the form?

Do While rstDept.EOF = False
strDeptValue = CStr(rstDept![Dept])
' Replace "parameter string" from QReport query's SQL with actual value
' of Dept field
' use this code step if Dept is numeric (don't use the other one
below)
strSQL = Replace(strQReportSQL, strQReportParameterName, _
strDeptValue, 1, -1, vbTextCompare)
' use this code step if Dept is text (don't use the other one
above)
' strSQL = Replace(strQReportSQL, strQReportParameterName, _
' "'" & strDeptValue & "'", 1, -1, vbTextCompare)
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strDeptValue
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp,
"U:\DocDirectReports\PACPAJ01\" & strDeptValue & ".xls"
rstDept.MoveNext
Loop
End If

rstDept.Close
Set rstDept = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code


(2) Second, in the macro that you're running when the .mdb file is first
opened, delete the action step for opening the current form. Replace that
action step with
Action: RunCode
Function Name: ExportDeptDataFromQReportQuery()


What the above setup will do is run the ExportDeptDataFromQReportQuery
function via your macro. This function creates a temporary query in your
file, giving it a temporary name. Then it opens the recordset of Dept
values. It loops through the Dept recordset. For each Dept value, it
creates
a department-specific SQL statement for each Dept value, assigns that SQL
statement to the temporary query, renames the temporary query to include
the
Dept value in the name, and then exports that temporary query to give you
an
EXCEL file with just that Dept value's data. When all Dept values have
been
used, it deletes the temporary query.

If you do not need to keep the parameter in QReport query for other
purposes, then you could delete the "[Forms]![FRpts].[txtDept]" parameter
from that query, let the VBA code create a new query based on QReport,
and
add a WHERE clause that would filter the data for a Dept value, and
export
the filtered data. It would be very similar to what I've outlined above,
except with a few tweaks.

Let me know if you find errors or problems with this code/setup.

 

[Ken Snell \(MVP\)]

You're welcome.

Are you saying you didn't put the " characters around the
[Forms]![FRpts].[txtDept] in the "Const" line? You do need those "
characters there, as noted in my code example.

I am glad that it's working for you. Feel free to post back if you have
questions about the code / setup. Good luck.