• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Access Error - Too Few Parameters

For some reason my code is bombing out on line 15 with the following error:  (Any Ideas?)

-----------------------------------------
Run-time error '3061'                    |
                                                   |
Too few parameters. Expected 2    |  
----------------------------------------

01   Private Sub CustomerPhrase()
02  
03      Dim strItemNum As String
04      Dim strCustNum As String
05      Dim strSQL As String
06      Dim dbs As Database
07      Dim rs As Recordset
08    
09      strItemNum = Trim(Me.Text30)
10      strCustNum = Trim(Me.cust_num)
11    
12      strSQL = "Select Phrase from tblPhrase where CustNum = '" & strCustNum & "' And Product = '" & strItemNum & "';"
13      Debug.Print strSQL
14      Set dbs = CurrentDb
15      Set rs = dbs.OpenRecordset(strSQL, DB_OPEN_DYNASET, dbSeeChanges)
16    
17      If rs!Phrase <> Null Then
18          Me.lblPhrase = rs!Phrase
19      Else
20          Me.lblPhrase = "Customer/Product record does not exist."
21      End If
22      Set dbs = Nothing
23      Set rs = Nothing
24   End Sub
0
ltrain2015
Asked:
ltrain2015
  • 4
  • 2
  • 2
  • +2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:


you might have a mispelled field name or

check this link
http://www.mvps.org/access/queries/qry0013.htm
0
 
ltrain2015Author Commented:
Well I found one mistake, I wasn't pulling info from the right tables but I fixed my SQL statement and now I am getting the following error:

-----------------------------------------
Runtime error 3075
Syntax error (missing operator) in query expression 'tblPhrase.Phrasenum = cast(tblCustProd.Phrase as smallint)'
----------------------------------------

Here is my new code with the new SQL Statement:

Private Sub CustomerPhrase()

    Dim strItemNum As String
    Dim strCustNum As String
    Dim strSQL As String
    Dim dbs As Database
    Dim rs As DAO.Recordset
   
    strItemNum = Trim(Me.Text30)
    strCustNum = Trim(Me.cust_num)
   
    strSQL = "Select tblPhrase.Phrase, tblCustProd.CustNum, tblCustProd.Product " _
             & "from tblPhrase inner join tblCustProd on (tblPhrase.Phrasenum = cast(tblCustProd.Phrase as smallint)) " _
             & "where tblCustProd.CustNum = '" & strCustNum & "' And tblCustProd.Product = '" & strItemNum & "';"
    Debug.Print strSQL
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset(strSQL, DB_OPEN_DYNASET, dbSeeChanges)
   
    If rs!Phrase <> Null Then
        Me.lblPhrase = rs!Phrase
    Else
        Me.lblPhrase = "Customer/Product record does not exist."
    End If
    Set dbs = Nothing
    Set rs = Nothing
End Sub
0
 
PowerShackCommented:
I think the problem is in the sql statement.  It should be the following:

    strsql = "Select tblPhrase.Phrase, tblCustProd.CustNum, tblCustProd.Product " _
             & "from tblPhrase inner join tblCustProd on (tblPhrase.Phrasenum = cast(tblCustProd.Phrase as smallint)) " _
             & "where tblCustProd.CustNum = '" & strCustNum & "'" & " and" & "tblCustProd.Product = '" & strItemNum & "';"


It wasn't a complete string.  You can tell by the word and showing as And.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
ltrain2015Author Commented:
Thanks but that didn't work.  I even tried added a space behind the word 'and' in your revised query.  Any more ideas?
0
 
PowerShackCommented:
Did you get the same syntax error?
0
 
ltrain2015Author Commented:
yes
0
 
Wayne BarronCommented:
Itran2015.

Get you supply a working page example?
And the Database Structor as well.
(Just for the Tables that are used)

I have ran across this error many times in the past.
But cannot remember what I did to fix it.
So without having an actual running code, I cannot help.
Though I have searched the net, but all information differs from the next.
So that is of no help.

Carrzkiss
0
 
Rey Obrero (Capricorn1)Commented:

if you revise this

   strSQL = "Select tblPhrase.Phrase, tblCustProd.CustNum, tblCustProd.Product " _
             & "from tblPhrase inner join tblCustProd on (tblPhrase.Phrasenum = cast(tblCustProd.Phrase as smallint)) " _
             & "where tblCustProd.CustNum = '" & strCustNum & "' And tblCustProd.Product = '" & strItemNum & "';"

to this

   strSQL = "Select tblPhrase.Phrase, tblCustProd.CustNum, tblCustProd.Product " _
             & "from tblPhrase inner join tblCustProd on tblPhrase.Phrasenum = tblCustProd.Phrase " _
             & "where tblCustProd.CustNum = '" & strCustNum & "' And tblCustProd.Product = '" & strItemNum & "';"

what do you get?

I don't know why you are using {cast} , I don't think you can use it  in VBA.

Is field CustNum of type text?
0
 
ltrain2015Author Commented:
capricorn1, Carrzkiss

One thing that I failed to mention is that both of these tables are SQL Server Linked Tables.

When I run it without "cast()" I get:
-------------------------------------------
Runtime error 3615
Type mismatch in expression.  
-------------------------------------------
It has to be this section b/c   "tblPhrase.Phrasenum = tblCustProd.Phrase"

tblPhrase.Phrasenum is smallint
while tblCustProd.Phrase is char

This is why I was trying to do the type conversion.

CustNum is of type char.

Thanks for all of your help.  I have to get this figured out..
0
 
puppydogbuddyCommented:
For the type mismatch, try using the Val function to convert char type to a value as follows:
"tblPhrase.Phrasenum = Val(tblCustProd.Phrase)"
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now