Solved

Access 2007 Receiving Run-time error '3061'

Posted on 2012-12-20
11
394 Views
Last Modified: 2012-12-27
Receiving Run-time error '3061' Too few parameters. Expected @ Set rst = CurrentDb.OpenRecordset(strSql). I can't figure out why.

'Total Households Served
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim vcatch As String
Dim vsql As String

vsql = Me.cboProvider

strSql = "SELECT tblPSHClients.[Entry Exit Provider Id], Count(tblPSHClients.[Client Uid]) AS [CountOfClient Uid] " & vbCrLf & _
"FROM tblPSHClients " & vbCrLf & _
"WHERE (((tblPSHClients.[Household Relate# Head Of Household])<>""No"")) " & vbCrLf & _
"GROUP BY tblPSHClients.[Entry Exit Provider Id] " & vbCrLf & _
"HAVING tblPSHClients.[Entry Exit Provider Id]= " & vsql & ";"

Set rst = CurrentDb.OpenRecordset(strSql)
If Not rst.EOF Then
Me.txtTotalHouseholds = rst![CountOfClient Uid]
Else
'MsgBox "Client" & " " & Me.txtsearch & " " & "does not have a veteran stauts"
Me.txtTotalHouseholds.Value = 0
End If

Set rst = Nothing
Set db = Nothing
0
Comment
Question by:jbakestull
  • 4
  • 3
  • 2
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38711641
Scrutinize your table and field names for typos.

This error generally means that one of your field names is unrecognized.
0
 

Author Comment

by:jbakestull
ID: 38711645
I've complied the database and double checked naming, no errors.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38711647
I've also simplified your sql here - you don't need the vbCRLF's:

strSql = "SELECT tblPSHClients.[Entry Exit Provider Id], Count(tblPSHClients.[Client Uid]) AS [CountOfClient Uid] " &  _
"FROM tblPSHClients " &  _
"WHERE tblPSHClients.[Household Relate# Head Of Household] <> 'No' " &  _
"GROUP BY tblPSHClients.[Entry Exit Provider Id] "  & _
"HAVING tblPSHClients.[Entry Exit Provider Id]= " & vsql 

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38711652
Simplified a little more:


strSql = "SELECT [Entry Exit Provider Id], Count([Client Uid]) AS [CountOfClient Uid] " &  _
"FROM tblPSHClients " &  _
"WHERE [Household Relate# Head Of Household] <> 'No' " &  _
"GROUP BY [Entry Exit Provider Id] "  & _
"HAVING [Entry Exit Provider Id]= " & vsql 

Open in new window


Also is Entry Exit Provider Id text or numeric?  
If it is text, use this instead:

strSql = "SELECT [Entry Exit Provider Id], Count([Client Uid]) AS [CountOfClient Uid] " &  _
"FROM tblPSHClients " &  _
"WHERE [Household Relate# Head Of Household] <> 'No' " &  _
"GROUP BY [Entry Exit Provider Id] "  & _
"HAVING [Entry Exit Provider Id]='" & vsql  & "'"

Open in new window


Finally if none of these ideas help try renaming [Household Relate# Head Of Household] to something that does not include any special characters.
0
 

Author Comment

by:jbakestull
ID: 38711657
[Entry Exit Provider Id] is text
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38711661
vsql is a string
maybe:
"HAVING tblPSHClients.[Entry Exit Provider Id]= '" & vsql & "'"
0
 

Author Comment

by:jbakestull
ID: 38711669
thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38711679
jbakestull,

Did you see my last suggestion for revising your query for a text [Entry Exit Provider Id]?

http:#a38711652
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38711686
I did not notice that, either.
This should be mbizup's.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

830 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