Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: Too Few Parameters...

    I keep getting an error ( Too few parameters: Expected 1) when I try to run this:
    Dim objXL As Object
    Dim objActiveWkb As Object
    Dim db As Database
    Dim rs As Recordset
    Dim Row As Integer

    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True
    objXL.Application.workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook

    Set db = CurrentDb
    strSQL = "SELECT * FROM [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel] WHERE [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel].[Division]= " & Division(i) & " ORDER BY [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel].[Date];"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


    I suspect the error is in the "& Division(i) &" part but i can't seem to fix it. Does anyone see something wrong here?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Where is "i" declared? I see no declaration or value for i anywhere...? Also, what line does it bomb on? Openrecordset()?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    I'm sorry- i didn't post it...

    Here is the beginning ( along with what i had before)-

    Dim Division(42) As Integer
    Dim i As Integer
    Dim strSQL As String

    Division(1) = 248
    Division(2) = 1054
    Division(3) = 1103
    Division(4) = 1106
    Division(5) = 1933
    Division(6) = 2050
    Division(7) = 2099
    Division(8) = 2116
    Division(9) = 2121
    Division(10) = 2127
    Division(11) = 2139
    Division(12) = 2148
    Division(13) = 2151
    Division(14) = 2192
    Division(15) = 2489
    Division(16) = 3023
    Division(17) = 3051
    Division(18) = 3103
    Division(19) = 3125
    Division(20) = 3126
    Division(21) = 3132
    Division(22) = 3135
    Division(23) = 3139
    Division(24) = 3147
    Division(25) = 3150
    Division(26) = 3160
    Division(27) = 3186
    Division(28) = 4028
    Division(29) = 4117
    Division(30) = 4118
    Division(31) = 4146
    Division(32) = 4147
    Division(33) = 4150
    Division(34) = 4194
    Division(35) = 3120
    Division(36) = 2140
    Division(37) = 3190
    Division(38) = 1104
    Division(39) = 3128
    Division(40) = 5301
    Division(41) = 5304
    Division(42) = 5315
    Dim objXL As Object
    Dim objActiveWkb As Object
    Dim db As Database
    Dim rs As Recordset
    Dim Row As Integer

    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True
    objXL.Application.workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook

    Set db = CurrentDb
    strSQL = "SELECT * FROM [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel] WHERE [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel].[Division]= " & Division(i) & " ORDER BY [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel].[Date];"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)



    I know this is 'choppy' but i was just trying to get it to work- then i'll clean it up a bit. What i am trying to do is this; I want to be able to run through an array of numbers that will query a table and then export each number's records to a spreadsheet.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    so i is declared
    ...but it has no value!

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Unless Option Explicit is defined I has a default value of 0 and since the array element of 0 was not assigned, it to has a value of 0 ... So, that being the case you've got an out-of-bounds error on the excel side that's coming back thru the query.
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    OKAY-HERE IS EVERYTHING (with some changes) I get this error- "Item not found in this collection"




    Private Sub cmdAging_Click()
    On Error GoTo Err_cmdAging_Click

    Dim Division(42) As Integer
    Dim i As Integer
    Dim strSQL As String

    Division(1) = 248
    Division(2) = 1054
    Division(3) = 1103
    Division(4) = 1106
    Division(5) = 1933
    Division(6) = 2050
    Division(7) = 2099
    Division(8) = 2116
    Division(9) = 2121
    Division(10) = 2127
    Division(11) = 2139
    Division(12) = 2148
    Division(13) = 2151
    Division(14) = 2192
    Division(15) = 2489
    Division(16) = 3023
    Division(17) = 3051
    Division(18) = 3103
    Division(19) = 3125
    Division(20) = 3126
    Division(21) = 3132
    Division(22) = 3135
    Division(23) = 3139
    Division(24) = 3147
    Division(25) = 3150
    Division(26) = 3160
    Division(27) = 3186
    Division(28) = 4028
    Division(29) = 4117
    Division(30) = 4118
    Division(31) = 4146
    Division(32) = 4147
    Division(33) = 4150
    Division(34) = 4194
    Division(35) = 3120
    Division(36) = 2140
    Division(37) = 3190
    Division(38) = 1104
    Division(39) = 3128
    Division(40) = 5301
    Division(41) = 5304
    Division(42) = 5315



    For i = 1 To 42


    Dim objXL As Object
    Dim objActiveWkb As Object
    Dim db As Database
    Dim rs As Recordset
    Dim Row As Integer

    Row = 3

    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = False
    objXL.Application.workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook

    Set db = CurrentDb
    strSQL = "SELECT * FROM tbl800/agedAR/PeopleSoft/AllVendorsFromExcel WHERE [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel].[Division]= " & Division(i) & " ORDER BY [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel].[Date];"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    With objActiveWkb
    While rs.EOF <> True
    .Worksheets(1).Cells(Row, 3) = rs![Dvision]
    .Worksheets(1).Cells(Row, 4) = rs![TRADE-CLASS]
    .Worksheets(1).Cells(Row, 5) = rs![SALES-ROUTE]

    rs.MoveNext
    Row = Row + 1
    Wend
    End With




    objActiveWkb.SaveAs Filename:="F:\TEST.xls"
    objActiveWkb.Close savechanges:=True

    Set objActiveWkb = Nothing
    Set objXL = Nothing



    Next i

    Exit_cmdAging_Click:
    Exit Sub

    Err_cmdAging_Click:
    MsgBox Err.Description
    Resume Exit_cmdAging_Click

    End Sub
    Private Sub cmdTransfer_Click()
    On Error GoTo Err_cmdTransfer_Click

  7. #7
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    I spelled DIVISION wrong but when i corrected it I get the TEW FEW PARAMETERS: EXPECTED 1 error again.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it would be nice to know which line dies. can you comment out
    on error goto
    so access stops?

    also: are you certain that this is a good idea:
    For i = 1 To 42
    Dim objXL As Object
    Dim objActiveWkb As Object
    Dim db As Database
    Dim rs As Recordset
    Dim Row As Integer

    can these declarations live up at the top of the routine where they belong?

    also:
    Set db = CurrentDb
    could also move out of the loop

    also:
    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = False
    objXL.Application.workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook

    inside the loop seems to be creating 42 workbooks and saving them all as
    objActiveWkb.SaveAs Filename:="F:\TEST.xls"
    ...so the first 41 get overwritten?

    apart from that - sorry i can't see where it is falling over. knowing which line would help.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Too few parameters usually points to the SQL. Either you typed a field name wrong or the field doesn't exist in the table. I would suggest putting in a break after the SQL then using the Immediate window to show the SQL and then pasting it in the Query editor. When you do that, Access will prompt you for the field that it does not recognize.

  10. #10
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    OKAY- Thanks for all of your help!

    Here's what i have (with some changes). The error happens at the "Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)" line. The error says TOO FEW PARAMETERS. EXPECTED 1.


    Private Sub cmdAging_Click()
    'On Error GoTo Err_cmdAging_Click

    Dim Division(42) As Integer
    Dim i As Integer
    Dim objXL As Object
    Dim objActiveWkb As Object
    Dim db As Database
    Dim rs As Recordset
    Dim Row As Integer
    Dim strSQL As String

    Set db = CurrentDb

    Division(1) = 248
    Division(2) = 1054
    Division(3) = 1103
    Division(4) = 1106
    Division(5) = 1933
    Division(6) = 2050
    Division(7) = 2099
    Division(8) = 2116
    Division(9) = 2121
    Division(10) = 2127
    Division(11) = 2139
    Division(12) = 2148
    Division(13) = 2151
    Division(14) = 2192
    Division(15) = 2489
    Division(16) = 3023
    Division(17) = 3051
    Division(18) = 3103
    Division(19) = 3125
    Division(20) = 3126
    Division(21) = 3132
    Division(22) = 3135
    Division(23) = 3139
    Division(24) = 3147
    Division(25) = 3150
    Division(26) = 3160
    Division(27) = 3186
    Division(28) = 4028
    Division(29) = 4117
    Division(30) = 4118
    Division(31) = 4146
    Division(32) = 4147
    Division(33) = 4150
    Division(34) = 4194
    Division(35) = 3120
    Division(36) = 2140
    Division(37) = 3190
    Division(38) = 1104
    Division(39) = 3128
    Division(40) = 5301
    Division(41) = 5304
    Division(42) = 5315



    For i = 1 To 42

    'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, Division(i) & "Aging", "G:\GROUP\FINANCE\SIS\DIVISION\Agings\" & Division(i) & "Aging.xls"

    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = False
    objXL.Application.workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook

    strSQL = "SELECT * FROM [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel] WHERE [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel].[Division]= " & Division(i) & " ORDER BY [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel].[Date];"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    Row = 3
    With objActiveWkb
    While rs.EOF <> True
    .Worksheets(1).Cells(Row, 3) = rs![Division]
    .Worksheets(1).Cells(Row, 4) = rs![TRADE-CLASS]
    .Worksheets(1).Cells(Row, 5) = rs![SALES-ROUTE]

    rs.MoveNext
    Row = Row + 1
    Wend
    End With

    objActiveWkb.SaveAs Filename:="F:\Agings\" & Division(i) & "Aging.xls"
    objActiveWkb.Close savechanges:=True

    Set objActiveWkb = Nothing
    Set objXL = Nothing

    Next i

    Exit_cmdAging_Click:
    Exit Sub

    Err_cmdAging_Click:
    MsgBox Err.Description
    Resume Exit_cmdAging_Click

    End Sub

  11. #11
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    ignore the "
    'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, Division(i) & "Aging", "G:\GROUP\FINANCE\SIS\DIVISION\Agings\" & Division(i) & "Aging.xls"

    statement- it just a commented-out line.

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And does the column Division exist in the table tbl800/agedAR/PeopleSoft/AllVendorsFromExcel that is in your access db?
    Back to Access ... ADO is not the way to go for speed ...

  13. #13
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Yes the column is there and it is set as a number.

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have you tried running:

    SELECT * FROM [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel] WHERE [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel].[Division]= 48 ORDER BY [tbl800/agedAR/PeopleSoft/AllVendorsFromExcel].[Date];

    In a regular old query to ensure that the sql is correct?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Thanks teddy- that was it. I had an error in the query.



    Thanks everyone for helping me 'see the light'!!!!!

    thanks again izyrider and m owen!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •