[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

VBA function with recordset error

I am trying to create a function that returns a concatenated string value from a recordset for a variable that gets passed in and searched for.

Function Code:

Public Function GetSessionRecs(p_sessionname As String)

strSQL = "Select Description from [Session Detail Table] where [SessionName] = p_sessionname "

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

   If rs.RecordCount = 0 Then
      'MsgBox "no records"
   Exit Function
   End If
   
   GetSessionRecs = ""
   
Do Until rs.EOF

  GetSessionRecs = GetSessionRecs & rs!Description

  rs.MoveNext
  Loop

rs.Close

End Function

When I call it from another block of code like this

p_sessionvalues = GetSessionRecs(p_sessionname)

I get:

Run-time error '3016':
Too few parameters. Expected 2.

Pointing to this line:
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
0
TOWELLR
Asked:
TOWELLR
  • 2
  • 2
  • 2
1 Solution
 
Anthony BerenguelCommented:
Try changing this line
strSQL = "Select Description from [Session Detail Table] where [SessionName] = p_sessionname "

Open in new window

to
strSQL = "Select Description from [Session Detail Table] where [SessionName] ='" & p_sessionname & "'"

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
This:
strSQL = "Select Description from [Session Detail Table] where [SessionName] = p_sessionname "


needs to be:

strSQL = "Select Description from [Session Detail Table] where [SessionName] = " & chr$(34) & p_sessionname & chr$(34)

assuming session name is a string.  Also check spelling and spacing.   Anything that is not understood will become a parameter, which when executing in code, Access won't deal with.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
and by the way, so you don't re-invent the wheel:

"Domain Aggregate" for Concatenating Values by Group in Microsoft Access
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

Jim.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
TOWELLRAuthor Commented:
OK It looks like this with a simpler numeric variable:

strSQL = "Select Description from [Session Detail Table] where [Item Code] ='" & p_itemcode & "'"
MsgBox "Func Select: " & strSQL

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

The MsgBox for the insert string looks clean:

Func Select: Select Description from [Session Detail Table] where [Item Code] ='1'

Now it is saying : too few parameters. Expected 1

I will read through the links provided.
0
 
Anthony BerenguelCommented:
ok. So  since it's a numeric field now you don't need the apostrophes. Change this line
strSQL = "Select Description from [Session Detail Table] where [Item Code] ='" & p_itemcode & "'"

Open in new window

to
strSQL = "Select Description from [Session Detail Table] where [Item Code] =" & p_itemcode 

Open in new window

0
 
TOWELLRAuthor Commented:
Solved:

My original syntax for the select was wrong.  Fixed it with the suggestion from aebea.  Thanks

When the error persisted and a read some other articles I knew it must be referring to a bad column somewhere which was my second issue.

The [Description] column was not in the table as Description.  It had another name.

Thanks to all for the help.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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