Solved

Help with Access to Excel export

Posted on 2011-03-21
45
251 Views
Last Modified: 2012-05-11
Hi, I asked for help on this before, but I decided to tackle it differently. But now I am getting a run-time error 3061 ...Too few paramenters expected 4. I have a form with date, department, position,and location field also with my export button. The user should be able to select a department from the drop down or All to export all departments to an excel spreadsheet where each department would be sent to there own tab in the single spreadsheet. If CC Center is selected only, that information should be exported. Can anyone see why my code throws errors? I figured if I can get at least one working, I should be able to figure out the others. The C Center Dept uses position and location fields and I thought my code would hard code the information so the user would only have to enter the date. But not sure why its not working.

Private Sub Command36_Click()
Dim xlapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strDate As String
Dim strPosition As String
Dim strDepartment As String
Dim strLocation As String
Dim strVolCat As String

Dim asRef(3) As String
Dim i As Integer
Dim bCopyAll As Boolean ' True equals - Copy All
    bCopyAll = False
    

strSQL = "Select [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM qryAgentSummary_Crosstab;"
Set xlapp = New Excel.Application
Set wb = xlapp.Workbooks.Open("C:\Documents and Settings\user\Desktop\Temp.xlsx")


    asRef(0) = "Call Center"
    asRef(1) = "004"
    asRef(2) = "007"
    asRef(3) = "216"
    
    'Select Case [Forms]![frmAgentSummaryExport]![cboDepartment]
    Select Case [Forms]![frmAgentSummaryExport]![txtBusinessDate]
    

    Case "Call Center"
        strDepartment = "Call Center"
        strPosition = "AO"
        strPosition = "CCR"
    
    
    Case "004"
        strDepartment = "004"
        strPosition = "AO"
        strPosition = "FSO"
        
    Case "007"
        strDepartment = "007"
        strPosition = "AO"
        strPosition = "FSO"
        
    Case "216"
        strDepartment = "216"
        strPosition = "AO"
        strPosition = "FSO"
        
    Case Else ' Copy ALL
        bCopyAll = True
    
    End Select
    
        For i = 0 To 3
            If bCopyAll = True Then
                If i = 0 Then
                    strDepartment = asRef(i)
                Else
                    strDepartment = strDepartment & ", " & asRef(i)
                End If
            Else
                i = 3
            End If
            
            strSQL = "Select [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM qryAgentSummary_Crosstab;"
                        
            Set ws = wb.Worksheets(strDepartment)
            Set rs = CurrentDb.OpenRecordset(strSQL)
            ws.range("A2").CopyFromRecordset rs
    
        Next i

        bCopyAll = False
        
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
    Set rs = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set xlapp = Nothing

End Sub

Open in new window

0
Comment
Question by:KLMServices
  • 23
  • 20
  • 2
45 Comments
 
LVL 21
Comment Utility
Does the query qryAgentSummary_Crosstab have parameters?

If yes,  you have sevel options. Here are a few:
1)  appending the data from qryAgentSummary_Crosstab into a table first.
2) Modify the querydef for qryAgentSummary_Crosstab  with your VBA code to insert the paramter vaules so there is not form references or prompts..
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Is the error occuring in here?
            strSQL = "Select [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM qryAgentSummary_Crosstab;"
                       
            Set ws = wb.Worksheets(strDepartment)
            Set rs = CurrentDb.OpenRecordset(strSQL)
            ws.range("A2").CopyFromRecordset rs

Does the query qryAgentSummary_Crosstab have criteria expressions referring to form fields?
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
example

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("qryAgencySummary_Crosstab")
qdf.Parameters("Forms!MyForm!SomeField") = Forms!MyForm!SomeField
qdf.Parameters("Forms!MyForm!AnotherField") = Forms!MyForm!AnotherField
etc.

Set rs = qdf.OpenRecordset

OM Gang
0
 

Author Comment

by:KLMServices
Comment Utility
Yes it does debug at:
 Set rs = CurrentDb.OpenRecordset(strSQL)

Yes, qryAgentSummary_Crosstab is referencing criteria expressions referring to form fields. below is the what my crosstab contains.

TRANSFORM Sum(tblAgentSummary.F4) AS SumOfF4
SELECT tblAgents.[Agent Name]
FROM tblAgents INNER JOIN tblAgentSummary ON tblAgents.[AgentID#] = tblAgentSummary.F3
WHERE (((tblAgentSummary.callDate) Between [forms]![frmAgentSummaryExport]![txtBusinessDate] And [forms]![frmAgentSummaryExport]![txtEndDate]) AND ((tblAgents.Position)=[forms]![frmAgentSummaryExport]![cboPosition]) AND ((tblAgents.Department)=[forms]![frmAgentSummaryExport]![cboDepartment]))
GROUP BY tblAgents.[Agent Name]
PIVOT tblAgentSummary.F1 In ("Salo","Other","GiftCard","Lend","Mort","Web");

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Give this a shot.
OM Gang

Add to your declarations
Dim qdf As DAO.QueryDef



            strSQL = "Select [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM qryAgentSummary_Crosstab;"

            Set qdf = CurrentDb.CreateQueryDef("", strSQL)
            qdf.Parameters("[forms]![frmAgentSummaryExport]![txtBusinessDate]") = [forms]![frmAgentSummaryExport]![txtBusinessDate]
            qdf.Parameters("[forms]![frmAgentSummaryExport]![txtEndDate]") = [forms]![frmAgentSummaryExport]![txtEndDate]
            qdf.Parameters("[forms]![frmAgentSummaryExport]![cboPosition]") = [forms]![frmAgentSummaryExport]![cboPosition]
            qdf.Parameters("[forms]![frmAgentSummaryExport]![cboDepartment]") = [forms]![frmAgentSummaryExport]![cboDepartment]
                       
            Set ws = wb.Worksheets(strDepartment)
            'Set rs = CurrentDb.OpenRecordset(strSQL)
            Set rs = qdf.OpenRecordset
            ws.range("A2").CopyFromRecordset rs

0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Forgot that you should also add to the end of your sub
Set qdf = Nothing

OM Gang
0
 
LVL 21
Comment Utility
Alternative solution:
If you dont want to go through all the code then you can run a make table query based on your crosstab. Use this new table as the export source. Just a few lines of code at most.  
0
 

Author Comment

by:KLMServices
Comment Utility
Thanks, think I am making progress. I am now getting a run-time error 9 ...Subscript out of range.

Debugs at "Set ws = wb.Worksheets(strDepartment)"
I made certain that my worksheet has a tab for all departments.

Thanks
0
 

Author Comment

by:KLMServices
Comment Utility
I have this line of code inserted, should it be removed?
Select Case [Forms]![frmAgentSummaryExport]![cboDepartment]
'Select Case [Forms]![frmAgentSummaryExport]![txtBusinessDate]

Open in new window

Below is what I have after all the changes.

Private Sub Command36_Click()
Dim xlapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strDate As String
Dim strPosition As String
Dim strDepartment As String
Dim strLocation As String
Dim strVolCat As String
Dim qdf As DAO.QueryDef


Dim asRef(3) As String
Dim i As Integer
Dim bCopyAll As Boolean ' True equals - Copy All
    bCopyAll = False
    

strSQL = "Select [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM qryAgentSummary_Crosstab;"
Set xlapp = New Excel.Application
Set wb = xlapp.Workbooks.Open("C:\Documents and Settings\user\Desktop\Temp.xlsx")


    asRef(0) = "Call Center"
    asRef(1) = "004"
    asRef(2) = "007"
    asRef(3) = "216"
    
    Select Case [Forms]![frmAgentSummaryExport]![cboDepartment]
    'Select Case [Forms]![frmAgentSummaryExport]![txtBusinessDate]
    

    Case "Call Center"
        strDepartment = "Call Center"
        strPosition = "AO"
        strPosition = "CCR"
    
    
    Case "004"
        strDepartment = "004"
        strPosition = "AO"
        strPosition = "FSO"
        
    Case "007"
        strDepartment = "007"
        strPosition = "AO"
        strPosition = "FSO"
        
    Case "216"
        strDepartment = "216"
        strPosition = "AO"
        strPosition = "FSO"
        
    Case Else ' Copy ALL
        bCopyAll = True
    
    End Select
    
        For i = 0 To 3
            If bCopyAll = True Then
                If i = 0 Then
                    strDepartment = asRef(i)
                Else
                    strDepartment = strDepartment & ", " & asRef(i)
                End If
            Else
                i = 3
            End If
            
             strSQL = "Select [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM qryAgentSummary_Crosstab;"

            Set qdf = CurrentDb.CreateQueryDef("", strSQL)
            qdf.Parameters("[forms]![frmAgentSummaryExport]![txtBusinessDate]") = [Forms]![frmAgentSummaryExport]![txtBusinessDate]
            qdf.Parameters("[forms]![frmAgentSummaryExport]![txtEndDate]") = [Forms]![frmAgentSummaryExport]![txtEndDate]
            qdf.Parameters("[forms]![frmAgentSummaryExport]![cboPosition]") = [Forms]![frmAgentSummaryExport]![cboPosition]
            qdf.Parameters("[forms]![frmAgentSummaryExport]![cboDepartment]") = [Forms]![frmAgentSummaryExport]![cboDepartment]
                        
            Set ws = wb.Worksheets(strDepartment)
            'Set rs = CurrentDb.OpenRecordset(strSQL)
            Set rs = qdf.OpenRecordset
            ws.range("A2").CopyFromRecordset rs


    
        Next i

        bCopyAll = False
        
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
    Set rs = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set xlapp = Nothing
    Set qdf = Nothing

End Sub

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
KLMServices, these are two different issues.  I believe the issue causing the 3061 error is resolved.  The issue with the subscript out of range error is 'most likely' being cause here
strDepartment = strDepartment & ", " & asRef(i)
...
...
Set ws = wb.Worksheets(strDepartment)

Place a break point in your sub and step through it.  When the error occurs what is the value of strDepartment?  At some point I'm guessin it equals something like
004, 007

OM Gang
0
 

Author Comment

by:KLMServices
Comment Utility
I got

strDepartment="Call Center, 004"
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Do you have worksheets named for every value of strDepartment being generated?  Is there a worksheet named Call Center, 004?
OM Gang
0
 

Author Comment

by:KLMServices
Comment Utility
Yes, thats the first thing I checked when I got that error.
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Please post a copy of the Excel workbook.  Make sure no confidential information is inlcuded.
OM Gang
0
 

Author Comment

by:KLMServices
Comment Utility
Here you go, its just a plain workbook.
Temp.xlsx
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
KLMServices, thanks for posting the workbook.
I see worksheets named
Call Center
004
007
216

There is no worksheet named
Call Center, 004

When this command executes
Set ws = wb.Worksheets(strDepartment)
and strDepartment = "Call Center, 004"
it is generating the Subscript out of range error you are receiving because it is attempting to open a worksheet that does not exist.  You need to take a look at your logic here
        For i = 0 To 3
            If bCopyAll = True Then
                If i = 0 Then
                    strDepartment = asRef(i)
                Else
                    strDepartment = strDepartment & ", " & asRef(i)
                End If
            Else
                i = 3
            End If
and determine what you really want to happen when bCopyAll = True AND i = 0.  As it is now, you are setting the value of strDepartment to equal whatever strDepartment currently equals PLUS a comma, a space and the value from the array at index i.


OM Gang
0
 

Author Comment

by:KLMServices
Comment Utility
Thanks for your help on this. I am really new at this and was trying to work with an example I found on another forum. Call Center  is one deparment and 004 is another department. I was trying to make it work as such. The user only needs to enter a date and select a department or they can select "All" to export all departments. If they selected department 004, only that data should be exported to the excel workbook. If "ALL" is selected, then all data should be exported to the correct sheet within the workbook. I will keep playing with it. Was hoping that it wouldn't be this much trouble. But certainly thanks for your help.
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
So, if ALL is selected, which worksheet should be populated?  All of them?
OM Gang
0
 

Author Comment

by:KLMServices
Comment Utility
They all should be populated once data is available for that each department.
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
So, let's step through the logic

If the user selects '004' in the cboDepartment combo box on the form you want the worksheet named '004' to be populated with data only for department 004.

If the user selects 'ALL' in the cboDepartment comob box on the form you want all four of the worksheets to be populated each with data only for its department.

You need to do two things:
1) set the recordset query to return the data for the appropriate department
2) select the correct worksheet to write data to

You're going to want to do something like this.  I haven't tested it but hopefully it gives you an idea of what you need to do.
OM Gang

Private Sub Command36_Click()
Dim xlapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strDate As String
Dim strPosition As String
Dim strDepartment As String
Dim strLocation As String
Dim strVolCat As String
Dim qdf As DAO.QueryDef

Dim asRef(3) As String
Dim i As Integer
Dim bCopyAll As Boolean ' True equals - Copy All
    bCopyAll = False
   

Set xlapp = New Excel.Application
Set wb = xlapp.Workbooks.Open("C:\Documents and Settings\user\Desktop\Temp.xlsx")


    asRef(0) = "Call Center"
    asRef(1) = "004"
    asRef(2) = "007"
    asRef(3) = "216"
   
    Select Case [Forms]![frmAgentSummaryExport]![cboDepartment]
    'Select Case [Forms]![frmAgentSummaryExport]![txtBusinessDate]
   

    Case "Call Center"
        strDepartment = "Call Center"
        strPosition = "AO"
        strPosition = "CCR"
   
   
    Case "004"
        strDepartment = "004"
        strPosition = "AO"
        strPosition = "FSO"
       
    Case "007"
        strDepartment = "007"
        strPosition = "AO"
        strPosition = "FSO"
       
    Case "216"
        strDepartment = "216"
        strPosition = "AO"
        strPosition = "FSO"
       
    Case Else ' Copy ALL
        bCopyAll = True
   
    End Select

    strSQL = "Select [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM qryAgentSummary_Crosstab;"
   
      If bCopyAll = True Then
            For i = 0 To 3
                Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                qdf.Parameters("[forms]![frmAgentSummaryExport]![txtBusinessDate]") = [Forms]![frmAgentSummaryExport]![txtBusinessDate]
                qdf.Parameters("[forms]![frmAgentSummaryExport]![txtEndDate]") = [Forms]![frmAgentSummaryExport]![txtEndDate]
                qdf.Parameters("[forms]![frmAgentSummaryExport]![cboPosition]") = [Forms]![frmAgentSummaryExport]![cboPosition]
            qdf.Parameters("[forms]![frmAgentSummaryExport]![cboDepartment]") = asRef(i)
            Set ws = wb.Worksheets(asRef(i))
            Set rs = qdf.OpenRecordset
            ws.Range("A2").CopyFromRecordset rs
                  Set qdf = Nothing
          Next i
      Else
                Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                qdf.Parameters("[forms]![frmAgentSummaryExport]![txtBusinessDate]") = [Forms]![frmAgentSummaryExport]![txtBusinessDate]
                qdf.Parameters("[forms]![frmAgentSummaryExport]![txtEndDate]") = [Forms]![frmAgentSummaryExport]![txtEndDate]
                qdf.Parameters("[forms]![frmAgentSummaryExport]![cboPosition]") = [Forms]![frmAgentSummaryExport]![cboPosition]
            qdf.Parameters("[forms]![frmAgentSummaryExport]![cboDepartment]") = strDepartment
            Set ws = wb.Worksheets(strDepartment)
            Set rs = qdf.OpenRecordset
            ws.Range("A2").CopyFromRecordset rs
        End If

        bCopyAll = False
       
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
   
    Set rs = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set xlapp = Nothing
    Set qdf = Nothing

End Sub
0
 

Author Comment

by:KLMServices
Comment Utility
Great, no errors when I used your changes, but no data was exported. It just prompted me to save the file. Maybe I missed a step.
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Add this

Debug.Print qdf.SQL

just before each of the recordset assignments
e.g. just before
Set rs = qdf.OpenRecordset

Run the procedure and check the Immediate window in the VBE for the output of the SQL statement.  Copy the SQL statement and paste it into a new query (SQL view).  Does it return the records you expect?
OM Gang
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:KLMServices
Comment Utility
No, it does not return the any records. I think it has to do with my select query. I have 2 queries, one labled "qryAgentSummary_Crosstab" and the other "qryAgentSummary"  Currently I use the qryAgentSummary when which is based on the qryAgentSummary_crosstab to generate my report manually. Which query should I used? I have included both SQL data for each query. In the mean-time, I am going to plug in each and see it that works. I tried to make the select statement smaller but, maybe I made more damange than good.

TRANSFORM Sum(tblAgentSummary.F4) AS SumOfF4
SELECT tblAgents.[Agent Name]
FROM tblAgents INNER JOIN tblAgentSummary ON tblAgents.[AgentID#] = tblAgentSummary.F3
WHERE (((tblAgentSummary.callDate) Between [forms]![frmAgentSummaryExport]![txtBusinessDate] And [forms]![frmAgentSummaryExport]![txtEndDate]) AND ((tblAgents.Position)=[forms]![frmAgentSummaryExport]![cboPosition]) AND ((tblAgents.Department)=[forms]![frmAgentSummaryExport]![cboDepartment]))
GROUP BY tblAgents.[Agent Name]
PIVOT tblAgentSummary.F1 In ("Salo","Other","GiftCard","Lend","Mort","Web");

Open in new window


SELECT qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web
FROM qryAgentSummary_Crosstab;

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
When you have the form open and you manually run qryAgentSummary_Crosstab does it return the expected records?  How about qryAgentSummary?
OM Gang
0
 

Author Comment

by:KLMServices
Comment Utility
It works when I use to do it manually for each department using the command below. But I that before I started on this new venture. I would have to select the date and all the drop down menu before running the procedure.

DoCmd.OutputTo acOutputQuery, "qryAgentSummary", acFormatXLSX, "C:\Documents and Settings\user\Desktop\Temp.xlsx", True
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
What happens if you do this instead?
OM Gang

Private Sub Command36_Click()
Dim xlapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strDate As String
Dim strPosition As String
Dim strDepartment As String
Dim strLocation As String
Dim strVolCat As String
Dim qdf As DAO.QueryDef

Dim asRef(3) As String
Dim i As Integer
Dim bCopyAll As Boolean ' True equals - Copy All
    bCopyAll = False
   

Set xlapp = New Excel.Application
Set wb = xlapp.Workbooks.Open("C:\Documents and Settings\user\Desktop\Temp.xlsx")


    asRef(0) = "Call Center"
    asRef(1) = "004"
    asRef(2) = "007"
    asRef(3) = "216"
   
    Select Case [Forms]![frmAgentSummaryExport]![cboDepartment]
    'Select Case [Forms]![frmAgentSummaryExport]![txtBusinessDate]
   

    Case "Call Center"
        strDepartment = "Call Center"
        strPosition = "AO"
        strPosition = "CCR"
   
   
    Case "004"
        strDepartment = "004"
        strPosition = "AO"
        strPosition = "FSO"
       
    Case "007"
        strDepartment = "007"
        strPosition = "AO"
        strPosition = "FSO"
       
    Case "216"
        strDepartment = "216"
        strPosition = "AO"
        strPosition = "FSO"
       
    Case Else ' Copy ALL
        bCopyAll = True
   
    End Select

    strSQL = "Select [Agent Name], Salo, (NZ(GiftCard))+(NZ(Other)) AS MSR, Lend, Mort, Web FROM qryAgentSummary_Crosstab;"
   
      If bCopyAll = True Then
            For i = 0 To 3
                'Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                Set qdf = CurrentDb.QueryDefs("qryAgentSummary")
                qdf.Parameters("[forms]![frmAgentSummaryExport]![txtBusinessDate]") = [Forms]![frmAgentSummaryExport]![txtBusinessDate]
                qdf.Parameters("[forms]![frmAgentSummaryExport]![txtEndDate]") = [Forms]![frmAgentSummaryExport]![txtEndDate]
                qdf.Parameters("[forms]![frmAgentSummaryExport]![cboPosition]") = [Forms]![frmAgentSummaryExport]![cboPosition]
            qdf.Parameters("[forms]![frmAgentSummaryExport]![cboDepartment]") = asRef(i)
            Set ws = wb.Worksheets(asRef(i))
            Set rs = qdf.OpenRecordset
            ws.Range("A2").CopyFromRecordset rs
                  Set qdf = Nothing
          Next i
      Else
                'Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                Set qdf = CurrentDb.QueryDefs("qryAgentSummary")
                qdf.Parameters("[forms]![frmAgentSummaryExport]![txtBusinessDate]") = [Forms]![frmAgentSummaryExport]![txtBusinessDate]
                qdf.Parameters("[forms]![frmAgentSummaryExport]![txtEndDate]") = [Forms]![frmAgentSummaryExport]![txtEndDate]
                qdf.Parameters("[forms]![frmAgentSummaryExport]![cboPosition]") = [Forms]![frmAgentSummaryExport]![cboPosition]
            qdf.Parameters("[forms]![frmAgentSummaryExport]![cboDepartment]") = strDepartment
            Set ws = wb.Worksheets(strDepartment)
            Set rs = qdf.OpenRecordset
            ws.Range("A2").CopyFromRecordset rs
        End If

        bCopyAll = False
       
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
   
    Set rs = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set xlapp = Nothing
    Set qdf = Nothing

End Sub
0
 

Author Comment

by:KLMServices
Comment Utility
Thank you for you patient on this with me. Still no data with the changes.
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
I've tried to duplicate what you are wanting to do and it is working for me.  In this sample code I experimented with both a query of a query (what you are attempting) and also an explicit SQL statement including a subquery.  Both function as expected and return the records I expect.  There must be a problem with your query or queries OR your form field references.

How large is your Access database and does it contain confidential data?  Perhaps you can strip out all the data, except some representative records, and post a copy of your db here so I can take a look.
OM Gang

Private Sub cmdRun_Click()
On Error GoTo Err_cmdRun_Click

    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim strQuery As String
   
    strQuery = "qrySubQueryParameterTest2"
    'strQuery = "SELECT Category, CategoryCode " _
            & "FROM (SELECT tblCategories_orig.* " _
            & "From tblCategories_orig " _
            & "WHERE (((tblCategories_orig.CategoryID)>=([Forms]![frmSubQueryParameterTest]![cboCategory]-1) " _
            & "And (tblCategories_orig.CategoryID)<=([Forms]![frmSubQueryParameterTest]![cboCategory]+1))));"

    'Set qdf = CurrentDb.CreateQueryDef("", strQuery)
    Set qdf = CurrentDb.QueryDefs(strQuery)
    qdf.Parameters("[Forms]![frmSubQueryParameterTest]![cboCategory]") = [Forms]![frmSubQueryParameterTest]![cboCategory]
    Set rs = qdf.OpenRecordset
    Debug.Print rs.RecordCount
    rs.MoveLast
    rs.MoveFirst
    Debug.Print rs.RecordCount
    Do Until rs.EOF
        Debug.Print rs(0) & " : " & rs(1)
        rs.MoveNext
    Loop

Exit_cmdRun_Click:
    Set rs = Nothing
    Set qdf = Nothing
    Exit Sub

Err_cmdRun_Click:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub cmdRun_Click of VBA Document Form_frmSubQueryParameterTest"
    Resume Exit_cmdRun_Click
End Sub
0
 

Author Comment

by:KLMServices
Comment Utility
Ok, here is my dba. I currently only have data for 03/16/2011 for testing purpose. Call Center requires Position of AO or CCR and the other departments require AO or FSO to generate results when using my orignal button.
TestDBA.accdb
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
KLMServices, are you sure the Excel workbooks aren't being populated?  Your app is functioning correctly for me.  The only change I made was the location of Temp.xlsx (in c:\temp on my computer).  Each time I change the selections in the combo boxes a different worksheet is populated in the workbook.  See attached.
OM Gang Temp2.xlsx Temp3.xlsx
0
 

Author Comment

by:KLMServices
Comment Utility
Not sure what I am doing wrong then. When I click the command button, it immediately opens Excel and the SAVE AS dialog box, which I don't understand why. I save the file and all sheets are blank. What could I be doing incorrectly?
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
When I click the button it also brings up the Save As dialog but the appropriate worksheet is not blank, e.g. it now has records.  Do you have full permissions to the directory where the template file is stored (C:\Documents and Settings\user\Desktop\Temp.xlsx)?

What are you putting in for dates?
OM Gang
0
 

Author Comment

by:KLMServices
Comment Utility
I am using the 03/16/2011 for the date. And I do have full permissions. The worksheets are black when the Dialog box is opened. Just can't figure it out.
0
 

Author Comment

by:KLMServices
Comment Utility
Ok, I figured out what is wrong. I was under the impression that I didn't need to select AO, or FSO from the Position field. Thats why the code below should have taken care off. That information is supposed to be hard coded. The user only needs to enter the date and select a department.

Case "Call Center"
        strDepartment = "Call Center"
        strPosition = "AO"
        strPosition = "CCR"
   
   
    Case "004"
        strDepartment = "004"
        strPosition = "AO"
        strPosition = "FSO"
       
    Case "007"
        strDepartment = "007"
        strPosition = "AO"
        strPosition = "FSO"
       
    Case "216"
        strDepartment = "216"
        strPosition = "AO"
        strPosition = "FSO"
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
I've attached another workbook.  I used starting data 3/16/2011 and ending date 3/16/2011.  I selected position = FSO and department = 004.  I clicked the button labeled 'New Export Button' and it generated the attached file.
OM Gang
Temp7.xlsx
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
<<Ok, I figured out what is wrong. I was under the impression that I didn't need to select AO, or FSO from the Position field. Thats why the code below should have taken care off. That information is supposed to be hard coded. The user only needs to enter the date and select a department. >>
It had to be something simple that was being overlooked.
You do have that code in your procedure but you're not actually doing anything with it.  The crosstab query is explicitly acquiring parameter data from the form fields.

A couple of things you'll need to address to get it the way you want:
1) you're assigning a value to the variable strPosition twice in each of the Case statements.  The second one will be used.  What is your intent/why are you assigning one value and then another?
2) you have no provision for strPosition when the user selects All

OM Gang
0
 

Author Comment

by:KLMServices
Comment Utility
I am sorry, I am making this more difficult than it needs to be.  In the call center we have 2 different types of employees. AO's and CCR's.  In the other departments, we have AO's and FSO's. If I select Call center, the excel spreadsheet should list all employees data first those with AO title and then the CCR's. Currently I have to run the procedure for each title. What I am doing here was trying to get it all done at once depending on the department selected.  I have included an example of what I was trying to do.  I was hoping it would be smart enough to know that the employees in the call center that are AO's and list their data in the Call Center tab and the same for the CCR's in the same tab below the AO's. Again, I was just trying to use coding from another user that seems to be doing what I wanted. But I know I have already waisted a bunch of your time and I appreciate it.
Temp-example.xlsx
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
KLMServices, I was working on it as well.  See the attached copy of your app.  I believe this is what you want.  I made changes to the button click procedure on the form and also made a change to the cross tab query.  Check it out.
OM Gang
TestDBA.accdb
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
<<But I know I have already waisted a bunch of your time>>
Not so.  It's why were both here (and most other on EE as well).
OM Gang
0
 

Author Comment

by:KLMServices
Comment Utility
OM Gang,

Thank you so much, Thank you, Thank you.  This is indeed what I was trying to accomplish. I am going thru the codes and learning how it all work together.  I will give full credit for the solution above, but just wanted to know if it will be difficult to have a break on each tab, so we know who are AO's and who are CCR or FSO?

Again, thank you.
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
Comment Utility
We can certainly sort the records so AOs list first but I'm not sure I understand what you mean by a break on each tab.  The simplest thing to do is to include the tblAgents.Position value in the output of each record.  I've created an example for you.
OM Gang
TestDBA.accdb
0
 

Author Comment

by:KLMServices
Comment Utility
Yes, what you did is what I mean't. But can I just put AO,CCR, FSO in it own column?
0
 
LVL 28

Assisted Solution

by:omgang
omgang earned 500 total points
Comment Utility
Replace the SQL for the cross tab query with this

TRANSFORM Sum(tblAgentSummary.F4) AS SumOfF4
SELECT tblAgents.[Agent Name], tblAgents.Position
FROM tblAgents INNER JOIN tblAgentSummary ON tblAgents.[AgentID#] = tblAgentSummary.F3
WHERE (((tblAgentSummary.callDate) Between [forms]![frmAgentSummaryExport]![txtBusinessDate] And [forms]![frmAgentSummaryExport]![txtEndDate]) AND ((tblAgents.Position)=[PosParam1] Or (tblAgents.Position)=[PosParam2]) AND ((tblAgents.Department)=[forms]![frmAgentSummaryExport]![cboDepartment]))
GROUP BY tblAgents.[Agent Name], tblAgents.Position
ORDER BY tblAgents.Position
PIVOT tblAgentSummary.F1 In ("Salo","Other","GiftCard","Lend","Mort","Web");


Replace the SQL for qryAgentSummary with this
SELECT qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Position, qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web
FROM qryAgentSummary_Crosstab;


You need to add the Position column to each worksheet in Temp.xlsx.  See attached.
OM Gang
Temp13.xlsx
0
 

Author Closing Comment

by:KLMServices
Comment Utility
You folks know your stuff. Wow- I have been messing with this for weeks and you make this look like a piece of cake. You just don't know how much hours productivity you have given us. Thank you and thank you.
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Good luck with the rest of you project.
OM Gang
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now