?
Solved

Access97 - Too Few Parameters

Posted on 2006-05-16
12
Medium Priority
?
317 Views
Last Modified: 2008-02-26
I am writing a simple Access 97 database but in my form/subform, I keep getting this error - Too Few Parameters.  Expected 1.  I have a main form called "frmOrders"; its fields include a ClientID, OrderDate (hidden - set to todays date); then my subform on the bottom of the screen (linked by ClientID, OrderDate) has a drop down for AgentState, and some other fields like AmtOwed, Amt Due, etc. - the subforms Control Source is "qryfrmOrders" - based on the table "tblOrders" - the key on this is ClientID, AgentState, and OrderDate.  In this scenario, there is one client with multiple AgentStates.  Everything works fine until I use the dropdown on AgentState and a choice is made, then it comes up with the error Too Few Parameters. Expected 1.  According to Microsoft, they state there is a colunn name that is incorrect but I have looked all all of the names and just cannot come up with anything.  My code for the sql statement is below - I have this same exact code with different names on another form and works perfect.

Private Sub AgentState_AfterUpdate()
Dim db As DATABASE
Dim oRecordset As Recordset
Dim szSql As String
Dim szErr As String

Set db = CurrentDb()

On Error GoTo ErrorHandler

'ISADRA  Here the code will check the tables for duplicate records and inform the user if there is a duplicate
'And also will back up one record by sending ESC so that this duplicate is not entered.
If IsNull(Me![ClientID]) Then
  MsgBox "You must first select a ClientID#!", , " Client needed for File"
  SendKeys "{ESC}"
  Exit Sub
End If

If IsNull(Me![OrderDate]) Then
  MsgBox "You must first enter a OrderDate!!", , " OrderDate needed for File"
  SendKeys "{ESC}"
  Exit Sub
End If

 

szSql = "Select * from tblOrders where AgentState = " & Me![AgentState] & " and  ClientID = " & Me![ClientID] & _
        " and OrderDate = #" & Me![OrderDate] & "#"

If IsNull(Me![AgentState]) = True Then
    Exit Sub
End If

Set oRecordset = db.OpenRecordset(szSql)
If oRecordset.RecordCount > 0 Then
    MsgBox "The State code " & Me![AgentState] & " Already exsists in the  file for " & Me![OrderDate] & _
           "!!....Please Enter a valid State Code for this Client", vbExclamation, "State Code Already Exists"
           SendKeys "{ESC}"
End If


Exit_Sub:
  db.Close
  Exit Sub
 

ErrorHandler:
MsgBox Err.Description
GoTo Exit_Sub
 
End Sub
0
Comment
Question by:Daren Anderson, MSIS
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16693765
where u use textboxes holding values
e.g

szSql = "Select * from tblOrders where AgentState = " & Me![AgentState] & " and  ClientID = " & Me![ClientID] & _
        " and OrderDate = #" & Me![OrderDate] & "#"


check that they have a valie
also u should be able to use

Me.AgentState

or AgentState.Value

check they all hold a value of some sort

instead of recordsets, u could make use of DLOOKUP as well
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16693773
is NZ available on Acc97

NZ(field,"")

this checks for empty and null fields instead of you doing IsNull (which does not check for IsEmpty)

0
 
LVL 77

Expert Comment

by:peter57r
ID: 16693782
Hi CFProcessMgr,

Missing parameters usually points to a spelling error but I can't see anything obvious here.
Add code as below.

msgbox szsql  ' add this line and see if the sql string makes sense
 Set oRecordset = db.OpenRecordset(szSql)

Pete
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.

 
LVL 8

Assisted Solution

by:Pigster14
Pigster14 earned 400 total points
ID: 16693804
I am assuming on this line you are getting the error?

Set oRecordset = db.OpenRecordset(szSql)

After this line:
szSql = "Select * from tblOrders where AgentState = " & Me![AgentState] & " and  ClientID = " & Me![ClientID] & _
        " and OrderDate = #" & Me![OrderDate] & "#"

Input this:

InputBox "", "", szSQL

An input box will come up as the sql statement would look with all the criteria.
Paste it into a query and run it. Does it work?

If so, set the query up to take the parameters directly from the form and then your line:

Set oRecordset = db.OpenRecordset(szSql)
can be
Set oRecordset = db.OpenRecordset(QueryName)

I have had this issue before and I have to play around with putting the sql in the code or passing the parameters through the actual query and then referencing the query.

Thanks.
0
 

Author Comment

by:Daren Anderson, MSIS
ID: 16693814
I have done a step thru in the code and all of my fields in the sql statement are filled in.  Also, I have never had a lot of luck with DLOOKUP so I dont use it.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16693868
can u do this

szSql = "Select * from tblOrders where AgentState = " & Me![AgentState] & " and  ClientID = " & Me![ClientID] & _
        " and OrderDate = #" & Me![OrderDate] & "#"

'add this line
debug.print szSql

then look in immediate window, copy/paste that sql and run in query editor

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16693876
ah ha, just saw pigster's post, Im thinking along the same lines as him

0
 
LVL 77

Expert Comment

by:peter57r
ID: 16693904
CFProcessMgr,
>  subform on the bottom of the screen (linked by ClientID, OrderDate)
> has a drop down for AgentState,
Just noticed this while re-reading...
So

szSql = "Select * from tblOrders where AgentState = " & Me![AgentState] & " and  ClientID = " & Me.Parent.[ClientID] & _
        " and OrderDate = #" & Me.Parent.[OrderDate] & "#"


Pete
0
 

Author Comment

by:Daren Anderson, MSIS
ID: 16694215
I changed the SQL statement to add the .Parent as shown above - no difference.  When I tried Pigster's suggestion, I get this in the inputbox = Select * from tblOrders where AgentState = AZ001 and  ClientID = 6127899000 and OrderDate = #5/16/2006#.  I put this in a new query and try to run it - I get an input box up asking for AZ001 - just like it is not recognized and then no matter what I put in the inputbox, I get this error - This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 600 total points
ID: 16694244
ok, AgentState looks like its text

is that right?

you have to surround it with quotes because its a string


szSql = "Select * from tblOrders where AgentState = '" & Me![AgentState] & "' and  ClientID = " & Me.Parent.[ClientID] & _
        " and OrderDate = #" & Me.Parent.[OrderDate] & "#"
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 600 total points
ID: 16694252
Well at the very least you need:

where AgentState = '" & Me![AgentState] & "' and  

to deal with a text value.  That might be the whole thing but I'm not so sure.

Pete
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 16695009
Have you looked at the actual RecordSource property for the AgentState drop-down. In my experience, it could be that you have something in the Criteria for this query, who's name cannot be resolved, such as a field on a form that is currently closed. Alternatively, look at the Parameter box from the Query menu in the query design view to see if an unexpected parameter has 'crept in'

Just my tuppence worth.

Regards.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

809 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