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 December 29th, 2003, 07:47 PM
Registered User
 
Join Date: Dec 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Opening A RecordSet

I wrote a function that is supposed to concatinate all the email addresses contained in a query into one string. The code is as follows:

Function CompileAddresses(ByVal query As String) As String


    Dim rs As ADODB.Recordset
    Dim strEmail As String

    Set rs = New ADODB.Recordset
    strEmail = ""

    rs.Open query, CurrentProject.Connection

    Do While rs.EOF = False
        If rs.Fields("Email") <> "" Then
            strEmail = strEmail & rs.Fields("Email") & ";"
        End If
        rs.MoveNext
    Loop

    CompileAddresses = strEmail

End Function

query is an SQL statement that is passed to the function.

When i try to run this code it gives the following error:

Run-time error '-2147217904(80040e10)':
No value given for one or more required parameters.

This error references this line:
rs.Open query, CurrentProject.Connection

I cant for the life of me figure out what im doing wrong. if anyone has any ideas, they would be greatly appreciated.

 
Old December 29th, 2003, 09:40 PM
Authorized User
 
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That message generally means that one of the fields in your SQL statement is incorrect or doesn't match the name in the table -- either way the issue is with the SQL statement. If you post that maybe we could resolve this for you.

Kenny Alligood
 
Old December 29th, 2003, 10:04 PM
Registered User
 
Join Date: Dec 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The 2 SQL statements that can be passed to this function at the moment are:

SELECT People.Email FROM (Sites INNER JOIN (People INNER JOIN TeamMembership ON People.PID=TeamMembership.PID) ON Sites.ID=TeamMembership.TID) INNER JOIN (Ladders INNER JOIN LadderMembership ON Ladders.LID=LadderMembership.LID) ON Sites.ID=LadderMembership.TID WHERE Ladders.LID LIKE Forms!EmailTeams!cboLadder.Value;

SELECT People.Email FROM Sites INNER JOIN (People INNER JOIN TeamMembership ON People.PID=TeamMembership.PID) ON Sites.ID=TeamMembership.TID WHERE TeamMembership.TID LIKE Forms!EmailTeams!cboTeam.Value;

cboLadder.Value and cboTeam.Value return the primary key value of record that is selected from the the Ladder combo box and the Team combo box respectively. They have different SQL statement to populate themselves and both work fine.

 
Old December 30th, 2003, 02:36 AM
Registered User
 
Join Date: Dec 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

ok, i figured it out. I need to enclose the variable after LIKE with % and quotation marks.

Does anyone know why this is the case even though i dont need to do that when i populate a combo box with using an SQL statement that has a variable?

My problem is now getting the text out of the field. Is my syntax correct? if not, what is the correct syntax?

 
Old December 30th, 2003, 11:35 AM
Registered User
 
Join Date: Jun 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I find the syntax requirements for paramterized queries bewildering. Is there a resource of examples anywhere that shows the syntax in various situations? thanks,
Rick Neifeld, Ph.D. Patent Attorney
President, Neifeld IP Law, PC and StockPricePredictor.com, LLC
2001 Jefferson Davis Highway, Suite 1001
Arlington, VA 22202
Tel: 703-415-0012
Fax: 703-415-0013
Email: rneifeld@neifeld.com
www.Neifeld.com and www.PatentValuePredictor.com
 
Old December 30th, 2003, 07:27 PM
Registered User
 
Join Date: Dec 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

yeah, something like that would be very useful.

I did figure out my problem though. I needed to include rs.MoveFirst right after the rs.Open statement. I didnt think this was needed because i thought that the recordset defaults to the first record. Can anyone provide any insite on this?

 
Old December 31st, 2003, 12:46 AM
Registered User
 
Join Date: Dec 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It seems i have not actually solved the problem. the MoveFirst method doesnt do anything for me. Ive narrowed the problem down to the LIKE statement. If i put in an actual value, like 1 or 2 after LIKE, everything works fine and function returns the concatinated email string. If i put in '%Forms!EmailTeams!cboLadder.Value%' after LIKE, the function returns a blank email string.

Does anyone have any idea what im doing wrong here? Thanks in advance for your help.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Recordset empty when opening querydef DeAnzaJig Access VBA 3 November 14th, 2005 09:31 AM
How to get # of recs without opening recordset amjad_mahmood Excel VBA 0 August 9th, 2005 05:49 AM
Opening recordset based on stored procedure misterqj Access 2 March 17th, 2005 10:09 PM
Problem using WHERE clause in opening Recordset johnman Classic ASP Databases 2 October 23rd, 2003 01:30 PM
Trouble Opening Recordset NicholasM Classic ASP Databases 1 October 18th, 2003 04:03 PM





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