Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 9th, 2009, 04:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default Concatenate help

Hello!
I have the below code that I am getting an error
"Run-time error '-2147217904 (80040e10)':
No value given for one ore more required parameters."

I use this same code in another database, and it works fine, so it really has me stumped.
Any help would be much appreciated!
Thanks!

The part of the code that becomes highlighted when trying to debug is:
Code:
    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
Here is the whole function that is resides in...

Code:
 
Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = "; ") _
        As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
'   this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'
'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)
'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
 
Old October 9th, 2009, 07:34 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You will have to debug and find out just what the value of pstrSQL is.

The error has nothing to do with concatenation, per se.

You are simply passing in a bad SQL query.
The Following User Says Thank You to Old Pedant For This Useful Post:
lryckman (October 12th, 2009)
 
Old October 9th, 2009, 07:43 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

But the code there is pretty crappy.

If you *are* using ADO (as opposed to DAO, and it appears you are) then it can all be done in about a quarter as much code.

Actually, an even better thing would be to not use this function at all and do it up one level.

But...

Code:
Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = "; ") _
        As String
    Dim rs As ADODB.Recordset
    Set rs = CurrentProject.Connection.Execute( pstrSQL )
    Dim list As String
    list = rs.GetString( , , , pstrDelim )
    rs.Close
    Set rs = Nothing
    Concatenate = Left( list, Len(list) - Len(pstrDelim) )
End Function
NOTE: It will *only* work if your SQL selects *ONLY* one field!

Such as:

SELECT name FROM table WHERE ....
or
SELECT email FROM table WHERE ...
 
Old October 12th, 2009, 08:52 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Red face

Quote:
Originally Posted by Old Pedant View Post
You will have to debug and find out just what the value of pstrSQL is.

The error has nothing to do with concatenation, per se.

You are simply passing in a bad SQL query.

lol! I went back and looked after reading your reply and sure enough, I was missing quotes in my select statement of the query using the Concatenate function!

Thanks!!!
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!





Similar Threads
Thread Thread Starter Forum Replies Last Post
concatenate records davehodges Access 5 November 1st, 2007 10:13 AM
Concatenate Rows stonesbg ASP.NET 2.0 Basics 5 February 6th, 2007 06:12 PM
Hyperlink (Concatenate) JEHalm Excel VBA 1 January 11th, 2006 01:09 PM
Argh! Why does this concatenate? interrupt Javascript How-To 1 August 13th, 2005 08:44 AM
string concatenate phelkuizon Classic ASP Databases 1 September 13th, 2004 04:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.