locked
Run-Time Error '3061' Too Few Parameters. Expected 1. RRS feed

  • Question

  • I am getting an error Run-Time Error '3061' Too Few Parameters. Expected 1 when I run some VBA.  When I first set it up, it worked fine and now I can't figure out what happened.  The only thing that has changed is the month.

    I have tried to check everywhere and have followed every suggestion I can to find  a fix for this. Hopefully you guys can help me figure out what i am missing.

    I am running some code that is grabbing meeting data.  

    The Code is breaking on Set rst = CurrentDb.OpenRecordset(strSQL).  

    Most of what I read seems to think it has to do with the SQL, so here is the SQL that is created.
    SELECT qry_Appointments.* FROM qry_Appointments WHERE Month([ApptDate])= 2 AND Year([ApptDate]) = 2014;


    When I take this and put it into an empty query and run the SQL it works perfect.  

    qry_Appointments runs fine as well.  It takes a table and pulls in various data points into, but nothing really complex. There is nothing running off a form.

    Public Sub DisplayMeetings()
    '   Add any meetings that occur this month to the proper day box on the form
    Dim strSQL As String
    Dim intTemp As Integer
    Dim intDays(37) As Integer
    Dim strDays(31) As String
    Dim strApptSubject As String
    Dim strApptStartTime As String
    Dim strApptDate As String
    Dim r As Integer
    Dim rst
        
    '   To start, clear all meetings from every day
        For r = 1 To 37
            Me("Day" & Trim$(r)) = ""
        Next r
    
    '   Grab this month's meetings from the qry_Appointments table
    
        strSQL = "SELECT qry_Appointments.* " & _
                 "FROM qry_Appointments " & _
                 "WHERE Month([ApptDate])= " & intPubMonth & " AND Year([ApptDate]) = " & intPubMyYear & ";"
        Set rst = CurrentDb.OpenRecordset(strSQL)
    
    '   Skip adding appointments if there are none for this month
        If rst.RecordCount > 0 Then
        
    '       Populate array intDays(r) with the day of the month (or zero if the box is unused)
            For r = 1 To 37
                If Me("Box" & Trim(r)) = "" Then
                    intDays(r) = 0
                Else
                    intDays(r) = Me("Box" & Trim(r))
                End If
                Me("Day" & Trim(r)) = ""
            Next r
        
            rst.MoveFirst
            
            Do While Not rst.EOF
    '           Grab the time, date and subject of each appointment
                strApptStartTime = Format(rst!ApptStartTime, "hh:mm AMPM")
                strApptDate = rst!ApptDate
    '           Truncate the appointment description
                strApptSubject = Left(rst!Appt, 20)
            
    '           Get the day of the month for this appointment
                intTemp = Day(rst!ApptDate)
                
    '           Add the appointment details to the proper day
                strDays(intTemp) = strDays(intTemp) & vbCrLf & strApptStartTime & " - " & strApptSubject
        
                rst.MoveNext
            Loop
        
    '       Loop through every calendar box.  If there are any appointments stored
    '       in array strDays(r), add them to the calendar box
            For r = 1 To 37
                If intDays(r) <> 0 Then
                    intTemp = intDays(r)
                    Me("Day" & Trim(r)) = strDays(intTemp)
                End If
            Next r
        End If
        
        rst.Close
    
    End Sub

    Tuesday, February 11, 2014 12:17 AM

Answers

  • Hi,

    did you really check that the SQL string has been created correctly, e.g. with

    Debug.Print strSQL

    in the line above Set rst...?

    If yes, then the culprit could be qry_Appointments. Check if there is any parameter in it. If e.g. it has a form reference then DAO can't handle it even if it's "only" a base query for the recordset's SQL. A workaround is the use of Eval() in the query:

    Forms!myForm!myControl --> Eval("Forms!myForm!myControl")

    This way you send the resulting value to the DAO recordset instead of the reference.

    If you don't succeed then post the SQL string of qry_Appointments.


    cu Karl *** Catalog for the professional Access application http://www.donkarl.com/en/catalog

    Tuesday, February 11, 2014 10:57 AM

All replies

  • Your code does show neither the declaration for intPubMonth and intPubMyYear nor the value assignment for these 2 variables before they are used in the SQL String construction.

    That would be where I look at for the potential problem(s)...

    Another point is that you should use Option Explicit in every Module so that if you gorget to declare the variables, the compiler will give compilation error so that you can fix them.  This prevents lots of problems, espcially with mis-spelt variable names.

     

     


    Van Dinh

    Tuesday, February 11, 2014 3:23 AM
  • Hi,

    did you really check that the SQL string has been created correctly, e.g. with

    Debug.Print strSQL

    in the line above Set rst...?

    If yes, then the culprit could be qry_Appointments. Check if there is any parameter in it. If e.g. it has a form reference then DAO can't handle it even if it's "only" a base query for the recordset's SQL. A workaround is the use of Eval() in the query:

    Forms!myForm!myControl --> Eval("Forms!myForm!myControl")

    This way you send the resulting value to the DAO recordset instead of the reference.

    If you don't succeed then post the SQL string of qry_Appointments.


    cu Karl *** Catalog for the professional Access application http://www.donkarl.com/en/catalog

    Tuesday, February 11, 2014 10:57 AM
  • Here is how my module opens.

    Option Compare Database
    Option Explicit
    
    Public intPubMonth, intPubMyYear  'The big calendar's current month & year


    When the form is opened:

    '   When the form is opened, check for passed
    '   arguments.  If there are some, set the
    '   month/date to be the passed date and if not,
    '   use the current month/date.
    
    Private Sub Form_Open(Cancel As Integer)
        
        If IsNull(Me.OpenArgs) Then
    '       Use today's date
            intPubMyYear = Year(Date)
            intPubMonth = Month(Date)
          Else
    '       Use passed arguments...
            intPubMyYear = Year(Me.OpenArgs)
            intPubMonth = Month(Me.OpenArgs)
        End If
    
        Call SetDates
        Call DisplayMeetings
    
    End Sub
    

    When checking the variables:

    intPubMyYear = 2

    intPubMonth = 2014

    When I debug I can see it pass through to the SQL.  Should I declare them in the individual sub routine?

    Tuesday, February 11, 2014 3:00 PM
  • I traced back the query to see if I had anything and sure enough I am pulling in a variable in one of the underlining queries a few layers deep that is only limiting the data that is returned.

    I figured out a way to at least remove that variable and it worked.    So that was the culprit on this issue.

    I really appreciate your help!


    Tuesday, February 11, 2014 3:47 PM