Access 2007 Receiving Run-time error '3061'

jbakestull
jbakestull used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Scrutinize your table and field names for typos.

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

Author

Commented:
I've complied the database and double checked naming, no errors.
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
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.
jbakestull

Author

Commented:
[Entry Exit Provider Id] is text
jerryb30

Commented:
vsql is a string
maybe:
"HAVING tblPSHClients.[Entry Exit Provider Id]= '" & vsql & "'"
jbakestull

Author

Commented:
thanks
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
jbakestull,

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

http:#a38711652
jerryb30

Commented:
I did not notice that, either.
This should be mbizup's.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial