ACCESS FEED: Re: [MS_AccessPros] Error 3061 Too Few Parameters

I should have also said that your analysis was spot on and very informative.

Thank you.

Patty

On Mon, Sep 9, 2013 at 11:29 PM, Graham Mandeno <graham@mandeno.com> wrote:

Hi Patty

We seem to be going round in circles here, and by my count there have been
more than 40 messages in this thread :-)

Doing a heck of a lot of reading between the lines, I deduce the following:

1. You wish to open a Recordset and use it to populate a ListView
2. You have a general-purpose function "FillList" which is supposed to
accomplish this
3. The function takes two arguments: Domain (the SQL statement or name of a
query to open the Recordset) and LV (the LestView control to be populated)
4. In this instance, you are passing the query named "qryLeftListView" as
the Domain argument.
5. The line <<Set rs = db.OpenRecordset(Domain)>> is raising Error 3061 :
Too Few Parameters

How did I do? :-)

OK, so this means that the query named "qryLeftListView" contains parameters
which cannot be resolved by OpenRecordset. This is almost certainly caused
by a reference to a form control in the criteria (WHERE clause) of the
query. For example, you probably have something like this:
SELECT .... WHERE CDate(StartDate) >
CDate(Forms!Select_Diagnosis![VisitList])
This is a reference to the control "VisitList" on the form
"Select_Diagnosis".

Now, you can probably open this query just fine from the Navigation Pane
(provided the form is open), so why should you not open it as a Recordset in
your code?

The reason is that when you open it from the Nav Pane you are going via the
Access user interface, and this understands the Forms collection and can
therefore resolve the reference. However, db.OpenRecordset bypasses the
access UI and goes directly to DAO (the database engine) which knows about
tables and queries and fields, but nothing about forms. It therefore views
Forms!Select_Diagnosis![VisitList] as an unresolved parameter.

So, how to circumvent the error? I suggest you change the code that opens
the Recordset to check if Domain is a saved query and, if it is, attempt to
resolve all its parameters before opening it.

Add the following two functions to your module code (they can go in the same
module as the FillList function, or somewhere else if you prefer):

===================================================
Public Function ObjectExists(col As Object, Member As String) As Boolean
Dim o As Object
On Error Resume Next
Set o = col(Member)
If Err Then
Err.Clear
ObjectExists = False
Else
ObjectExists = True
End If
End Function

Public Function ResolveQueryParams( _
QryName As String, _
Optional db As DAO.Database _
) As DAO.QueryDef
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
On Error GoTo ProcErr
If db Is Nothing Then Set db = CurrentDb
Set qdf = db.QueryDefs(QryName)
For Each prm In qdf.Parameters
On Error Resume Next
prm.Value = Eval(prm.Name)
If Err.Number <> 0 Then
Err.Clear
On Error GoTo ProcErr
prm.Value = InputBox("Enter parameter value for: " _
& vbCrLf & prm.Name, "Query '" & QryName & "'")
End If
Next prm
Set ResolveQueryParams = qdf

ProcEnd:
On Error Resume Next
Set qdf = Nothing
Exit Function

ProcErr:
Dim msg As String
Select Case Err.Number
Case 3265 'item not found in this collection
msg = "Query '" & QryName & "' not found"
Case Else
msg = "Error #" & Err.Number & vbCrLf & Err.Description
End Select
MsgBox msg, vbExclamation, "ResolveQueryParams"
Resume ProcEnd

End Function
===================================================

Then, replace this line in your FillList function:
Set rs = db.OpenRecordset(Domain)
with this:
===================================================
If ObjectExists( db.QueryDefs, Domain ) Then
' Domain is a saved query
Set rs = ResolveQueryParams( Domain, db ).OpenRecordset
Else
Set rs = db.OpenRecordset(Domain)
End If
===================================================

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes

Sent: Tuesday, 10 September 2013 03:27

To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters

I'm sorry... did not mean to send.
That is however the code, if not properly aligned.
The debug looks like this:
SELECT *FROM [qryLeftListView]WHERE [TestDate]>12/21/2012

Thank you,
Patty

On Mon, Sep 9, 2013 at 11:24 AM, Patricia Mapes <patty.mapes@gmail.com>
wrote:
To be more descriptive:
If I hover over the "Domain" is shows the "qryLeftListView"
If I hover over "DateOfVist" is shows the correct date.
If I hover over the "TestDate" (which when running the query gives the
converted dates) it appears empty. This I assume is why my ">" is not
working.
This is the code:
Set db = CurrentDb
           Set rs = db.OpenRecordset(Domain)

           strSQL = "SELECT *FROM [qryLeftListView]WHERE [TestDate]>" &
DateOfVisit
           Debug.Print strSQL

On Mon, Sep 9, 2013 at 11:18 AM, Patricia Mapes <patty.mapes@gmail.com>
wrote:
Yes, it is working from the query. The code to fill the ListView was
copy/pasted.
I thought the Domain should either be the table, or a query that the data
was coming from but putting either in that space was unacceptable.
I'm quite rusty at the moment and so I'm scrambling trying to remember how
all of these pieces fit together.
I appreciate your help.

On Mon, Sep 9, 2013 at 10:43 AM, Robert Peterson
<bob@alternatefinishing.com> wrote:

Sorry I missed that it was an argument of the function.
So you have this string that is an SQL statement but it never gets applied
to the "Domain" to filter it.
Whatever is the "qryLeftListView" is what the rest of your function is
working from.

Bob Peterson

"Never look back unless you are planning to go that way."
― Henry David Thoreau

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes
Sent: Monday, September 09, 2013 10:07 AM

To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters

It is the "qryLeftListView"...

On Mon, Sep 9, 2013 at 8:23 AM, Robert Peterson <bob@alternatefinishing.com>
wrote:

You never answered Graham. What is "Domain" ? I don't see it declared
anywhere.

I think you are doing things in the wrong order. This -

           Set rs = db.OpenRecordset(Domain)
   'select all events
                strSQL = "SELECT * FROM [diagnosis] WHERE
(CDate(EndDate))>#" & DateOfVisit & "#"
should be -

     'select all events
                strSQL = "SELECT * FROM [diagnosis] WHERE
(CDate(EndDate))>#" & DateOfVisit & "#"
    Set rs = db.OpenRecordset(strSQL)

Bob Peterson

"Never look back unless you are planning to go that way."
― Henry David Thoreau

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes
Sent: Sunday, September 08, 2013 8:23 PM

To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters

I know, very confusing. I'm so sorry. Taking you at your word, this is the
entire module code. As you can see it is designed to populate a ListView.

Option Compare Database
Option Explicit
Function FillList(Domain As String, LV As Object) As Boolean
      '==================================================================
      ' Purpose:   to fill a ListView control with data from a table or a
query
      ' Arguments: a Domain which is the name of the table or query, and a
ListView control object
      ' Returns:   A Boolean value to indicate if the function was
successful
     '==================================================================
      Dim db As DAO.Database
      Dim rs As DAO.recordSet
      Dim intTotCount As Integer
      Dim intCount1 As Integer
      Dim intCount2 As Integer
      Dim colNew As ColumnHeader
      Dim NewLine As ListItem
      Dim strSQL As String
      Dim DateOfVisit As Date
      Dim TestStart As DateDim db As DAO.Database
      On Error GoTo Err_Man
            DateOfVisit =
CDate(Forms!Select_Diagnosis![VisitList])
   ' Clear the ListView control.
           LV.ListItems.Clear
         LV.ColumnHeaders.Clear
     ' Set Variables.
           Set db = CurrentDb
           Set rs = db.OpenRecordset(Domain)
   'select all events
                strSQL = "SELECT * FROM [diagnosis] WHERE
(CDate(EndDate))>#" & DateOfVisit & "#"
        Debug.Print strSQL
' Set Column Headers.
           For intCount1 = 0 To rs.Fields.Count - 1
           Set colNew = LV.ColumnHeaders.Add(, , CStr(rs(intCount1).Name),
4580)
           Next intCount1
LV.View = 3    ' Set View property to 'Report'.
           LV.GridLines = True 'Show gridlines
           LV.Enabled = True 'Allow records to be selected
          LV.FullRowSelect = True 'Allows entire row to be selected

           ' Set Total Records Counter.
           rs.MoveLast
           intTotCount = rs.RecordCount
           rs.MoveFirst
           ' Loop through recordset and add Items to the control.
           For intCount1 = 1 To intTotCount
              If IsNumeric(rs(0).Value) Then
                 Set NewLine = LV.ListItems.Add(, , Str(rs(0).Value))
              Else
                 Set NewLine = LV.ListItems.Add(, , CStr(rs(0).Value))
              End If
              For intCount2 = 1 To rs.Fields.Count - 1
                 NewLine.SubItems(intCount2) = rs(intCount2).Value
              Next intCount2
              rs.MoveNext
           Next intCount1
           Exit Function
Err_Man:
         ' Ignore Error 94 which indicates you passed a NULL value.
         If Err = 94 Then
            Resume Next
         Else
         ' Otherwise display the error message.
            MsgBox "Error: " & Err.Number & Chr(13) & _
               Chr(10) & Err.Description
         End If
End Function

On Sun, Sep 8, 2013 at 7:59 PM, Graham Mandeno <graham@mandeno.com> wrote:

Hi Patty

Please forgive me, but I would like to return to your original question.
You said:
<quote>
I have the following code, failing on the last line:

DateOfVisit = CDate(Forms!Select_Diagnosis![VisitList])

  Set db = CurrentDb
Set rs = db.OpenRecordset(Domain)
             'select all events

strSQL = "SELECT * FROM [diagnosis] WHERE (CDate(StartDate)) >" &
DateOfVisit
</quote>

The ONLY line that could possibly raise that error is this one:

Set rs = db.OpenRecordset(Domain)

And the problem would be in the value of the (presumably String) variable
Domain, and nothing to do with StartDate.

So, like Duane, I am also confused :-)

Could you please post the code for the entire procedure which is failing?
Or at least EVERYTHING (including declarations) up to the line which is
failing.

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes
Sent: Monday, 9 September 2013 11:40
To: MS_Access_Professionals@yahoogroups.com

Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters

It is a ListView Duane.

On Sun, Sep 8, 2013 at 7:23 PM, Duane Hookom <duanehookom@hotmail.com>
wrote:

I'm not sure how all of the code fits together. You are using the SQL
statement to create a recordset for something. What is that something that
apparently shows more records than expected?

Duane Hookom MVP
MS Access

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: patty.mapes@gmail.com
> Date: Sun, 8 Sep 2013 18:59:28 -0400
> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>
>
>
> I'm so sorry Duane. It isn't enough I've confused myself but now I'm
> doing it to you! You are right. I had just lost my way trying to find
> the solution.
> This statement works when I put it into a query, and it sorts the
> records as needed.
> strSQL = "SELECT * FROM diagnosis.diag, diagnosis.icd9 FROM diagnosis
> WHERE (((CDate([StartDate]))>
> [Forms]![Select_Diagnosis]![VisitList]))GROUP BY diagnosis.diag,
> diagnosis.icd9"
>
> In the module, however, it returns all records, although it is giving
> the date of the variable (VisitList).
>
> I do appreciate your help and suggestions very much.
> Patty
>
>
> On Sun, Sep 8, 2013 at 6:02 PM, Duane Hookom
> <duanehookom@hotmail.com<mailto:duanehookom@hotmail.com>> wrote:
>
>
> Patty,
>
> Now I'm really confused. I thought your code used the SQL statement
> " SELECT * FROM [diagnosis] WHERE (CDate([StartDate]))>#11/18/2011# "
>
> How/where do the more recent SQL statements come from? I don't see any
> code that mentions these last SQL statements.
>
>
> Duane Hookom MVP
> MS Access
>
> ________________________________
>> To:
>
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoo
groups.com>
>> From: patty.mapes@gmail.com<mailto:patty.mapes@gmail.com>
>> Date: Sun, 8 Sep 2013 17:43:42 -0400
>> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>>
>> When I put this Select statement into a query I get the correct data:
>> SELECT diagnosis.diag, diagnosis.icd9, diagnosis.diag_id FROM diagnosis
>> WHERE (((CDate([StartDate]))>[forms]![select_Diagnosis]![VisitList])
>> AND ((diagnosis.diag_id)>0)) GROUP BY diagnosis.diag, diagnosis.icd9,
>> diagnosis.diag_id;
>>
>> When I have it in my module, with what I think are the correct syntax
>> changes I get incorrect data, as in all of the records whether their
>> StartData is before or after the DateOfVisit. No error message.
>>
>> This is my "corrected" string:
>> strSQL = "SELECT * FROM diagnosis.diag, diagnosis.icd9,
>> diagnosis.diag_id FROM diagnosis WHERE (((CDate([StartDate]))>#" &
>> [Forms]![select_Diagnosis]![VisitList] & "#) AND
>> ((diagnosis.diag_id)>0)) GROUP BY diagnosis.diag, diagnosis.icd9,
>> diagnosis.diag_id"
>>
>> I know it looks messy... sorry..
>>
>> Patty
>>
>> On Sun, Sep 8, 2013 at 4:59 PM, Duane Hookom
>>
>
<duanehookom@hotmail.com<mailto:duanehookom@hotmail.com><mailto:duanehookom@
hotmail.com<mailto:duanehookom@hotmail.com>>>
> wrote:
>>
>>
>> Patty,
>>
>> Are you suggesting that when you view the datasheet of the query, you
>> are seeing records with a StartDate value < 11/18/2011?
>>
>>
>> Duane Hookom MVP
>> MS Access
>>
>> ________________________________
>>> From:
> patty.mapes@gmail.com<mailto:patty.mapes@gmail.com>

>>>
>>> I think all that it showed me is that my WHERE statement isn't work at
>>> all. This is the sql:
>>>
>>> strSQL = "SELECT * FROM [diagnosis] WHERE (CDate([StartDate]))>#" &
>>> DateOfVisit & "#"
>>>
>>> And this is the debug:
>>>
>>> SELECT * FROM [diagnosis] WHERE (CDate([StartDate]))>#11/18/2011#
>>>
>>> The WHERE statement should eliminate about 1/2 of the records but I am
>>> getting all of the records displayed in my ListView. It is picking of
>>> the correct DateOfVisit.
>>>
>>>
>>> On Sun, Sep 8, 2013 at 4:28 PM, Duane Hookom
> wrote:
>>>
>>>
>>> Patty,
>>>
>>> How did the debugging of the SQL statement look? Is it what you
expected?
>>>
>>> The debugging is the first step I would have taken to see what is going
>>> wrong.
>>>
>>>
>>> Duane Hookom MVP
>>> MS Access
>>>
>>> ________________________________
>>>> From:
>>
> patty.mapes@gmail.com<mailto:patty.mapes@gmail.com>

>>>> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>>>>
>>>> This is my actual code John.
>>>>
>>>> strSQL = "SELECT * FROM [diagnosis] WHERE (CDate([StartDate]))>&" &
>>>> DateOfVisit & "&"
>>>>
>>>> On Sun, Sep 8, 2013 at 3:47 PM, John Viescas
>>
>
<JohnV@msn.com<mailto:JohnV@msn.com><mailto:JohnV@msn.com<mailto:JohnV@msn.c
om>>>>>>
>>>>
>>>> Patty-
>>>>
>>>> Did you see my suggestion about putting brackets around [StartDate] ??
>>>>
>>>> John Viescas, Author
>>>>
>>>> From:
>>>>
>>>> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>>>>
>>>> I tried that Duane.
>>>> This is what I got:
>>>>
>>>> SELECT * FROM [diagnosis] WHERE (CDate(StartDate))>&7/5/2013&
>>>>

>>>> So it does look like the (CDate(StartDate)) is not producing a date,
>>>> and all records with all StartDates are being passed. Therefore, I
>>>> assume that my error in the (CDate(StartDate)).
>>>>
>>>> Do you have any ideas about that?
>>>>
>>>> Thanks,
>>>>
>>>> Patty
>>>>
>>>>
>>>>
>>>> On Sun, Sep 8, 2013 at 2:44 PM, Duane Hookom
>>>>
>>>> Patty,
>>>> If something isn't working as expected in code, add some debugging.
>>>> Try add this line after the variable is updated:
>>>>
>>>> Debug.Print strSQL
>>>>
>>>> Then open the debug window (press Ctrl+G) to view the statement. You
>>>> can copy the select statement and paste it into the SQL view of a new
>>>> query for troubleshooting.
>>>>
>>>>
>>>> Duane Hookom MVP
>>>> MS Access
>>>>
>>>> ________________________________
From: patty.mapes@gmail.com<mailto:patty.mapes@gmail.com>

>>>>> Date: Sun, 8 Sep 2013 14:39:43 -0400
>>>>> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>>>>
>>>>> Duane,
>>>>> I have tried different variations (=, <,>) that should change the data

>>>>> but nothing is having any impact. It appears that entire WHERE section

>>>>> of the statement is just being ignored, but not throwing any error
>>>>> message.
>>>>>
>>>>>
>>>>> On Sun, Sep 8, 2013 at 2:00 PM, Duane Hookom
>>>>
>>
> <duanehookom@hotmail.com<mailto:duanehookom@hotmail.com>>>>> wrote:
>>>>>
>>>>> I believe you are missing the delimiters around your date value
>>>>>
>>>>
>>>>> strSQL = "SELECT * FROM [diagnosis] WHERE (CDate(StartDate))>&"
>>>>> & DateOfVisit & "&"
>>>>
>>>>>
>>>>> Duane Hookom MVP
>>>>> MS Access
>>>>>
>>>>> ________________________________
>>>>>> From: patty.mapes@gmail.com<mailto:patty.mapes@gmail.com><

>>>>>
>>>>>> I have the following code, failing on the last line:
>>>>>>
>>>>>> DateOfVisit = CDate(Forms!Select_Diagnosis![VisitList])
>>>>>>
>>>>>> Set db = CurrentDb
>>>>>> Set rs = db.OpenRecordset(Domain)
>>>>>> 'select all events
>>>>>>
>>>>>> strSQL = "SELECT * FROM [diagnosis] WHERE (CDate(StartDate))>" &
>>>>>> DateOfVisit
>>>>>>
>>>>>>
>>>>>> If someone can see my error, I will be very grateful.
>>>>>>
>>>>>> Thank you,
>>>>>>
>>>>>> Patty
>>>>>> --
>>>>>> Patricia Mapes

--
Patricia Mapes

--
Patricia Mapes

--
Patricia Mapes

--
Patricia Mapes

--
Patricia Mapes

--
Patricia Mapes

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/