Run time error - "Too Few parameters,Exception 1"

[destr19]

Dim cstsname As String
Dim cstpc As String
 
Dim rstresult As Recordset 'recordset to store output
Dim mydatabase As Database
 
'Variable to store user entered surname
Me.Text20.SetFocus
cstsname = Text20.Text
 
'Variable to store user entered postcode
Me.Text22.SetFocus
cstpc = Text22.Text
 
'sql statement to select from customer table
 
Set mydatabase = CurrentDb
 
Set rstresult = mydatabase.OpenRecordset( _
"SELECT CustomerID, CustomerForename, CustomerSurname, CstPostcode FROM tblcustomer WHERE CustomerSurname = '" & cstsname & "' AND CustPostcode = '" & cstpc & "'", dbOpenDynaset, dbReadOnly)

I am trying to use the select statement to retrieve the matching record from a table of customers details and output them to a list box. I keep getting "Too few parameters, Exception 1". I am using variables as can be seen from the code above and not directly referenceing objects on the Microsoft access form. Any suggestions.

[Stewart Ross]

Hi, and Welcome to Bytes!

There are two different kinds of recordsets which can be used - ADO, which is generally more suited for applications outside of Access that need to refer to tables via the JET database engine (or via connections to client-server databases such as SQL Server), and DAO, the native Access recordset type.

Their methods have different definitions and arguments.

In Access 2003 the default type changed to the slightly different ADO from the native DAO recordsets, leading to confusing error messages like the one you have mentioned.

To resolve this change your definition of rstresult to qualify it with the DAO type:

DIM rstresult as DAO.Recordset

To use DAO recordsets you must also ensure that there is a reference to the Microsoft DAO 3.X object library in your project references. From the VB editor select Tools, References and ensure that the latest version is ticked if it is not currently ticked, otherwise you will find the compiler generates a syntax error on the qualified declaration.

-Stewart

ps the actual error message is "too few parameters, expected 1" (not exception 1)