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
ltrain2015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 BarronAuthor, Web DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.