Suggestions Please - Subform Results Export to Excel

[tmdrake]

I have a Mainform where the user selection from one or a multiple of 4
comboboxes and select a date range. This information displays in a subform.

I would like to use a Command Button on the Mainform to export the
information displayed in the subform to Excel. Suggestions Please.

Thanks

 

[Jeanette Cunningham]

tmdrake,
build a query based on the subform and the filters used on the mainform.
When the query is correctly showing just the records showing in the subform,
use Transfer Spreadsheet to put the data from the query into excel.
Use the vba help on Transfer Spreadsheet to set it up.
Transfer Spreadsheet is fairly simple to use.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[tmdrake]

Maybe I should explain better what I am trying to do and what is happening.

1) I have a mainform where the user can select from on or a combination of
4 comboboxes then enter a Start date and End date, then click the select
button on the mainform. Using the code below, the subform then displays the
selected information:
Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

2) I have position a Export to Excel button on the mainform. I am trying
to have the information displayed in the subform exported to excel.
The recordsource for the subform is coded as follows:
SELECT tblHours_Worked.ProjectID, tblHours_Worked.DisciplineName,
tblHours_Worked.SectionNumber, tblHours_Worked.LastName,
tblHours_Worked.FirstName, tblHours_Worked.[SLC Code], tblHours_Worked.[Week
Ending], tblHours_Worked.PHW, tblHours_Worked.AHW FROM tblHours_Worked WHERE
True;

Now this is currently the code being used to export the information in the
subform to excel. The user can double click on the fields in the subform,
enter start and end dates:
Private Sub SectionNumber_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.SectionNumber
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"SectionNumber = " & stLinkCriteria

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
Debug.Print stLinkCriteria
End Sub

I don't want the user to have to double click then enter a start and end
date, I would like for them to be able to click the Export to Excel button
and the information displayed exports to excel. Using the query:
qryHours_Worked_Export. No go to a pivot table form then to Excel.

Hopefully this help better understand what I a asking. If not I'll try to
better to explain.

Thanks

 

[Jeanette Cunningham]

tmdrake,
you can export the strSQL that is the recordsource of the form called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to use the query
defs of a saved query - search the discussion group for instructions on
this.)

First step is to make some minor changes to the sub that builds strSQL when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Sub


Change the Sub Select to something like this:
Private Sub Select_Click()
strSQL = HoursExportXL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

Create the export sub something like this:
Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file name"
End Sub

Note: the above is air code, I have not tested it but it gives you the idea
of how to do it.


Jeanette Cunningham -- Melbourne Victoria Australia

Maybe I should explain better what I am trying to do and what is
happening.

1) I have a mainform where the user can select from on or a combination
of
4 comboboxes then enter a Start date and End date, then click the select
button on the mainform. Using the code below, the subform then displays
the
selected information:
Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

2) I have position a Export to Excel button on the mainform. I am trying
to have the information displayed in the subform exported to excel.
The recordsource for the subform is coded as follows:
SELECT tblHours_Worked.ProjectID, tblHours_Worked.DisciplineName,
tblHours_Worked.SectionNumber, tblHours_Worked.LastName,
tblHours_Worked.FirstName, tblHours_Worked.[SLC Code],
tblHours_Worked.[Week
Ending], tblHours_Worked.PHW, tblHours_Worked.AHW FROM tblHours_Worked
WHERE
True;

Now this is currently the code being used to export the information in the
subform to excel. The user can double click on the fields in the subform,
enter start and end dates:
Private Sub SectionNumber_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.SectionNumber
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"SectionNumber = " & stLinkCriteria

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
Debug.Print stLinkCriteria
End Sub

I don't want the user to have to double click then enter a start and end
date, I would like for them to be able to click the Export to Excel button
and the information displayed exports to excel. Using the query:
qryHours_Worked_Export. No go to a pivot table form then to Excel.

Hopefully this help better understand what I a asking. If not I'll try to
better to explain.

Thanks
--
tmdrake

tmdrake,
build a query based on the subform and the filters used on the mainform.
When the query is correctly showing just the records showing in the
subform,
use Transfer Spreadsheet to put the data from the query into excel.
Use the vba help on Transfer Spreadsheet to set it up.
Transfer Spreadsheet is fairly simple to use.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[tmdrake]

Thanks for you help,

Everything works properly with the exception of the Export. I am receiving
a run-time error '3011' - The Microsoft Jet database engine could not find
the object 'SELECT tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName,
[tblHours_Worked].SectionNumber,[tblHours_Worked].LastName,
[tblHours_Worked].[FirstName'. Make sure the object exists and that you
spellits name and the path name correctly.

What does this mean?
--
tmdrake

tmdrake,
you can export the strSQL that is the recordsource of the form called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to use the query
defs of a saved query - search the discussion group for instructions on
this.)

First step is to make some minor changes to the sub that builds strSQL when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Sub


Change the Sub Select to something like this:
Private Sub Select_Click()
strSQL = HoursExportXL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

Create the export sub something like this:
Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file name"
End Sub

Note: the above is air code, I have not tested it but it gives you the idea
of how to do it.


Jeanette Cunningham -- Melbourne Victoria Australia

Maybe I should explain better what I am trying to do and what is
happening.

1) I have a mainform where the user can select from on or a combination
of
4 comboboxes then enter a Start date and End date, then click the select
button on the mainform. Using the code below, the subform then displays
the
selected information:
Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

2) I have position a Export to Excel button on the mainform. I am trying
to have the information displayed in the subform exported to excel.
The recordsource for the subform is coded as follows:
SELECT tblHours_Worked.ProjectID, tblHours_Worked.DisciplineName,
tblHours_Worked.SectionNumber, tblHours_Worked.LastName,
tblHours_Worked.FirstName, tblHours_Worked.[SLC Code],
tblHours_Worked.[Week
Ending], tblHours_Worked.PHW, tblHours_Worked.AHW FROM tblHours_Worked
WHERE
True;

Now this is currently the code being used to export the information in the
subform to excel. The user can double click on the fields in the subform,
enter start and end dates:
Private Sub SectionNumber_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.SectionNumber
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"SectionNumber = " & stLinkCriteria

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
Debug.Print stLinkCriteria
End Sub

I don't want the user to have to double click then enter a start and end
date, I would like for them to be able to click the Export to Excel button
and the information displayed exports to excel. Using the query:
qryHours_Worked_Export. No go to a pivot table form then to Excel.

Hopefully this help better understand what I a asking. If not I'll try to
better to explain.

Thanks
--
tmdrake

tmdrake,
build a query based on the subform and the filters used on the mainform.
When the query is correctly showing just the records showing in the
subform,
use Transfer Spreadsheet to put the data from the query into excel.
Use the vba help on Transfer Spreadsheet to set it up.
Transfer Spreadsheet is fairly simple to use.


Jeanette Cunningham -- Melbourne Victoria Australia


I have a Mainform where the user selection from one or a multiple of 4
comboboxes and select a date range. This information displays in a
subform.

I would like to use a Command Button on the Mainform to export the
information displayed in the subform to Excel. Suggestions Please.

Thanks

 

[Jeanette Cunningham]

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for the subform. Is
this correct?

Copy the query string from the immediate window and paste in into the SQL
view of a new query.
Try to switch to datasheet view and you will get an error message about the
query which can help you to fix it.


Jeanette Cunningham -- Melbourne Victoria Australia

Thanks for you help,

Everything works properly with the exception of the Export. I am
receiving
a run-time error '3011' - The Microsoft Jet database engine could not find
the object 'SELECT tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName,
[tblHours_Worked].SectionNumber,[tblHours_Worked].LastName,
[tblHours_Worked].[FirstName'. Make sure the object exists and that you
spellits name and the path name correctly.

What does this mean?
--
tmdrake

tmdrake,
you can export the strSQL that is the recordsource of the form called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to use the query
defs of a saved query - search the discussion group for instructions on
this.)

First step is to make some minor changes to the sub that builds strSQL
when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Sub


Change the Sub Select to something like this:
Private Sub Select_Click()
strSQL = HoursExportXL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

Create the export sub something like this:
Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file
name"
End Sub

Note: the above is air code, I have not tested it but it gives you the
idea
of how to do it.


Jeanette Cunningham -- Melbourne Victoria Australia

Maybe I should explain better what I am trying to do and what is
happening.

1) I have a mainform where the user can select from on or a
combination
of
4 comboboxes then enter a Start date and End date, then click the
select
button on the mainform. Using the code below, the subform then
displays
the
selected information:
Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] &
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

2) I have position a Export to Excel button on the mainform. I am
trying
to have the information displayed in the subform exported to excel.
The recordsource for the subform is coded as follows:
SELECT tblHours_Worked.ProjectID, tblHours_Worked.DisciplineName,
tblHours_Worked.SectionNumber, tblHours_Worked.LastName,
tblHours_Worked.FirstName, tblHours_Worked.[SLC Code],
tblHours_Worked.[Week
Ending], tblHours_Worked.PHW, tblHours_Worked.AHW FROM tblHours_Worked
WHERE
True;

Now this is currently the code being used to export the information in
the
subform to excel. The user can double click on the fields in the
subform,
enter start and end dates:
Private Sub SectionNumber_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.SectionNumber
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"SectionNumber = " & stLinkCriteria

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
Debug.Print stLinkCriteria
End Sub

I don't want the user to have to double click then enter a start and
end
date, I would like for them to be able to click the Export to Excel
button
and the information displayed exports to excel. Using the query:
qryHours_Worked_Export. No go to a pivot table form then to Excel.

Hopefully this help better understand what I a asking. If not I'll try
to
better to explain.

Thanks
--
tmdrake


:

tmdrake,
build a query based on the subform and the filters used on the
mainform.
When the query is correctly showing just the records showing in the
subform,
use Transfer Spreadsheet to put the data from the query into excel.
Use the vba help on Transfer Spreadsheet to set it up.
Transfer Spreadsheet is fairly simple to use.


Jeanette Cunningham -- Melbourne Victoria Australia


I have a Mainform where the user selection from one or a multiple of
4
comboboxes and select a date range. This information displays in a
subform.

I would like to use a Command Button on the Mainform to export the
information displayed in the subform to Excel. Suggestions Please.

Thanks

 

[tmdrake]

I did as you instructed. When placing the query string in a new query, there
were no errors. The query worked fine. However, I still get the same error
when selecting the Export button.

Also, You are right in assuming the strSQL is the full recordsource query
for the subform.

Your Help is greatly appreciated.
--
tmdrake

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for the subform. Is
this correct?

Copy the query string from the immediate window and paste in into the SQL
view of a new query.
Try to switch to datasheet view and you will get an error message about the
query which can help you to fix it.


Jeanette Cunningham -- Melbourne Victoria Australia

Thanks for you help,

Everything works properly with the exception of the Export. I am
receiving
a run-time error '3011' - The Microsoft Jet database engine could not find
the object 'SELECT tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName,
[tblHours_Worked].SectionNumber,[tblHours_Worked].LastName,
[tblHours_Worked].[FirstName'. Make sure the object exists and that you
spellits name and the path name correctly.

What does this mean?
--
tmdrake

tmdrake,
you can export the strSQL that is the recordsource of the form called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to use the query
defs of a saved query - search the discussion group for instructions on
this.)

First step is to make some minor changes to the sub that builds strSQL
when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Sub


Change the Sub Select to something like this:
Private Sub Select_Click()
strSQL = HoursExportXL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

Create the export sub something like this:
Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file
name"
End Sub

Note: the above is air code, I have not tested it but it gives you the
idea
of how to do it.


Jeanette Cunningham -- Melbourne Victoria Australia


Maybe I should explain better what I am trying to do and what is
happening.

1) I have a mainform where the user can select from on or a
combination
of
4 comboboxes then enter a Start date and End date, then click the
select
button on the mainform. Using the code below, the subform then
displays
the
selected information:
Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] &
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

2) I have position a Export to Excel button on the mainform. I am
trying
to have the information displayed in the subform exported to excel.
The recordsource for the subform is coded as follows:
SELECT tblHours_Worked.ProjectID, tblHours_Worked.DisciplineName,
tblHours_Worked.SectionNumber, tblHours_Worked.LastName,
tblHours_Worked.FirstName, tblHours_Worked.[SLC Code],
tblHours_Worked.[Week
Ending], tblHours_Worked.PHW, tblHours_Worked.AHW FROM tblHours_Worked
WHERE
True;

Now this is currently the code being used to export the information in
the
subform to excel. The user can double click on the fields in the
subform,
enter start and end dates:
Private Sub SectionNumber_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.SectionNumber
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"SectionNumber = " & stLinkCriteria

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
Debug.Print stLinkCriteria
End Sub

I don't want the user to have to double click then enter a start and
end
date, I would like for them to be able to click the Export to Excel
button
and the information displayed exports to excel. Using the query:
qryHours_Worked_Export. No go to a pivot table form then to Excel.

Hopefully this help better understand what I a asking. If not I'll try
to
better to explain.

Thanks
--
tmdrake


:

tmdrake,
build a query based on the subform and the filters used on the
mainform.
When the query is correctly showing just the records showing in the
subform,
use Transfer Spreadsheet to put the data from the query into excel.
Use the vba help on Transfer Spreadsheet to set it up.
Transfer Spreadsheet is fairly simple to use.


Jeanette Cunningham -- Melbourne Victoria Australia


I have a Mainform where the user selection from one or a multiple of
4
comboboxes and select a date range. This information displays in a
subform.

I would like to use a Command Button on the Mainform to export the
information displayed in the subform to Excel. Suggestions Please.

Thanks

 

[Jeanette Cunningham]

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final version of the
code that is not working.


Jeanette Cunningham -- Melbourne Victoria Australia

I did as you instructed. When placing the query string in a new query,
there
were no errors. The query worked fine. However, I still get the same
error
when selecting the Export button.

Also, You are right in assuming the strSQL is the full recordsource query
for the subform.

Your Help is greatly appreciated.
--
tmdrake

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file
name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for the subform.
Is
this correct?

Copy the query string from the immediate window and paste in into the SQL
view of a new query.
Try to switch to datasheet view and you will get an error message about
the
query which can help you to fix it.


Jeanette Cunningham -- Melbourne Victoria Australia

Thanks for you help,

Everything works properly with the exception of the Export. I am
receiving
a run-time error '3011' - The Microsoft Jet database engine could not
find
the object 'SELECT tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName,
[tblHours_Worked].SectionNumber,[tblHours_Worked].LastName,
[tblHours_Worked].[FirstName'. Make sure the object exists and that
you
spellits name and the path name correctly.

What does this mean?
--
tmdrake


:

tmdrake,
you can export the strSQL that is the recordsource of the form called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to use the
query
defs of a saved query - search the discussion group for instructions
on
this.)

First step is to make some minor changes to the sub that builds
strSQL
when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] &
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Sub


Change the Sub Select to something like this:
Private Sub Select_Click()
strSQL = HoursExportXL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

Create the export sub something like this:
Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file
name"
End Sub

Note: the above is air code, I have not tested it but it gives you
the
idea
of how to do it.


Jeanette Cunningham -- Melbourne Victoria Australia


Maybe I should explain better what I am trying to do and what is
happening.

1) I have a mainform where the user can select from on or a
combination
of
4 comboboxes then enter a Start date and End date, then click the
select
button on the mainform. Using the code below, the subform then
displays
the
selected information:
Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] &
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

2) I have position a Export to Excel button on the mainform. I am
trying
to have the information displayed in the subform exported to excel.
The recordsource for the subform is coded as follows:
SELECT tblHours_Worked.ProjectID, tblHours_Worked.DisciplineName,
tblHours_Worked.SectionNumber, tblHours_Worked.LastName,
tblHours_Worked.FirstName, tblHours_Worked.[SLC Code],
tblHours_Worked.[Week
Ending], tblHours_Worked.PHW, tblHours_Worked.AHW FROM
tblHours_Worked
WHERE
True;

Now this is currently the code being used to export the information
in
the
subform to excel. The user can double click on the fields in the
subform,
enter start and end dates:
Private Sub SectionNumber_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.SectionNumber
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"SectionNumber = " & stLinkCriteria

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
Debug.Print stLinkCriteria
End Sub

I don't want the user to have to double click then enter a start and
end
date, I would like for them to be able to click the Export to Excel
button
and the information displayed exports to excel. Using the query:
qryHours_Worked_Export. No go to a pivot table form then to Excel.

Hopefully this help better understand what I a asking. If not I'll
try
to
better to explain.

Thanks
--
tmdrake


:

tmdrake,
build a query based on the subform and the filters used on the
mainform.
When the query is correctly showing just the records showing in the
subform,
use Transfer Spreadsheet to put the data from the query into excel.
Use the vba help on Transfer Spreadsheet to set it up.
Transfer Spreadsheet is fairly simple to use.


Jeanette Cunningham -- Melbourne Victoria Australia


I have a Mainform where the user selection from one or a multiple
of
4
comboboxes and select a date range. This information displays in
a
subform.

I would like to use a Command Button on the Mainform to export
the
information displayed in the subform to Excel. Suggestions
Please.

Thanks

 

[tmdrake]

Jeanette, thanks so much for all of your help. I apoloize for not responding
sooner. Please see below for the information you requested, Again Thanks so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final version of the
code that is not working.


Jeanette Cunningham -- Melbourne Victoria Australia

I did as you instructed. When placing the query string in a new query,
there
were no errors. The query worked fine. However, I still get the same
error
when selecting the Export button.

Also, You are right in assuming the strSQL is the full recordsource query
for the subform.

Your Help is greatly appreciated.
--
tmdrake

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file
name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for the subform.
Is
this correct?

Copy the query string from the immediate window and paste in into the SQL
view of a new query.
Try to switch to datasheet view and you will get an error message about
the
query which can help you to fix it.


Jeanette Cunningham -- Melbourne Victoria Australia


Thanks for you help,

Everything works properly with the exception of the Export. I am
receiving
a run-time error '3011' - The Microsoft Jet database engine could not
find
the object 'SELECT tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName,
[tblHours_Worked].SectionNumber,[tblHours_Worked].LastName,
[tblHours_Worked].[FirstName'. Make sure the object exists and that
you
spellits name and the path name correctly.

What does this mean?
--
tmdrake


:

tmdrake,
you can export the strSQL that is the recordsource of the form called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to use the
query
defs of a saved query - search the discussion group for instructions
on
this.)

First step is to make some minor changes to the sub that builds
strSQL
when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] &
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Sub


Change the Sub Select to something like this:
Private Sub Select_Click()
strSQL = HoursExportXL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

Create the export sub something like this:
Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file
name"
End Sub

Note: the above is air code, I have not tested it but it gives you
the
idea
of how to do it.


Jeanette Cunningham -- Melbourne Victoria Australia


Maybe I should explain better what I am trying to do and what is
happening.

1) I have a mainform where the user can select from on or a
combination
of
4 comboboxes then enter a Start date and End date, then click the
select
button on the mainform. Using the code below, the subform then
displays
the
selected information:
Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] &
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

2) I have position a Export to Excel button on the mainform. I am
trying
to have the information displayed in the subform exported to excel.
The recordsource for the subform is coded as follows:
SELECT tblHours_Worked.ProjectID, tblHours_Worked.DisciplineName,
tblHours_Worked.SectionNumber, tblHours_Worked.LastName,
tblHours_Worked.FirstName, tblHours_Worked.[SLC Code],
tblHours_Worked.[Week
Ending], tblHours_Worked.PHW, tblHours_Worked.AHW FROM
tblHours_Worked
WHERE
True;

Now this is currently the code being used to export the information
in
the
subform to excel. The user can double click on the fields in the
subform,
enter start and end dates:
Private Sub SectionNumber_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.SectionNumber
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"SectionNumber = " & stLinkCriteria

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
Debug.Print stLinkCriteria
End Sub

I don't want the user to have to double click then enter a start and
end
date, I would like for them to be able to click the Export to Excel
button
and the information displayed exports to excel. Using the query:
qryHours_Worked_Export. No go to a pivot table form then to Excel.

Hopefully this help better understand what I a asking. If not I'll
try
to
better to explain.

Thanks
--
tmdrake


:

tmdrake,
build a query based on the subform and the filters used on the
mainform.
When the query is correctly showing just the records showing in the
subform,
use Transfer Spreadsheet to put the data from the query into excel.
Use the vba help on Transfer Spreadsheet to set it up.
Transfer Spreadsheet is fairly simple to use.


Jeanette Cunningham -- Melbourne Victoria Australia


I have a Mainform where the user selection from one or a multiple
of
4
comboboxes and select a date range. This information displays in
a
subform.

I would like to use a Command Button on the Mainform to export
the
information displayed in the subform to Excel. Suggestions
Please.

Thanks

 

[Jeanette Cunningham]

back with exporting the subform data to excel.
I have checked this again, my memory of how to do it was faulty.
It is a bit more complicated than I first suggested.

The easiest way to do this is to set up a saved query.
In the query put criteria for each field that can be filtered on in the main
form.

So build a saved query using tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName . . .
and so on to include all fields you want in the report.

Under the column for ProjectID, put a criteria that selects the ProjectID
from the main form, something like this:

Forms!frmMainName!ProjectIDControlName

Replace frmMainName with the name of your parent form
Replace ProjectIDControlName with the name of the control for ProjectID

Save the query.
Open the parent form and while it is open, also open the saved query from
above and check that it gives the results where the subform data matches the
ProjectID chosen in the parent form.

Repeat the process for each field where the user can choose a value on the
parent form.
You will end up with a query that will give you all the data that matches
the selections made on the parent form.

Use TransferSpreadsheet to export the query to excel.


Jeanette Cunningham -- Melbourne Victoria Australia

Jeanette, thanks so much for all of your help. I apoloize for not
responding
sooner. Please see below for the information you requested, Again Thanks
so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final version of
the
code that is not working.


Jeanette Cunningham -- Melbourne Victoria Australia

I did as you instructed. When placing the query string in a new query,
there
were no errors. The query worked fine. However, I still get the same
error
when selecting the Export button.

Also, You are right in assuming the strSQL is the full recordsource
query
for the subform.

Your Help is greatly appreciated.
--
tmdrake


:

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file
name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for the
subform.
Is
this correct?

Copy the query string from the immediate window and paste in into the
SQL
view of a new query.
Try to switch to datasheet view and you will get an error message
about
the
query which can help you to fix it.


Jeanette Cunningham -- Melbourne Victoria Australia


Thanks for you help,

Everything works properly with the exception of the Export. I am
receiving
a run-time error '3011' - The Microsoft Jet database engine could
not
find
the object 'SELECT tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName,
[tblHours_Worked].SectionNumber,[tblHours_Worked].LastName,
[tblHours_Worked].[FirstName'. Make sure the object exists and that
you
spellits name and the path name correctly.

What does this mean?
--
tmdrake


:

tmdrake,
you can export the strSQL that is the recordsource of the form
called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to use the
query
defs of a saved query - search the discussion group for
instructions
on
this.)

First step is to make some minor changes to the sub that builds
strSQL
when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] &
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " &
Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Sub


Change the Sub Select to something like this:
Private Sub Select_Click()
strSQL = HoursExportXL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

Create the export sub something like this:
Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and
file
name"
End Sub

Note: the above is air code, I have not tested it but it gives you
the
idea
of how to do it.


Jeanette Cunningham -- Melbourne Victoria Australia


Maybe I should explain better what I am trying to do and what is
happening.

1) I have a mainform where the user can select from on or a
combination
of
4 comboboxes then enter a Start date and End date, then click the
select
button on the mainform. Using the code below, the subform then
displays
the
selected information:
Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" &
Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber]
&
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " &
Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

2) I have position a Export to Excel button on the mainform. I
am
trying
to have the information displayed in the subform exported to
excel.
The recordsource for the subform is coded as follows:
SELECT tblHours_Worked.ProjectID, tblHours_Worked.DisciplineName,
tblHours_Worked.SectionNumber, tblHours_Worked.LastName,
tblHours_Worked.FirstName, tblHours_Worked.[SLC Code],
tblHours_Worked.[Week
Ending], tblHours_Worked.PHW, tblHours_Worked.AHW FROM
tblHours_Worked
WHERE
True;

Now this is currently the code being used to export the
information
in
the
subform to excel. The user can double click on the fields in the
subform,
enter start and end dates:
Private Sub SectionNumber_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.SectionNumber
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"SectionNumber = " & stLinkCriteria

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
Debug.Print stLinkCriteria
End Sub

I don't want the user to have to double click then enter a start
and
end
date, I would like for them to be able to click the Export to
Excel
button
and the information displayed exports to excel. Using the query:
qryHours_Worked_Export. No go to a pivot table form then to
Excel.

Hopefully this help better understand what I a asking. If not
I'll
try
to
better to explain.

Thanks
--
tmdrake


:

tmdrake,
build a query based on the subform and the filters used on the
mainform.
When the query is correctly showing just the records showing in
the
subform,
use Transfer Spreadsheet to put the data from the query into
excel.
Use the vba help on Transfer Spreadsheet to set it up.
Transfer Spreadsheet is fairly simple to use.


Jeanette Cunningham -- Melbourne Victoria Australia


I have a Mainform where the user selection from one or a
multiple
of
4
comboboxes and select a date range. This information displays
in
a
subform.

I would like to use a Command Button on the Mainform to export
the
information displayed in the subform to Excel. Suggestions
Please.

Thanks

 

[tmdrake]

Jeanette again thanks for you help.

Below is the code that I am using for the export button;

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
Debug.Print "strSQL:" & strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryHours_Worked(2), "\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

However, now I am receiving the following error:
The expression On Click you entered as the event property setting produced
the following error: Sub or Function no defined.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Proceudre].
*There may have been an error evaluation the function, event, or macro.

What does this mean.


--
tmdrake

back with exporting the subform data to excel.
I have checked this again, my memory of how to do it was faulty.
It is a bit more complicated than I first suggested.

The easiest way to do this is to set up a saved query.
In the query put criteria for each field that can be filtered on in the main
form.

So build a saved query using tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName . . .
and so on to include all fields you want in the report.

Under the column for ProjectID, put a criteria that selects the ProjectID
from the main form, something like this:

Forms!frmMainName!ProjectIDControlName

Replace frmMainName with the name of your parent form
Replace ProjectIDControlName with the name of the control for ProjectID

Save the query.
Open the parent form and while it is open, also open the saved query from
above and check that it gives the results where the subform data matches the
ProjectID chosen in the parent form.

Repeat the process for each field where the user can choose a value on the
parent form.
You will end up with a query that will give you all the data that matches
the selections made on the parent form.

Use TransferSpreadsheet to export the query to excel.


Jeanette Cunningham -- Melbourne Victoria Australia

Jeanette, thanks so much for all of your help. I apoloize for not
responding
sooner. Please see below for the information you requested, Again Thanks
so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final version of
the
code that is not working.


Jeanette Cunningham -- Melbourne Victoria Australia


I did as you instructed. When placing the query string in a new query,
there
were no errors. The query worked fine. However, I still get the same
error
when selecting the Export button.

Also, You are right in assuming the strSQL is the full recordsource
query
for the subform.

Your Help is greatly appreciated.
--
tmdrake


:

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and file
name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for the
subform.
Is
this correct?

Copy the query string from the immediate window and paste in into the
SQL
view of a new query.
Try to switch to datasheet view and you will get an error message
about
the
query which can help you to fix it.


Jeanette Cunningham -- Melbourne Victoria Australia


Thanks for you help,

Everything works properly with the exception of the Export. I am
receiving
a run-time error '3011' - The Microsoft Jet database engine could
not
find
the object 'SELECT tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName,
[tblHours_Worked].SectionNumber,[tblHours_Worked].LastName,
[tblHours_Worked].[FirstName'. Make sure the object exists and that
you
spellits name and the path name correctly.

What does this mean?
--
tmdrake


:

tmdrake,
you can export the strSQL that is the recordsource of the form
called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to use the
query
defs of a saved query - search the discussion group for
instructions
on
this.)

First step is to make some minor changes to the sub that builds
strSQL
when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] &
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " &
Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Sub


Change the Sub Select to something like this:
Private Sub Select_Click()
strSQL = HoursExportXL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

Create the export sub something like this:
Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and
file
name"
End Sub

Note: the above is air code, I have not tested it but it gives you
the
idea
of how to do it.


Jeanette Cunningham -- Melbourne Victoria Australia


Maybe I should explain better what I am trying to do and what is
happening.

1) I have a mainform where the user can select from on or a
combination
of
4 comboboxes then enter a Start date and End date, then click the
select
button on the mainform. Using the code below, the subform then
displays
the
selected information:
Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" &
Me![DisciplineName]

 

[Jeanette Cunningham]

--Remove the lines about strSQL
--put qryHours_Worked(2) in quotes because TransferSpreadsheet expects a
string for this argument
--remove the last 2 arguments of TransferSpreadsheet as you don't need them
for this export.


Private Sub Export_to_Excel_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryHours_Worked(2)", "\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls"

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette again thanks for you help.

Below is the code that I am using for the export button;

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
Debug.Print "strSQL:" & strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryHours_Worked(2), "\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

However, now I am receiving the following error:
The expression On Click you entered as the event property setting produced
the following error: Sub or Function no defined.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Proceudre].
*There may have been an error evaluation the function, event, or macro.

What does this mean.


--
tmdrake

back with exporting the subform data to excel.
I have checked this again, my memory of how to do it was faulty.
It is a bit more complicated than I first suggested.

The easiest way to do this is to set up a saved query.
In the query put criteria for each field that can be filtered on in the
main
form.

So build a saved query using tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName . . .
and so on to include all fields you want in the report.

Under the column for ProjectID, put a criteria that selects the ProjectID
from the main form, something like this:

Forms!frmMainName!ProjectIDControlName

Replace frmMainName with the name of your parent form
Replace ProjectIDControlName with the name of the control for ProjectID

Save the query.
Open the parent form and while it is open, also open the saved query from
above and check that it gives the results where the subform data matches
the
ProjectID chosen in the parent form.

Repeat the process for each field where the user can choose a value on
the
parent form.
You will end up with a query that will give you all the data that matches
the selections made on the parent form.

Use TransferSpreadsheet to export the query to excel.


Jeanette Cunningham -- Melbourne Victoria Australia

Jeanette, thanks so much for all of your help. I apoloize for not
responding
sooner. Please see below for the information you requested, Again
Thanks
so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] &
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake


:

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final version
of
the
code that is not working.


Jeanette Cunningham -- Melbourne Victoria Australia


I did as you instructed. When placing the query string in a new
query,
there
were no errors. The query worked fine. However, I still get the
same
error
when selecting the Export button.

Also, You are right in assuming the strSQL is the full recordsource
query
for the subform.

Your Help is greatly appreciated.
--
tmdrake


:

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and
file
name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for the
subform.
Is
this correct?

Copy the query string from the immediate window and paste in into
the
SQL
view of a new query.
Try to switch to datasheet view and you will get an error message
about
the
query which can help you to fix it.


Jeanette Cunningham -- Melbourne Victoria Australia


Thanks for you help,

Everything works properly with the exception of the Export. I am
receiving
a run-time error '3011' - The Microsoft Jet database engine could
not
find
the object 'SELECT tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName,
[tblHours_Worked].SectionNumber,[tblHours_Worked].LastName,
[tblHours_Worked].[FirstName'. Make sure the object exists and
that
you
spellits name and the path name correctly.

What does this mean?
--
tmdrake


:

tmdrake,
you can export the strSQL that is the recordsource of the form
called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to use
the
query
defs of a saved query - search the discussion group for
instructions
on
this.)

First step is to make some minor changes to the sub that builds
strSQL
when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" &
Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber]
&
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " &
Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " &
Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Sub


Change the Sub Select to something like this:
Private Sub Select_Click()
strSQL = HoursExportXL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

Create the export sub something like this:
Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path
and
file
name"
End Sub

Note: the above is air code, I have not tested it but it gives
you
the
idea
of how to do it.


Jeanette Cunningham -- Melbourne Victoria Australia


Maybe I should explain better what I am trying to do and what
is
happening.

1) I have a mainform where the user can select from on or a
combination
of
4 comboboxes then enter a Start date and End date, then click
the
select
button on the mainform. Using the code below, the subform
then
displays
the
selected information:
Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" &
Me![DisciplineName]

 

[tmdrake]

Hi Jeanette,

I did as you instructed, however now I am receiving a Run-time error'3436':
Failure creating file.

I also built the query as you instructed however, when I make the selection
on the Main Form, the query does not populate.

On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Maybe I should have mentioned that when the user is making a selection from
the main form, he/she is also selecting a Start Date and End Date (separate
comboboxes on the main form). In other words the user will make a selection
from one or a combination of four comboboxes (ProjectID, Discipline Name,
Section Number or Last Name) and the also select the Start Date and End
Date, the click the select button.

Base on the selection the subform will populate with the information
requested.

Please give me more instruction on how to build the query used to Export
this information from the subform by using a button on the main form.

--
tmdrake

--Remove the lines about strSQL
--put qryHours_Worked(2) in quotes because TransferSpreadsheet expects a
string for this argument
--remove the last 2 arguments of TransferSpreadsheet as you don't need them
for this export.


Private Sub Export_to_Excel_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryHours_Worked(2)", "\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls"

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette again thanks for you help.

Below is the code that I am using for the export button;

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
Debug.Print "strSQL:" & strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryHours_Worked(2), "\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

However, now I am receiving the following error:
The expression On Click you entered as the event property setting produced
the following error: Sub or Function no defined.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Proceudre].
*There may have been an error evaluation the function, event, or macro.

What does this mean.


--
tmdrake

back with exporting the subform data to excel.
I have checked this again, my memory of how to do it was faulty.
It is a bit more complicated than I first suggested.

The easiest way to do this is to set up a saved query.
In the query put criteria for each field that can be filtered on in the
main
form.

So build a saved query using tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName . . .
and so on to include all fields you want in the report.

Under the column for ProjectID, put a criteria that selects the ProjectID
from the main form, something like this:

Forms!frmMainName!ProjectIDControlName

Replace frmMainName with the name of your parent form
Replace ProjectIDControlName with the name of the control for ProjectID

Save the query.
Open the parent form and while it is open, also open the saved query from
above and check that it gives the results where the subform data matches
the
ProjectID chosen in the parent form.

Repeat the process for each field where the user can choose a value on
the
parent form.
You will end up with a query that will give you all the data that matches
the selections made on the parent form.

Use TransferSpreadsheet to export the query to excel.


Jeanette Cunningham -- Melbourne Victoria Australia



Jeanette, thanks so much for all of your help. I apoloize for not
responding
sooner. Please see below for the information you requested, Again
Thanks
so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] &
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake


:

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final version
of
the
code that is not working.


Jeanette Cunningham -- Melbourne Victoria Australia


I did as you instructed. When placing the query string in a new
query,
there
were no errors. The query worked fine. However, I still get the
same
error
when selecting the Export button.

Also, You are right in assuming the strSQL is the full recordsource
query
for the subform.

Your Help is greatly appreciated.
--
tmdrake


:

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path and
file
name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for the
subform.
Is
this correct?

Copy the query string from the immediate window and paste in into
the
SQL
view of a new query.
Try to switch to datasheet view and you will get an error message
about
the
query which can help you to fix it.


Jeanette Cunningham -- Melbourne Victoria Australia


Thanks for you help,

Everything works properly with the exception of the Export. I am
receiving
a run-time error '3011' - The Microsoft Jet database engine could
not
find
the object 'SELECT tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName,
[tblHours_Worked].SectionNumber,[tblHours_Worked].LastName,
[tblHours_Worked].[FirstName'. Make sure the object exists and
that
you
spellits name and the path name correctly.

What does this mean?
--
tmdrake


:

tmdrake,
you can export the strSQL that is the recordsource of the form
called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to use
the
query
defs of a saved query - search the discussion group for
instructions
on
this.)

First step is to make some minor changes to the sub that builds
strSQL
when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" &
Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber]
&
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " &

 

[Jeanette Cunningham]

Hi,
the query you build to get the subform data gets its criteria from the main
form.
Where you say
Here is part of my answer from previously
-->Forms!frmMainName!ProjectIDControlName

-->Replace frmMainName with the name of your parent form
-->Replace ProjectIDControlName with the name of the control for ProjectID


The important difference between my suggestion and your query criteria is
that one uses the main form and your example uses the subform.

Try the query with the criteria like this:
Forms!frmHours_Worked_!ProjectID
(leave out any mention of controls or fields on the subform,
only use controls or fields on the main or parent form)

The criteria in the query needs to get the information from the main or
parent form, not the subform.

When you have the query correctly getting the data for ProjectID, then you
try getting the criteria for DisciplineName.
Post back if you have any problems with that one.
After you get the query showing the same data that shows on the subform,
then it is time to try exporting.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,

I did as you instructed, however now I am receiving a Run-time
error'3436':
Failure creating file.

I also built the query as you instructed however, when I make the
selection
on the Main Form, the query does not populate.

On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Maybe I should have mentioned that when the user is making a selection
from
the main form, he/she is also selecting a Start Date and End Date
(separate
comboboxes on the main form). In other words the user will make a
selection
from one or a combination of four comboboxes (ProjectID, Discipline Name,
Section Number or Last Name) and the also select the Start Date and End
Date, the click the select button.

Base on the selection the subform will populate with the information
requested.

Please give me more instruction on how to build the query used to Export
this information from the subform by using a button on the main form.

--
tmdrake

--Remove the lines about strSQL
--put qryHours_Worked(2) in quotes because TransferSpreadsheet expects a
string for this argument
--remove the last 2 arguments of TransferSpreadsheet as you don't need
them
for this export.


Private Sub Export_to_Excel_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryHours_Worked(2)",
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls"

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette again thanks for you help.

Below is the code that I am using for the export button;

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
Debug.Print "strSQL:" & strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryHours_Worked(2),
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

However, now I am receiving the following error:
The expression On Click you entered as the event property setting
produced
the following error: Sub or Function no defined.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Proceudre].
*There may have been an error evaluation the function, event, or macro.

What does this mean.


--
tmdrake


:

back with exporting the subform data to excel.
I have checked this again, my memory of how to do it was faulty.
It is a bit more complicated than I first suggested.

The easiest way to do this is to set up a saved query.
In the query put criteria for each field that can be filtered on in
the
main
form.

So build a saved query using tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName . . .
and so on to include all fields you want in the report.

Under the column for ProjectID, put a criteria that selects the
ProjectID
from the main form, something like this:

Forms!frmMainName!ProjectIDControlName

Replace frmMainName with the name of your parent form
Replace ProjectIDControlName with the name of the control for
ProjectID

Save the query.
Open the parent form and while it is open, also open the saved query
from
above and check that it gives the results where the subform data
matches
the
ProjectID chosen in the parent form.

Repeat the process for each field where the user can choose a value on
the
parent form.
You will end up with a query that will give you all the data that
matches
the selections made on the parent form.

Use TransferSpreadsheet to export the query to excel.


Jeanette Cunningham -- Melbourne Victoria Australia



Jeanette, thanks so much for all of your help. I apoloize for not
responding
sooner. Please see below for the information you requested, Again
Thanks
so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] &
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake


:

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final
version
of
the
code that is not working.


Jeanette Cunningham -- Melbourne Victoria Australia


I did as you instructed. When placing the query string in a new
query,
there
were no errors. The query worked fine. However, I still get the
same
error
when selecting the Export button.

Also, You are right in assuming the strSQL is the full
recordsource
query
for the subform.

Your Help is greatly appreciated.
--
tmdrake


:

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path
and
file
name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for the
subform.
Is
this correct?

Copy the query string from the immediate window and paste in
into
the
SQL
view of a new query.
Try to switch to datasheet view and you will get an error
message
about
the
query which can help you to fix it.


Jeanette Cunningham -- Melbourne Victoria Australia


Thanks for you help,

Everything works properly with the exception of the Export. I
am
receiving
a run-time error '3011' - The Microsoft Jet database engine
could
not
find
the object 'SELECT tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName,
[tblHours_Worked].SectionNumber,[tblHours_Worked].LastName,
[tblHours_Worked].[FirstName'. Make sure the object exists
and
that
you
spellits name and the path name correctly.

What does this mean?
--
tmdrake


:

tmdrake,
you can export the strSQL that is the recordsource of the
form
called
frmHours_Worked_subform.
Use OutputTo instead of TransferSpreadsheet.
( If you wish to use TransferSpreadsheet, you would need to
use
the
query
defs of a saved query - search the discussion group for
instructions
on
this.)

First step is to make some minor changes to the sub that
builds
strSQL
when
you click the Select button.

Create a new function with the name of HoursExportXL

Private Function HoursExportXL() As String
Dim strSQL as String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" &
Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " &
Me![SectionNumber]
&
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] &
"'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " &

 

[tmdrake]

Sorry again for the delayed response, really busy at work.

However, I did what you stated in the previous reply (used
[Forms]![tblProject_Staffing_Resources]![ProjectID] as the criteria), only
now a parameter box appears requesting the ProjectID to base the query. How
do I get the query to read from the Mainform?

Also how can I use multiple critieria in the query? If the user wants to
make more than one selection from the combo boxes.

Thanks again for your help and patience.
--
tmdrake

Hi,
the query you build to get the subform data gets its criteria from the main
form.
Where you say
Here is part of my answer from previously
-->Forms!frmMainName!ProjectIDControlName

-->Replace frmMainName with the name of your parent form
-->Replace ProjectIDControlName with the name of the control for ProjectID


The important difference between my suggestion and your query criteria is
that one uses the main form and your example uses the subform.

Try the query with the criteria like this:
Forms!frmHours_Worked_!ProjectID
(leave out any mention of controls or fields on the subform,
only use controls or fields on the main or parent form)

The criteria in the query needs to get the information from the main or
parent form, not the subform.

When you have the query correctly getting the data for ProjectID, then you
try getting the criteria for DisciplineName.
Post back if you have any problems with that one.
After you get the query showing the same data that shows on the subform,
then it is time to try exporting.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,

I did as you instructed, however now I am receiving a Run-time
error'3436':
Failure creating file.

I also built the query as you instructed however, when I make the
selection
on the Main Form, the query does not populate.

On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Maybe I should have mentioned that when the user is making a selection
from
the main form, he/she is also selecting a Start Date and End Date
(separate
comboboxes on the main form). In other words the user will make a
selection
from one or a combination of four comboboxes (ProjectID, Discipline Name,
Section Number or Last Name) and the also select the Start Date and End
Date, the click the select button.

Base on the selection the subform will populate with the information
requested.

Please give me more instruction on how to build the query used to Export
this information from the subform by using a button on the main form.

--
tmdrake

--Remove the lines about strSQL
--put qryHours_Worked(2) in quotes because TransferSpreadsheet expects a
string for this argument
--remove the last 2 arguments of TransferSpreadsheet as you don't need
them
for this export.


Private Sub Export_to_Excel_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryHours_Worked(2)",
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls"

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Jeanette again thanks for you help.

Below is the code that I am using for the export button;

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
Debug.Print "strSQL:" & strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryHours_Worked(2),
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

However, now I am receiving the following error:
The expression On Click you entered as the event property setting
produced
the following error: Sub or Function no defined.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Proceudre].
*There may have been an error evaluation the function, event, or macro.

What does this mean.


--
tmdrake


:

back with exporting the subform data to excel.
I have checked this again, my memory of how to do it was faulty.
It is a bit more complicated than I first suggested.

The easiest way to do this is to set up a saved query.
In the query put criteria for each field that can be filtered on in
the
main
form.

So build a saved query using tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName . . .
and so on to include all fields you want in the report.

Under the column for ProjectID, put a criteria that selects the
ProjectID
from the main form, something like this:

Forms!frmMainName!ProjectIDControlName

Replace frmMainName with the name of your parent form
Replace ProjectIDControlName with the name of the control for
ProjectID

Save the query.
Open the parent form and while it is open, also open the saved query
from
above and check that it gives the results where the subform data
matches
the
ProjectID chosen in the parent form.

Repeat the process for each field where the user can choose a value on
the
parent form.
You will end up with a query that will give you all the data that
matches
the selections made on the parent form.

Use TransferSpreadsheet to export the query to excel.


Jeanette Cunningham -- Melbourne Victoria Australia



Jeanette, thanks so much for all of your help. I apoloize for not
responding
sooner. Please see below for the information you requested, Again
Thanks
so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] &
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " & Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake


:

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final
version
of
the
code that is not working.


Jeanette Cunningham -- Melbourne Victoria Australia


I did as you instructed. When placing the query string in a new
query,
there
were no errors. The query worked fine. However, I still get the
same
error
when selecting the Export button.

Also, You are right in assuming the strSQL is the full
recordsource
query
for the subform.

Your Help is greatly appreciated.
--
tmdrake


:

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path
and
file
name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for the
subform.
Is
this correct?

Copy the query string from the immediate window and paste in
into

 

[Jeanette Cunningham]

Please post back with
--the name of the main form
--the name of the subform
--the name of the subform control
--the name of the textbox or combo or list box that has ProjectID as its
control source
--the primary key field for the main form
--the primary key field for the subform
--the query or table that is the record source of the main form
--the query or table that is the record source of the subform
--the names of the fields in the main form
--the names of the fields in the subform


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Sorry again for the delayed response, really busy at work.

However, I did what you stated in the previous reply (used
[Forms]![tblProject_Staffing_Resources]![ProjectID] as the criteria), only
now a parameter box appears requesting the ProjectID to base the query.
How
do I get the query to read from the Mainform?

Also how can I use multiple critieria in the query? If the user wants to
make more than one selection from the combo boxes.

Thanks again for your help and patience.
--
tmdrake

Hi,
the query you build to get the subform data gets its criteria from the
main
form.
Where you say

On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Here is part of my answer from previously
-->Forms!frmMainName!ProjectIDControlName

-->Replace frmMainName with the name of your parent form
-->Replace ProjectIDControlName with the name of the control for
ProjectID


The important difference between my suggestion and your query criteria is
that one uses the main form and your example uses the subform.

Try the query with the criteria like this:
Forms!frmHours_Worked_!ProjectID
(leave out any mention of controls or fields on the subform,
only use controls or fields on the main or parent form)

The criteria in the query needs to get the information from the main or
parent form, not the subform.

When you have the query correctly getting the data for ProjectID, then
you
try getting the criteria for DisciplineName.
Post back if you have any problems with that one.
After you get the query showing the same data that shows on the subform,
then it is time to try exporting.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,

I did as you instructed, however now I am receiving a Run-time
error'3436':
Failure creating file.

I also built the query as you instructed however, when I make the
selection
on the Main Form, the query does not populate.

On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Maybe I should have mentioned that when the user is making a selection
from
the main form, he/she is also selecting a Start Date and End Date
(separate
comboboxes on the main form). In other words the user will make a
selection
from one or a combination of four comboboxes (ProjectID, Discipline
Name,
Section Number or Last Name) and the also select the Start Date and
End
Date, the click the select button.

Base on the selection the subform will populate with the information
requested.

Please give me more instruction on how to build the query used to
Export
this information from the subform by using a button on the main form.

--
tmdrake


:

--Remove the lines about strSQL
--put qryHours_Worked(2) in quotes because TransferSpreadsheet expects
a
string for this argument
--remove the last 2 arguments of TransferSpreadsheet as you don't need
them
for this export.


Private Sub Export_to_Excel_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryHours_Worked(2)",
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls"

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Jeanette again thanks for you help.

Below is the code that I am using for the export button;

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
Debug.Print "strSQL:" & strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryHours_Worked(2),
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

However, now I am receiving the following error:
The expression On Click you entered as the event property setting
produced
the following error: Sub or Function no defined.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Proceudre].
*There may have been an error evaluation the function, event, or
macro.

What does this mean.


--
tmdrake


:

back with exporting the subform data to excel.
I have checked this again, my memory of how to do it was faulty.
It is a bit more complicated than I first suggested.

The easiest way to do this is to set up a saved query.
In the query put criteria for each field that can be filtered on in
the
main
form.

So build a saved query using tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName . . .
and so on to include all fields you want in the report.

Under the column for ProjectID, put a criteria that selects the
ProjectID
from the main form, something like this:

Forms!frmMainName!ProjectIDControlName

Replace frmMainName with the name of your parent form
Replace ProjectIDControlName with the name of the control for
ProjectID

Save the query.
Open the parent form and while it is open, also open the saved
query
from
above and check that it gives the results where the subform data
matches
the
ProjectID chosen in the parent form.

Repeat the process for each field where the user can choose a value
on
the
parent form.
You will end up with a query that will give you all the data that
matches
the selections made on the parent form.

Use TransferSpreadsheet to export the query to excel.


Jeanette Cunningham -- Melbourne Victoria Australia



Jeanette, thanks so much for all of your help. I apoloize for
not
responding
sooner. Please see below for the information you requested,
Again
Thanks
so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" &
Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber]
&
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " &
Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake


:

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final
version
of
the
code that is not working.


Jeanette Cunningham -- Melbourne Victoria Australia


I did as you instructed. When placing the query string in a
new
query,
there
were no errors. The query worked fine. However, I still get
the
same
error
when selecting the Export button.

Also, You are right in assuming the strSQL is the full
recordsource
query
for the subform.

Your Help is greatly appreciated.
--
tmdrake


:

tmdrake,
to debug, try this:
In the export sub put an extra line as shown with **
but remove the ** in the actual code.

Private Sub cmdExportXL_Click()
strSQL = HoursExportXL
**Debug.Print "strSQL: " & strSQL**
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS, "full path
and
file
name"
End Sub

Now run the form and click the export button.
Press Ctl + G to open the immediate window.
Access will show you what it found for strSQL

I am assuming that strSQL is the full recordsource query for
the
subform.
Is
this correct?

Copy the query string from the immediate window and paste in
into

 

[tmdrake]

Jeanette, again thanks for you help. See below for the response to your
request for additional information. Hope this makes thing much clearer. If
you need any additional information, please let me know.

the name of the main form - frmProject Staffing Resources

Record source - tblHours_Worked
cboProjectID - row source - qryProjectID
cboDisciplineName - row source - qryDisciplineName
cboSectionNumber - row source - qrySectionNumber
cboLastName - row source - qryLastName
txtbox - Start_Date
txtbox - EndDate
Command Button "Select" - vba code
Command Button "Clear" - vba code

the name of the subform - frmHours_Worked_subform

Record Source: SELECT tblHours_Worked.ProjectID,
tblHours_Worked.DisciplineName, tblHours_Worked.SectionNumber,
tblHours_Worked.LastName, tblHours_Worked.FirstName, tblHours_Worked.[SLC
Code], tblHours_Worked.[Week Ending], tblHours_Worked.PHW,
tblHours_Worked.AHW FROM tblHours_Worked WHERE True And [SectionNumber]=4430
And [Week Ending]>=#7/1/2008# And [Week Ending]<=#7/30/2008#;

the name of the subform control - N/A

the name of the textbox or combo or list box that has ProjectID as its
control source - See above
the primary key field for the main form - Autonumber
the primary key field for the subform - Autonumber
the query or table that is the record source of the main form - See above.
the query or table that is the record source of the subform - See above.
the names of the fields in the main form - See above.
the names of the fields in the subform - See Above

Thanks again.
--
tmdrake

Please post back with
--the name of the main form
--the name of the subform
--the name of the subform control
--the name of the textbox or combo or list box that has ProjectID as its
control source
--the primary key field for the main form
--the primary key field for the subform
--the query or table that is the record source of the main form
--the query or table that is the record source of the subform
--the names of the fields in the main form
--the names of the fields in the subform


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Sorry again for the delayed response, really busy at work.

However, I did what you stated in the previous reply (used
[Forms]![tblProject_Staffing_Resources]![ProjectID] as the criteria), only
now a parameter box appears requesting the ProjectID to base the query.
How
do I get the query to read from the Mainform?

Also how can I use multiple critieria in the query? If the user wants to
make more than one selection from the combo boxes.

Thanks again for your help and patience.
--
tmdrake

Hi,
the query you build to get the subform data gets its criteria from the
main
form.
Where you say
On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Here is part of my answer from previously
-->Forms!frmMainName!ProjectIDControlName

-->Replace frmMainName with the name of your parent form
-->Replace ProjectIDControlName with the name of the control for
ProjectID


The important difference between my suggestion and your query criteria is
that one uses the main form and your example uses the subform.

Try the query with the criteria like this:
Forms!frmHours_Worked_!ProjectID
(leave out any mention of controls or fields on the subform,
only use controls or fields on the main or parent form)

The criteria in the query needs to get the information from the main or
parent form, not the subform.

When you have the query correctly getting the data for ProjectID, then
you
try getting the criteria for DisciplineName.
Post back if you have any problems with that one.
After you get the query showing the same data that shows on the subform,
then it is time to try exporting.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi Jeanette,

I did as you instructed, however now I am receiving a Run-time
error'3436':
Failure creating file.

I also built the query as you instructed however, when I make the
selection
on the Main Form, the query does not populate.

On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Maybe I should have mentioned that when the user is making a selection
from
the main form, he/she is also selecting a Start Date and End Date
(separate
comboboxes on the main form). In other words the user will make a
selection
from one or a combination of four comboboxes (ProjectID, Discipline
Name,
Section Number or Last Name) and the also select the Start Date and
End
Date, the click the select button.

Base on the selection the subform will populate with the information
requested.

Please give me more instruction on how to build the query used to
Export
this information from the subform by using a button on the main form.

--
tmdrake


:

--Remove the lines about strSQL
--put qryHours_Worked(2) in quotes because TransferSpreadsheet expects
a
string for this argument
--remove the last 2 arguments of TransferSpreadsheet as you don't need
them
for this export.


Private Sub Export_to_Excel_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryHours_Worked(2)",
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls"

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Jeanette again thanks for you help.

Below is the code that I am using for the export button;

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
Debug.Print "strSQL:" & strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryHours_Worked(2),
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

However, now I am receiving the following error:
The expression On Click you entered as the event property setting
produced
the following error: Sub or Function no defined.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Proceudre].
*There may have been an error evaluation the function, event, or
macro.

What does this mean.


--
tmdrake


:

back with exporting the subform data to excel.
I have checked this again, my memory of how to do it was faulty.
It is a bit more complicated than I first suggested.

The easiest way to do this is to set up a saved query.
In the query put criteria for each field that can be filtered on in
the
main
form.

So build a saved query using tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName . . .
and so on to include all fields you want in the report.

Under the column for ProjectID, put a criteria that selects the
ProjectID
from the main form, something like this:

Forms!frmMainName!ProjectIDControlName

Replace frmMainName with the name of your parent form
Replace ProjectIDControlName with the name of the control for
ProjectID

Save the query.
Open the parent form and while it is open, also open the saved
query
from
above and check that it gives the results where the subform data
matches
the
ProjectID chosen in the parent form.

Repeat the process for each field where the user can choose a value
on
the
parent form.
You will end up with a query that will give you all the data that
matches
the selections made on the parent form.

Use TransferSpreadsheet to export the query to excel.


Jeanette Cunningham -- Melbourne Victoria Australia



Jeanette, thanks so much for all of your help. I apoloize for
not
responding
sooner. Please see below for the information you requested,
Again
Thanks
so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" &
Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber]
&
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " &
Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " & Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake


:

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final
version
of
the
code that is not working.

 

[Jeanette Cunningham]

Hi tmdrake,
here is the procedure that works for me.
Create a query based on tblHours_Worked
Include all the fields you need.
Save the query as qryExpt.

For a simple example, we will just make a selection from cboProjectID.
Open qryExpt in design view.
In the criteria row for ProjectID, type the following:
Forms![frmProject Staffing Resources]!cboProjectID
Save the query.

On the form frmProject Staffing Resources, put a command button.
For its on click event put code like this:

Private Sub Command20_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExpt",
"C:\Staffing.xls"
End Sub

note that qryExpt is the name of the query to export
C:\Staffing.xls is the name for the excel file to put the exported data
into.

Now open frmProject Staffing Resources,
choose a projectid and click the button.
The data will be exported to excel.
You should be able to find the file called Staffing.xls on the C drive and
it should contain the data matching the projectid selected on the form.

When you have it working using ProjectID, add another criteria to the query
such as DisciplineName.
Test and get the export working for this.
Then add the next criteria and so on until you have covered all the ones you
want.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette, again thanks for you help. See below for the response to your
request for additional information. Hope this makes thing much clearer.
If
you need any additional information, please let me know.

the name of the main form - frmProject Staffing Resources

Record source - tblHours_Worked
cboProjectID - row source - qryProjectID
cboDisciplineName - row source - qryDisciplineName
cboSectionNumber - row source - qrySectionNumber
cboLastName - row source - qryLastName
txtbox - Start_Date
txtbox - EndDate
Command Button "Select" - vba code
Command Button "Clear" - vba code

the name of the subform - frmHours_Worked_subform

Record Source: SELECT tblHours_Worked.ProjectID,
tblHours_Worked.DisciplineName, tblHours_Worked.SectionNumber,
tblHours_Worked.LastName, tblHours_Worked.FirstName, tblHours_Worked.[SLC
Code], tblHours_Worked.[Week Ending], tblHours_Worked.PHW,
tblHours_Worked.AHW FROM tblHours_Worked WHERE True And
[SectionNumber]=4430
And [Week Ending]>=#7/1/2008# And [Week Ending]<=#7/30/2008#;

the name of the subform control - N/A

the name of the textbox or combo or list box that has ProjectID as its
control source - See above
the primary key field for the main form - Autonumber
the primary key field for the subform - Autonumber
the query or table that is the record source of the main form - See above.
the query or table that is the record source of the subform - See above.
the names of the fields in the main form - See above.
the names of the fields in the subform - See Above

Thanks again.
--
tmdrake

Please post back with
--the name of the main form
--the name of the subform
--the name of the subform control
--the name of the textbox or combo or list box that has ProjectID as its
control source
--the primary key field for the main form
--the primary key field for the subform
--the query or table that is the record source of the main form
--the query or table that is the record source of the subform
--the names of the fields in the main form
--the names of the fields in the subform


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Sorry again for the delayed response, really busy at work.

However, I did what you stated in the previous reply (used
[Forms]![tblProject_Staffing_Resources]![ProjectID] as the criteria),
only
now a parameter box appears requesting the ProjectID to base the query.
How
do I get the query to read from the Mainform?

Also how can I use multiple critieria in the query? If the user wants
to
make more than one selection from the combo boxes.

Thanks again for your help and patience.
--
tmdrake


:

Hi,
the query you build to get the subform data gets its criteria from the
main
form.
Where you say
On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Here is part of my answer from previously
-->Forms!frmMainName!ProjectIDControlName

-->Replace frmMainName with the name of your parent form
-->Replace ProjectIDControlName with the name of the control for
ProjectID


The important difference between my suggestion and your query criteria
is
that one uses the main form and your example uses the subform.

Try the query with the criteria like this:
Forms!frmHours_Worked_!ProjectID
(leave out any mention of controls or fields on the subform,
only use controls or fields on the main or parent form)

The criteria in the query needs to get the information from the main
or
parent form, not the subform.

When you have the query correctly getting the data for ProjectID, then
you
try getting the criteria for DisciplineName.
Post back if you have any problems with that one.
After you get the query showing the same data that shows on the
subform,
then it is time to try exporting.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi Jeanette,

I did as you instructed, however now I am receiving a Run-time
error'3436':
Failure creating file.

I also built the query as you instructed however, when I make the
selection
on the Main Form, the query does not populate.

On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Maybe I should have mentioned that when the user is making a
selection
from
the main form, he/she is also selecting a Start Date and End Date
(separate
comboboxes on the main form). In other words the user will make a
selection
from one or a combination of four comboboxes (ProjectID, Discipline
Name,
Section Number or Last Name) and the also select the Start Date and
End
Date, the click the select button.

Base on the selection the subform will populate with the information
requested.

Please give me more instruction on how to build the query used to
Export
this information from the subform by using a button on the main
form.

--
tmdrake


:

--Remove the lines about strSQL
--put qryHours_Worked(2) in quotes because TransferSpreadsheet
expects
a
string for this argument
--remove the last 2 arguments of TransferSpreadsheet as you don't
need
them
for this export.


Private Sub Export_to_Excel_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryHours_Worked(2)",
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls"

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Jeanette again thanks for you help.

Below is the code that I am using for the export button;

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
Debug.Print "strSQL:" & strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryHours_Worked(2),
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

However, now I am receiving the following error:
The expression On Click you entered as the event property setting
produced
the following error: Sub or Function no defined.

*The expression may not result in the name of a macro, the name
of a
user-defined function, or [Event Proceudre].
*There may have been an error evaluation the function, event, or
macro.

What does this mean.


--
tmdrake


:

back with exporting the subform data to excel.
I have checked this again, my memory of how to do it was faulty.
It is a bit more complicated than I first suggested.

The easiest way to do this is to set up a saved query.
In the query put criteria for each field that can be filtered on
in
the
main
form.

So build a saved query using tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName . . .
and so on to include all fields you want in the report.

Under the column for ProjectID, put a criteria that selects the
ProjectID
from the main form, something like this:

Forms!frmMainName!ProjectIDControlName

Replace frmMainName with the name of your parent form
Replace ProjectIDControlName with the name of the control for
ProjectID

Save the query.
Open the parent form and while it is open, also open the saved
query
from
above and check that it gives the results where the subform data
matches
the
ProjectID chosen in the parent form.

Repeat the process for each field where the user can choose a
value
on
the
parent form.
You will end up with a query that will give you all the data
that
matches
the selections made on the parent form.

Use TransferSpreadsheet to export the query to excel.


Jeanette Cunningham -- Melbourne Victoria Australia



Jeanette, thanks so much for all of your help. I apoloize for
not
responding
sooner. Please see below for the information you requested,
Again
Thanks
so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" &
Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " &
Me![SectionNumber]
&
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] &
"'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " &
Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " &
Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake


:

I think it is time to look at the code again.
Would you post the code as you have it now - that's the final
version
of
the
code that is not working.

 

[Jeanette Cunningham]

In your situation the form and subform are both based on the same table.
The method I gave you in the last post works best if the main form is based
on one table and the subform is based on a child table related to the table
from the main form.
Setting up the query with criteria like
Forms![frmStaffing]!cboDisciplineName works best when the criteria being set
are in the main table but not in the child table.

When I want to export to excel, I nearly always build a SQL string and
append to it a table I use for just exports.

To build the SQL string, do it like the sample search form on Allen Brown's
web site.
Instead of creating a filter, create a where clause to build the SQL to put
this query in a table for export.
You can export the table to excel.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi tmdrake,
here is the procedure that works for me.
Create a query based on tblHours_Worked
Include all the fields you need.
Save the query as qryExpt.

For a simple example, we will just make a selection from cboProjectID.
Open qryExpt in design view.
In the criteria row for ProjectID, type the following:
Forms![frmProject Staffing Resources]!cboProjectID
Save the query.

On the form frmProject Staffing Resources, put a command button.
For its on click event put code like this:

Private Sub Command20_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExpt",
"C:\Staffing.xls"
End Sub

note that qryExpt is the name of the query to export
C:\Staffing.xls is the name for the excel file to put the exported data
into.

Now open frmProject Staffing Resources,
choose a projectid and click the button.
The data will be exported to excel.
You should be able to find the file called Staffing.xls on the C drive and
it should contain the data matching the projectid selected on the form.

When you have it working using ProjectID, add another criteria to the
query such as DisciplineName.
Test and get the export working for this.
Then add the next criteria and so on until you have covered all the ones
you want.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette, again thanks for you help. See below for the response to your
request for additional information. Hope this makes thing much clearer.
If
you need any additional information, please let me know.

the name of the main form - frmProject Staffing Resources

Record source - tblHours_Worked
cboProjectID - row source - qryProjectID
cboDisciplineName - row source - qryDisciplineName
cboSectionNumber - row source - qrySectionNumber
cboLastName - row source - qryLastName
txtbox - Start_Date
txtbox - EndDate
Command Button "Select" - vba code
Command Button "Clear" - vba code

the name of the subform - frmHours_Worked_subform

Record Source: SELECT tblHours_Worked.ProjectID,
tblHours_Worked.DisciplineName, tblHours_Worked.SectionNumber,
tblHours_Worked.LastName, tblHours_Worked.FirstName, tblHours_Worked.[SLC
Code], tblHours_Worked.[Week Ending], tblHours_Worked.PHW,
tblHours_Worked.AHW FROM tblHours_Worked WHERE True And
[SectionNumber]=4430
And [Week Ending]>=#7/1/2008# And [Week Ending]<=#7/30/2008#;

the name of the subform control - N/A

the name of the textbox or combo or list box that has ProjectID as its
control source - See above
the primary key field for the main form - Autonumber
the primary key field for the subform - Autonumber
the query or table that is the record source of the main form - See
above.
the query or table that is the record source of the subform - See above.
the names of the fields in the main form - See above.
the names of the fields in the subform - See Above

Thanks again.
--
tmdrake

Please post back with
--the name of the main form
--the name of the subform
--the name of the subform control
--the name of the textbox or combo or list box that has ProjectID as its
control source
--the primary key field for the main form
--the primary key field for the subform
--the query or table that is the record source of the main form
--the query or table that is the record source of the subform
--the names of the fields in the main form
--the names of the fields in the subform


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Sorry again for the delayed response, really busy at work.

However, I did what you stated in the previous reply (used
[Forms]![tblProject_Staffing_Resources]![ProjectID] as the criteria),
only
now a parameter box appears requesting the ProjectID to base the
query.
How
do I get the query to read from the Mainform?

Also how can I use multiple critieria in the query? If the user wants
to
make more than one selection from the combo boxes.

Thanks again for your help and patience.
--
tmdrake


:

Hi,
the query you build to get the subform data gets its criteria from
the
main
form.
Where you say
On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Here is part of my answer from previously
-->Forms!frmMainName!ProjectIDControlName

-->Replace frmMainName with the name of your parent form
-->Replace ProjectIDControlName with the name of the control for
ProjectID


The important difference between my suggestion and your query
criteria
is
that one uses the main form and your example uses the subform.

Try the query with the criteria like this:
Forms!frmHours_Worked_!ProjectID
(leave out any mention of controls or fields on the subform,
only use controls or fields on the main or parent form)

The criteria in the query needs to get the information from the main
or
parent form, not the subform.

When you have the query correctly getting the data for ProjectID,
then
you
try getting the criteria for DisciplineName.
Post back if you have any problems with that one.
After you get the query showing the same data that shows on the
subform,
then it is time to try exporting.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi Jeanette,

I did as you instructed, however now I am receiving a Run-time
error'3436':
Failure creating file.

I also built the query as you instructed however, when I make the
selection
on the Main Form, the query does not populate.

On the query in the critiera filed I placed
Forms!frmHours_Worked_subform!ProjectID.

Maybe I should have mentioned that when the user is making a
selection
from
the main form, he/she is also selecting a Start Date and End Date
(separate
comboboxes on the main form). In other words the user will make a
selection
from one or a combination of four comboboxes (ProjectID, Discipline
Name,
Section Number or Last Name) and the also select the Start Date
and
End
Date, the click the select button.

Base on the selection the subform will populate with the
information
requested.

Please give me more instruction on how to build the query used to
Export
this information from the subform by using a button on the main
form.

--
tmdrake


:

--Remove the lines about strSQL
--put qryHours_Worked(2) in quotes because TransferSpreadsheet
expects
a
string for this argument
--remove the last 2 arguments of TransferSpreadsheet as you don't
need
them
for this export.


Private Sub Export_to_Excel_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryHours_Worked(2)",
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls"

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Jeanette again thanks for you help.

Below is the code that I am using for the export button;

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
Debug.Print "strSQL:" & strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryHours_Worked(2),
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

However, now I am receiving the following error:
The expression On Click you entered as the event property
setting
produced
the following error: Sub or Function no defined.

*The expression may not result in the name of a macro, the name
of a
user-defined function, or [Event Proceudre].
*There may have been an error evaluation the function, event, or
macro.

What does this mean.


--
tmdrake


:

back with exporting the subform data to excel.
I have checked this again, my memory of how to do it was
faulty.
It is a bit more complicated than I first suggested.

The easiest way to do this is to set up a saved query.
In the query put criteria for each field that can be filtered
on
in
the
main
form.

So build a saved query using tblHours_Worked].ProjectID,
[tblHours_Worked].DisciplineName . . .
and so on to include all fields you want in the report.

Under the column for ProjectID, put a criteria that selects the
ProjectID
from the main form, something like this:

Forms!frmMainName!ProjectIDControlName

Replace frmMainName with the name of your parent form
Replace ProjectIDControlName with the name of the control for
ProjectID

Save the query.
Open the parent form and while it is open, also open the saved
query
from
above and check that it gives the results where the subform
data
matches
the
ProjectID chosen in the parent form.

Repeat the process for each field where the user can choose a
value
on
the
parent form.
You will end up with a query that will give you all the data
that
matches
the selections made on the parent form.

Use TransferSpreadsheet to export the query to excel.


Jeanette Cunningham -- Melbourne Victoria Australia



Jeanette, thanks so much for all of your help. I apoloize
for
not
responding
sooner. Please see below for the information you requested,
Again
Thanks
so
much.


Code for selections and display in subform:

Private Function HoursExportXL() As String
Dim strSQL As String

strSQL = "SELECT [tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[SLC Code], " _
& "[tblHours_Worked].[Week Ending], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" &
Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " &
Me![SectionNumber]
&
""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] &
"'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
If Not IsNull(Me![Start_Date]) Then
strSQL = strSQL & " AND [Week Ending] >= " &
Format(Me![Start_Date],
"\#mm\/dd\/yyyy\#")
End If
If Not IsNull(Me![EndDate]) Then
strSQL = strSQL & " AND [Week Ending] <= " &
Format(Me![EndDate],
"\#mm\/dd\/yyyy\#")
End If
Debug.Print strSQL
HoursExportXL = strSQL
End Function


Code for Export to Excel Button:

Private Sub Export_to_Excel_Click()
strSQL = HoursExportXL
DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS,
"\\Escfil02\4400_Shared\4400_Project_Control\Resource
Management
DB\pivtblHours_Worked_template_Section.xls", True, ""
End Sub

Please let me know if you need any additional information.
--
tmdrake


:

I think it is time to look at the code again.
Would you post the code as you have it now - that's the
final
version
of
the
code that is not working.