Too Few parameters. Expected 2. (3061) - Northwind DB - Please Help

J

jermaine123

I am a newbie when it comes to access, so I beg you to be patient. I
downloaded the Northwind 2007 db and made some adjustments. My goal is to
allow persons logging into the db to only customer records to which they are
assigned and administrators should view all records. I used the following as
criteria on the Customer Extended query...

Like IIf([TempVars]![CurrentUserLevel]=4,"*",[TempVars]![CurrentUserID]). The
query works fine and filters the records when I run it in access.

When I go back to the Order Details page and choose a customer I get the
error...

Too Few parameters. Expected 2. (3061)
RecordsetWrapper.OpenRecordset

strSQL = "SELECT * FROM [Customers Extended] WHERE [ID] = 52"

There is a module called RecordsetWrapper in NOrthwind and this is where the
error is pointing.

I have included the SQL from my query below and also the code for the
RecordsetWrapper OpenRecordset function.

I need your help URGENTLY.


CUSTOMERS EXTENDED QUERY
==========================

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]
),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS
[File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First
Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]))
AS [Contact Name], Customers.*, Customers.[PTS ID]
FROM Customers LEFT JOIN [Benefits Profile] ON Customers.ID = [Benefits
Profile].[Customer ID]
WHERE (((Customers.[PTS ID]) Like IIf([TempVars]![CurrentUserLevel]=4,"*",
[TempVars]![CurrentUserID])))
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First
Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])
), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),
IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));

RECORDSETWRAPPER OpenRecordset Function
=====================================

Public Function OpenRecordset(Domain As String, _
Optional Criteria As String = "1=1", _
Optional OrderBy As String, _
Optional RecordsetType As DAO.RecordsetTypeEnum
= dbOpenDynaset, _
Optional RecordsetOptions As DAO.
RecordsetOptionEnum _
) As Boolean


If Not m_rs Is Nothing Then
' Close the recordset so it can be re-used
CloseRecordset
End If

Dim strSQL As String
strSQL = "SELECT * FROM [" & Domain & "] WHERE " & Criteria


If OrderBy <> "" Then
strSQL = strSQL & " ORDER BY " & OrderBy
End If

On Error GoTo ErrorHandler
Set m_rs = CurrentDb.OpenRecordset(strSQL, RecordsetType,
RecordsetOptions)
OpenRecordset = True

Done:
Exit Function
ErrorHandler:
' verify the private Recordset object was not set
Debug.Assert m_rs Is Nothing

' Resume statement will be hit when debugging
If eh.LogError("RecordsetWrapper.OpenRecordset", "strSQL = " & Chr(34) &
strSQL & Chr(34)) Then Resume
End Function
 
J

jermaine123 via AccessMonster.com

Ken,

I have tried the code you sent but I was unable to get it working. I kept
getting the error ..

" is not a valid name. Make sure it does not include invalid characters or
punctuations that are too long (3125).
RecordsetWrapper.OpenRecordset

strSQL = "SELECT * FROM [Customers Extended] WHERE [ID] = 52"


Could you please give me a quick overview of how to implement your demo in
Access 2007?
Thanks Ken,

I have to run out a bit. I am going to look look at the demo and the code
you sent and reply in a little while.
Jermaine
PS: You might be interested in my demo of the sort of 'row level security'
which you are aiming at. Its available from:
Click to expand...
[quoted text clipped - 8 lines]
Ken Sheridan
Stafford, England
Click to expand...
Click to expand...
 
K

KenSheridan via AccessMonster.com

Jermaine:

Firstly, as regards my demo file, not using Access 2007 myself I can't say
for certain whether there are any issues or not, but I'd have thought it
would convert without any problems. I've had no feedback to suggest the
contrary at any rate.

You'll see that it contains the following function in the basSecDemo module:

Public Function GetCurrentUser() As String

On Error Resume Next
GetCurrentUser = [Forms]![frmLogin]![cboUserName]

End Function

In the demo this gets the name from the frmLogin form which remains open, but
hidden, throughout the application. This is just a simulation of course as
anyone can log in as any user in the demo; which of course is how it
demonstrates how the records returned are restricted on the basis of
different users' logging in. You are storing the user names some other way
by the look of it, so you'd just have to amend the above function so that it
gets the name of the current user from wherever you are obtaining it.

You'd need to include the Users, UserGroups and UserGroupsMembership tables
in your database, but these can just be imported, along with the
relationships between them, from my demo, emptied, and then filled with your
own data. And you'd need to add the UserGroupFlag column to your principle
table.

Turning to your current problem, opening the recordset could probably be
simplified by writing a smaller function than the built in OpenRecordset
function. Essentially all you seem to be doing is opening a recordset based
on your Customers Extended query, restricting it on the ID column. So if you
can explain where the ID value is obtained from, i.e. what is passed into the
function as its Criteria argument, I could probably come up with a simplified
function which would do the job based on your query.

Ken Sheridan
Stafford, England
Ken,

I have tried the code you sent but I was unable to get it working. I kept
getting the error ..

" is not a valid name. Make sure it does not include invalid characters or
punctuations that are too long (3125).
RecordsetWrapper.OpenRecordset

strSQL = "SELECT * FROM [Customers Extended] WHERE [ID] = 52"

Could you please give me a quick overview of how to implement your demo in
Access 2007?
Thanks Ken,
Click to expand...
[quoted text clipped - 7 lines]
Click to expand...
 
J

jermaine123 via AccessMonster.com

Thanks for your help Ken,

Regarding my current problem,

On the Order Details form there is a combobox (named: Customer ID) that is
linked to the Customer ID field in the Customer Extended query. This combo
box allows you to choose the customer for which you want to run a transaction.
The code in the update event of this combobox is...

Private Sub Customer_ID_AfterUpdate()
SetFormState False

If Not IsNull(Me![Customer ID]) Then
SetDefaultShippingAddress
End If

End Sub

When a customer is chosen it automatically fills in the Shipping Addess for
the Customer. You will see that it calls the SetDefaultShippingAddress()
function which then calls OpenRecordset(). This is where it tries to pass in
the ID value obtained from the combobox above. The SetDefaultShippingAddress
function code is below...

Sub SetDefaultShippingAddress()
If IsNull(Me![Customer ID]) Then
ClearShippingAddress
Else

Dim rsw As New RecordsetWrapper
If rsw.OpenRecordset("Customers Extended", "[ID] = " & Me.Customer_ID)
Then
With rsw.Recordset
Me![Ship Name] = ![Contact Name]
Me![Ship Address] = ![Address]
Me![Ship City] = ![City]
Me![Ship State/Province] = ![State/Province]
Me![Ship ZIP/Postal Code] = ![ZIP/Postal Code]
Me![Ship Country/Region] = ![Country/Region]
End With
End If
End If
End Sub

NB: When I log in I create 2 temporary global variables [TempVars]!
[CurrentUserLevel] and [TempVars]![CurrentUserID] which I use to filter the
Customers Extended query

Hope my explanation makes sense.

Thanks Again,

Jermaine
Jermaine:

Firstly, as regards my demo file, not using Access 2007 myself I can't say
for certain whether there are any issues or not, but I'd have thought it
would convert without any problems. I've had no feedback to suggest the
contrary at any rate.

You'll see that it contains the following function in the basSecDemo module:

Public Function GetCurrentUser() As String

On Error Resume Next
GetCurrentUser = [Forms]![frmLogin]![cboUserName]

End Function

In the demo this gets the name from the frmLogin form which remains open, but
hidden, throughout the application. This is just a simulation of course as
anyone can log in as any user in the demo; which of course is how it
demonstrates how the records returned are restricted on the basis of
different users' logging in. You are storing the user names some other way
by the look of it, so you'd just have to amend the above function so that it
gets the name of the current user from wherever you are obtaining it.

You'd need to include the Users, UserGroups and UserGroupsMembership tables
in your database, but these can just be imported, along with the
relationships between them, from my demo, emptied, and then filled with your
own data. And you'd need to add the UserGroupFlag column to your principle
table.

Turning to your current problem, opening the recordset could probably be
simplified by writing a smaller function than the built in OpenRecordset
function. Essentially all you seem to be doing is opening a recordset based
on your Customers Extended query, restricting it on the ID column. So if you
can explain where the ID value is obtained from, i.e. what is passed into the
function as its Criteria argument, I could probably come up with a simplified
function which would do the job based on your query.

Ken Sheridan
Stafford, England
Ken,
Click to expand...
[quoted text clipped - 15 lines]
Click to expand...
 
K

KenSheridan via AccessMonster.com

You should be able to do it the way I described by first establishing one
recordset based on the query and then another by filtering it:

Sub SetDefaultShippingAddress()

If IsNull(Me![Customer ID]) Then
ClearShippingAddress
Else
Dim dbs AS DAO.Database
Dim rstAll as DAO.Recordset
Dim rstFiltered as DAO.Recordset
Dim qdf as DAO.Querydef
Dim prm As DAO.Parameter

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("Customers Extended")

For Each prm in qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rstAll = qdf.OpenRecordset
rstAll.Filter = "ID = " & Me.Customer_ID
Set rstFiltered= rstAll.OpenRecordset

With rstFiltered
Me![Ship Name] = ![Contact Name]
Me![Ship Address] = ![Address]
Me![Ship City] = ![City]
Me![Ship State/Province] = ![State/Province]
Me![Ship ZIP/Postal Code] = ![ZIP/Postal Code]
Me![Ship Country/Region] = ![Country/Region]
End With
End If

End Sub

Ken Sheridan
Stafford, England
Thanks for your help Ken,

Regarding my current problem,

On the Order Details form there is a combobox (named: Customer ID) that is
linked to the Customer ID field in the Customer Extended query. This combo
box allows you to choose the customer for which you want to run a transaction.
The code in the update event of this combobox is...

Private Sub Customer_ID_AfterUpdate()
SetFormState False

If Not IsNull(Me![Customer ID]) Then
SetDefaultShippingAddress
End If

End Sub

When a customer is chosen it automatically fills in the Shipping Addess for
the Customer. You will see that it calls the SetDefaultShippingAddress()
function which then calls OpenRecordset(). This is where it tries to pass in
the ID value obtained from the combobox above. The SetDefaultShippingAddress
function code is below...

Sub SetDefaultShippingAddress()
If IsNull(Me![Customer ID]) Then
ClearShippingAddress
Else

Dim rsw As New RecordsetWrapper
If rsw.OpenRecordset("Customers Extended", "[ID] = " & Me.Customer_ID)
Then
With rsw.Recordset
Me![Ship Name] = ![Contact Name]
Me![Ship Address] = ![Address]
Me![Ship City] = ![City]
Me![Ship State/Province] = ![State/Province]
Me![Ship ZIP/Postal Code] = ![ZIP/Postal Code]
Me![Ship Country/Region] = ![Country/Region]
End With
End If
End If
End Sub

NB: When I log in I create 2 temporary global variables [TempVars]!
[CurrentUserLevel] and [TempVars]![CurrentUserID] which I use to filter the
Customers Extended query

Hope my explanation makes sense.

Thanks Again,

Jermaine
Jermaine:
Click to expand...
[quoted text clipped - 42 lines]
Click to expand...
 
H

hennie

Hi,

I have not read through the thread but I have found on several occasions the
same error code. What I have noticed in the past is whenever I try to run a
query/sql in code and it is based on another query or queries I receive the
"Expected two parameters or to few parameters. " When I change the query
into a Make table query and use the resulting table as the record set my
problem disappear.

Hennie

KenSheridan via AccessMonster.com said:
You should be able to do it the way I described by first establishing one
recordset based on the query and then another by filtering it:

Sub SetDefaultShippingAddress()

If IsNull(Me![Customer ID]) Then
ClearShippingAddress
Else
Dim dbs AS DAO.Database
Dim rstAll as DAO.Recordset
Dim rstFiltered as DAO.Recordset
Dim qdf as DAO.Querydef
Dim prm As DAO.Parameter

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("Customers Extended")

For Each prm in qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rstAll = qdf.OpenRecordset
rstAll.Filter = "ID = " & Me.Customer_ID
Set rstFiltered= rstAll.OpenRecordset

With rstFiltered
Me![Ship Name] = ![Contact Name]
Me![Ship Address] = ![Address]
Me![Ship City] = ![City]
Me![Ship State/Province] = ![State/Province]
Me![Ship ZIP/Postal Code] = ![ZIP/Postal Code]
Me![Ship Country/Region] = ![Country/Region]
End With
End If

End Sub

Ken Sheridan
Stafford, England
Thanks for your help Ken,

Regarding my current problem,

On the Order Details form there is a combobox (named: Customer ID) that is
linked to the Customer ID field in the Customer Extended query. This combo
box allows you to choose the customer for which you want to run a
transaction.
The code in the update event of this combobox is...

Private Sub Customer_ID_AfterUpdate()
SetFormState False

If Not IsNull(Me![Customer ID]) Then
SetDefaultShippingAddress
End If

End Sub

When a customer is chosen it automatically fills in the Shipping Addess
for
the Customer. You will see that it calls the SetDefaultShippingAddress()
function which then calls OpenRecordset(). This is where it tries to pass
in
the ID value obtained from the combobox above. The
SetDefaultShippingAddress
function code is below...

Sub SetDefaultShippingAddress()
If IsNull(Me![Customer ID]) Then
ClearShippingAddress
Else

Dim rsw As New RecordsetWrapper
If rsw.OpenRecordset("Customers Extended", "[ID] = " &
Me.Customer_ID)
Then
With rsw.Recordset
Me![Ship Name] = ![Contact Name]
Me![Ship Address] = ![Address]
Me![Ship City] = ![City]
Me![Ship State/Province] = ![State/Province]
Me![Ship ZIP/Postal Code] = ![ZIP/Postal Code]
Me![Ship Country/Region] = ![Country/Region]
End With
End If
End If
End Sub

NB: When I log in I create 2 temporary global variables [TempVars]!
[CurrentUserLevel] and [TempVars]![CurrentUserID] which I use to filter
the
Customers Extended query

Hope my explanation makes sense.

Thanks Again,

Jermaine
Jermaine:
Click to expand...
[quoted text clipped - 42 lines]
Ken Sheridan
Stafford, England
Click to expand...
Click to expand...
Click to expand...
 
J

jermaine123 via AccessMonster.com

Ken,

That works perfectly. You are the Master of your domain. This site is the
best

Thanks again

Jermaine
You should be able to do it the way I described by first establishing one
recordset based on the query and then another by filtering it:

Sub SetDefaultShippingAddress()

If IsNull(Me![Customer ID]) Then
ClearShippingAddress
Else
Dim dbs AS DAO.Database
Dim rstAll as DAO.Recordset
Dim rstFiltered as DAO.Recordset
Dim qdf as DAO.Querydef
Dim prm As DAO.Parameter

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("Customers Extended")

For Each prm in qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rstAll = qdf.OpenRecordset
rstAll.Filter = "ID = " & Me.Customer_ID
Set rstFiltered= rstAll.OpenRecordset

With rstFiltered
Me![Ship Name] = ![Contact Name]
Me![Ship Address] = ![Address]
Me![Ship City] = ![City]
Me![Ship State/Province] = ![State/Province]
Me![Ship ZIP/Postal Code] = ![ZIP/Postal Code]
Me![Ship Country/Region] = ![Country/Region]
End With
End If

End Sub

Ken Sheridan
Stafford, England
Thanks for your help Ken,
Click to expand...
[quoted text clipped - 55 lines]
Click to expand...
 
J

jermaine123 via AccessMonster.com

Thanks for the tip hennie
Hi,

I have not read through the thread but I have found on several occasions the
same error code. What I have noticed in the past is whenever I try to run a
query/sql in code and it is based on another query or queries I receive the
"Expected two parameters or to few parameters. " When I change the query
into a Make table query and use the resulting table as the record set my
problem disappear.

Hennie
You should be able to do it the way I described by first establishing one
recordset based on the query and then another by filtering it:
Click to expand...
[quoted text clipped - 101 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error 3061 - too few parameters 2
ERROR 3061 - Too few parameters 1
Run-time error 3061. Too Few Parameters. Expected 2. 2
Error 3061 Too few parameters expected 1 - good tested query 4
Please Help With Runtime Error '3061': Too few parameters. Expected 1 2
can't figure out 3061 error = too few parameters 0
Help with Syntax Too Few Parameters 9
Please Help, RunTime 3061 Too few Parameters 7

Top