none
Error 3061 Too few parameters. Expected 4.

    Question

  • Hi, I have a DB set and in the form i have Queries that run from there it used to work fine and now when i tried to import more data it gives me this error. here is my code can someone help thanks. the part in yellow is where is says that the error exist. thanks in advance.

     

    Public Function ImportVData(sMonthYear As String, sPayDate As String, sFile As String, vArr) As Boolean
              Const cTMPTbl = "__^__TMP_IMPORT"
              Const cTMPTbl0 = "__^__TMP_IMPORT0"
              Const cQRY = "__Qry_Append_New_Data"
             
              Dim sTmpFile As String
              Dim qry As QueryDef
              Dim strA
              Dim bFirst As Boolean

    10        On Error GoTo ImportVData_Error

    20        If TableExists(cTMPTbl) Then
    30            DoCmd.DeleteObject acTable, cTMPTbl
    40        End If

    50        bFirst = True
    60        For Each strA In vArr
    70            If strA > "" Then
    80                If TableExists(cTMPTbl0) Then
    90                    DoCmd.DeleteObject acTable, cTMPTbl0
    100               End If
                     
    110               DoCmd.TransferSpreadsheet acImport, , cTMPTbl0, sFile, True, strA & "$"
    120               If CheckForImportErrors(strA & "$") Then
    130                   Err.Raise -999, , "Import errors encountered. User chose not to contiune."
    140               End If
    150               If bFirst Then
    160                   DoCmd.CopyObject , cTMPTbl, acTable, cTMPTbl0
    170                   bFirst = False
    180               Else
    190                   If Not RunSQL("INSERT INTO [__^__TMP_IMPORT] SELECT [__^__TMP_IMPORT0].* FROM [__^__TMP_IMPORT0];") Then
    200                       Err.Raise -999, , "Unable to append imported data for the '" & strA & "' tab."
    210                   Else
    220                       If TableExists(cTMPTbl0) Then
    230                           DoCmd.DeleteObject acTable, cTMPTbl0
    240                       End If
    250                   End If
    260               End If
    270           End If
    280       Next
             
              'Add "Ident" column to tmp table
    290       If Not RunSQL("ALTER TABLE [" & cTMPTbl & "] ADD COLUMN IDENT COUNTER(1,1);") Then
    300           Err.Raise -999, , "Unable to add column 'Ident' to the '" & cTMPTbl & "'."
    310       End If
             
              'remove any old data
    320       If Not RunSQL("DELETE * FROM [_ARCHIVE_ATT_COM] WHERE MONTH_YEAR='" & _
                  sMonthYear & "';") Then
    330           Err.Raise -999, , "Unable to remove old rows for " & sMonthYear & "'."
    340       End If
             
    350       Set qry = Application.CurrentDb.QueryDefs(cQRY)
    360       qry.Parameters("PERIOD") = sMonthYear
    370       qry.Parameters("PAYMENT_DATE") = CDate(sPayDate)
    380       qry.Execute
             
    390       qry.Close
    400       Set qry = Nothing
                 
    410       If TableExists(cTMPTbl0) Then
    420           DoCmd.DeleteObject acTable, cTMPTbl0
    430       End If
    440       If TableExists(cTMPTbl) Then
    450           DoCmd.DeleteObject acTable, cTMPTbl
    460       End If
             
    470       ImportVData = True
    480       On Error GoTo 0
    490       Exit Function

    Wednesday, July 30, 2008 3:55 PM

Answers

  • Hi,

     

    Got your email, best to respond here.

     

    You need to look at the query (__Qry_Append_New_Data) in the database and have a look to see how many parameters it takes, should be indicated by a ? character / or actually they look to be named.

     

    I think the query has defined 4 parameters and in the code

     

    350       Set qry = Application.CurrentDb.QueryDefs(cQRY)
    360       qry.Parameters("PERIOD") = sMonthYear
    370       qry.Parameters("PAYMENT_DATE") = CDate(sPayDate)
    380       qry.Execute

     

    There are only two parameters here. You need to find the names and data types of the 2 additional parameters from the database query and add them before the Execute command.

     

    350       Set qry = Application.CurrentDb.QueryDefs(cQRY)
    360       qry.Parameters("PERIOD") = sMonthYear
    370       qry.Parameters("PAYMENT_DATE") = CDate(sPayDate)

    371       qry.Parameters("PARAMETER NAME") = value  'find parameter name in query

    372       qry.Parameters("PARAMETER NAME") = value  'find parameter name in query

    380       qry.Execute

     

     

    Wednesday, July 30, 2008 8:34 PM

All replies

  • Hi,

     

    Got your email, best to respond here.

     

    You need to look at the query (__Qry_Append_New_Data) in the database and have a look to see how many parameters it takes, should be indicated by a ? character / or actually they look to be named.

     

    I think the query has defined 4 parameters and in the code

     

    350       Set qry = Application.CurrentDb.QueryDefs(cQRY)
    360       qry.Parameters("PERIOD") = sMonthYear
    370       qry.Parameters("PAYMENT_DATE") = CDate(sPayDate)
    380       qry.Execute

     

    There are only two parameters here. You need to find the names and data types of the 2 additional parameters from the database query and add them before the Execute command.

     

    350       Set qry = Application.CurrentDb.QueryDefs(cQRY)
    360       qry.Parameters("PERIOD") = sMonthYear
    370       qry.Parameters("PAYMENT_DATE") = CDate(sPayDate)

    371       qry.Parameters("PARAMETER NAME") = value  'find parameter name in query

    372       qry.Parameters("PARAMETER NAME") = value  'find parameter name in query

    380       qry.Execute

     

     

    Wednesday, July 30, 2008 8:34 PM
  • Hi

     

    Good to see you back on the forum Derek,  I suspect you are right!

     

     

    Regards

     

    ADG

    Wednesday, July 30, 2008 11:00 PM
  • Thanks, Derek

     

    I looked at the Query at it only has the two PAYMENT DATE And PERIOD i dont have any other parameter in there. Again thanks i appreciate the help.

     

    Adam

     

    Thursday, July 31, 2008 2:29 PM
  •  ADG wrote:

    Hi

     

    Good to see you back on the forum Derek,  I suspect you are right!

     

    Regards

     

    ADG

     

    How you doing ADG? hope this finds you well. Yeah I've went C#/VB.NET now and not getting much in the way of VBA/Office development, kind of forgotten the majority of it. Good to see your still leading the way on supporting VBA. Smile

     

     

     

     

     Michie23 wrote:

    Thanks, Derek

     

    I looked at the Query at it only has the two PAYMENT DATE And PERIOD i dont have any other parameter in there. Again thanks i appreciate the help.

     

    Adam

     

     

    Hi Adam, what happens when you run the query directly? It should prompt you for the parameters, I believe you that you checked and know your way around ok, its just the error message suggests there is more to that query. Something isn't mapping up. Perhaps running the query might prompt you four times.

     

    If you can post the SQL of the query.

    Thursday, July 31, 2008 3:31 PM
  •  

    PARAMETERS PERIOD Text ( 255 ), PAYMENT_DATE DateTime;
    INSERT INTO _ARCHIVE_ATT_COM ( MONTH_YEAR, PAYMENT_DATE, [DEALER CODE], DEALER, MARKET, SUBMARKET, STATE, REGION, [MONTH], STORE, [CUSTOMER NAME], [MOBILE #], [ACT DATE], [DISC DATE], [DISC REASON], [SUSP DATE], [DAYS VESTED], TRANS_TYPE, COMM_TYPE, [ESN/SIM], IMEI, [BILL CODE], AMOUNT, NOTES, [BILL CODE DESCRIPTION], TERM, [BC Rate], [CARRIER CODE], Ident )
    SELECT [PERIOD] AS X, [PAYMENT_DATE] AS Y, [__^__TMP_IMPORT].[DEALER CODE] AS Expr1, [__^__TMP_IMPORT].DEALER AS Expr2, [__^__TMP_IMPORT].MARKET AS Expr3, [__^__TMP_IMPORT].SUBMARKET AS Expr4, [__^__TMP_IMPORT].STATE AS Expr5, [__^__TMP_IMPORT].REGION AS Expr6, [__^__TMP_IMPORT].MONTH AS Expr7, [__^__TMP_IMPORT].STORE AS Expr8, [__^__TMP_IMPORT].[CUSTOMER NAME] AS Expr9, [__^__TMP_IMPORT].[MOBILE #] AS Expr10, [__^__TMP_IMPORT].[ACT DATE] AS Expr11, [__^__TMP_IMPORT].[DISC DATE] AS Expr12, [__^__TMP_IMPORT].[DISC REASON] AS Expr13, [__^__TMP_IMPORT].[SUSP DATE] AS Expr14, [__^__TMP_IMPORT].[DAYS VESTED] AS Expr15, [__^__TMP_IMPORT].[TRANS TYPE] AS Expr16, [__^__TMP_IMPORT].[COMM TYPE] AS Expr17, [__^__TMP_IMPORT].[ESN/SIM] AS Expr18, [__^__TMP_IMPORT].IMEI AS Expr19, [__^__TMP_IMPORT].[BILL CODE] AS Expr20, [__^__TMP_IMPORT].AMOUNT AS Expr21, [__^__TMP_IMPORT].NOTES AS Expr22, [__^__TMP_IMPORT].[BILL CODE DESCRIPTION] AS Expr23, [__^__TMP_IMPORT].TERM AS Expr24, [__^__TMP_IMPORT].[BC RATE] AS Expr25, [__^__TMP_IMPORT].[CARRIER CODE] AS Expr26, [__^__TMP_IMPORT].IDENT AS Expr27
    FROM [__^__TMP_IMPORT];
     

     

     

    I tried to run it wont run

    Thursday, July 31, 2008 4:20 PM
  • The above looks OK, two parameters as you say, and the field names and number match. Could the field names in [__^__TMP_IMPORT] have changed since the query was written?

    Thursday, July 31, 2008 5:19 PM
  • Nothing Changed in the query, Nothing Changed from the information that is being Imported either, it was working it just stopped working and brings up this error now. I compared the data being Imported to ones from previous and both Match this is why is frustrating I dont know what else to do with it. Any help is appreciated

     

    Thanks,

    Adam

     

    Friday, August 01, 2008 1:41 PM
  • I am having the same issue in a completely different database. I hope someone can help me with this.

     

    I have the same error (looking for two parameters) and I seem to have linked it to code actively running. I have a simple select query pulling data from one table based on info on the form. Specifically, looking for dates based on a month and year provided by the user. The query pulls the fields from text boxes on the form, ad then outputs corresponding dates. When I run the query on its own, it asks me to supply the criteria. If I do, I get a good, working result. If I load up the form, type values in the boxes, and run the query, I get the same result. If I use code (such as AfterUpdate) to open the query, it works.

     

    So what's the deal, you ask? Well, I'm trying to use that query as a recordset which code will loop through to populate the form with more data. At the point I use 'Set Recs = CurrentDb.OpenRecordset("DateQuery")' I get this mysterious error. Upon further investigation, I found that while code was running (in break mode, as I didn't feel like creating an infinite loop to test) the query WILL run, but it provides blank data / 0 records. As soon as I end code execution, the query works fine again.

     

    So can someone tell me how, without changing this to a make-table query (because that does work, but poses other issues), I am supposed to loop through the records of my query using code?

     

     

    Thanks a lot!

     

    :-)

    Thursday, September 04, 2008 11:05 PM
  • Hi

     

    can you post the query in SQL format, and your code. It might be easier to spot the problem. I am guessing that it is a problem with scope of the parameters which are linked to the form. If the query cannot see the form and you have linked to fields / controls on the form you will get a problem.

     

    Regards

     

    ADG

     

    Friday, September 05, 2008 11:26 AM
  •  

    Sure. Sorry for not doing that up front. The code takes an array of labels on the form and tries to set the value of the query items to the caption of those labels. There is some more code that is missing (like what to do if there is no more data when the loop hasn't finished) but that's not what's troublign me at this point.

     

    I have pasted two segments of code to show different attempts I have made.

     

    QUERY:

     

    SELECT Month(Date) AS [Month], Day(Date) AS [Day], Weekday(Date) AS Weekday, Year(Date) AS [Year]
    FROM CalendarMaster
    WHERE (((Month([Date]))=Forms!CalendarView!MonthBox) And ((Year([Date]))=Forms!CalendarView!YearBox));

    CODE #1:

     

    Public Function MakeCalendar(CalendarForm As Form)
    Dim BoxArray(42) As Label
    Dim BoxLoop As Integer
    Dim StartBox As Integer
    Dim DateRecs As DAO.Recordset

        Set DateRecs = Application.CurrentDb.OpenRecordset("MakeCalendar")
           
        For BoxLoop = 1 To 42
            Set BoxArray(BoxLoop) = CalendarForm.Controls.Item(BoxLoop)
        Next
      
        DateRecs.MoveFirst
       
        StartBox = DateRecs!Weekday
      
        For BoxLoop = StartBox To 42
            BoxArray(StartBox).Caption = DateRecs!Day
        Next
       
    End Function

     

    CODE #2:

     

    Global DateRecs As DAO.Recordset

     

    Private Sub Form_Load()

        Dim BoxArray(42) As Label
        Dim BoxLoop As Integer
        Dim StartBox As Integer
            
        DoCmd.Restore


        SetDateFlags

       
         Set DateRecs = Application.CurrentDb.OpenRecordset("MakeCalendar")
            
         For BoxLoop = 1 To 42
             Set BoxArray(BoxLoop) = Me.Controls.Item(BoxLoop)
         Next
       
         DateRecs.MoveFirst
        
         StartBox = DateRecs!Weekday
       
         For BoxLoop = StartBox To 42
             BoxArray(StartBox).Caption = DateRecs!Day
         Next
           
    End Sub

     

    Friday, September 05, 2008 2:34 PM
  • Aha! I've found a solution on another forum. (It's only a solution, and not an actual explanation of why this error occurs)

     

    http://www.tek-tips.com/viewthread.cfm?qid=700580

     

    The trick is to use the SQL statement without specifying the variables, instead concatenating the string with the variables. If that doesn't make much sense, I'm giving an example below:

     

    Old way (wrong):

     

    sSQL = "SELECT Month(CalendarMaster.Date) AS [Month], Day(CalendarMaster.Date) AS [Day], " _

    & "Weekday(CalendarMaster.Date) AS Weekday, Year(CalendarMaster.Date) AS [Year] " _

    & "FROM CalendarMaster" _

    & "WHERE (((Month([CalendarMaster].[Date]))=[Forms]![CalendarView]![MonthBox]) AND " _

    & "((Year([CalendarMaster].[Date]))=[Forms]![CalendarView]![YearBox]));"

     

    New way (right):

     

    sSQL = "SELECT Month(CalendarMaster.Date) AS [Month], Day(CalendarMaster.Date) AS [Day], " _

    & "Weekday(CalendarMaster.Date) AS Weekday, Year(CalendarMaster.Date) AS [Year]" _
    & " FROM CalendarMaster" _
    & " WHERE (((Month(" & [Forms]![CalendarMaster].[Date] & "))=" & [Forms]![CalendarMaster]![MonthBox] _

    & ") AND ((Year(" & [Forms]![CalendarMaster].[Date] & "))=" & [Forms]![CalendarMaster]![YearBox] & "));"

     

    In other words, the statement "WHERE [Date] = [Variable];" is wrong and  "WHERE [Date] =" & [Variable] & ";"  is right. 

     

    Thanks for the help!

    Friday, September 05, 2008 10:34 PM