[AccessD] FW: VBA Function

[Rocky Smolin]

Dear Lists:

 

I don't do much with ADO and less with SQL.  The back end here is SQL. 

 

He says if he puts the code into the QBE it retrieves all the clients with
the string in their email address.  But in the code it only retrieves one.

 

Does anyone see why the code below doesn't work for him?

 

MTIA

 

 

Rocky Smolin

Beach Access Software

760-683-5777

www.bchacc.com

www.e-z-mrp.com

Skype: rocky.smolin

 

 

 

From: James Charlton [mailto:James at charltonweeks.com] 
Sent: Monday, August 07, 2017 11:17 AM
To: Rocky Smolin
Subject: VBA Function

 

I am using the following function to find a client by an email address:

It works ONLY IF  a client is found that only has one email and this is it.

Some clients have multiple emails listed and there are multiple clients that
have the search item in their emails.  

I want all the clients that have the search item in their email field.

How do I fix this?

jc

 

 

Public Function FetchNameFrmEMailPart(ByVal stEMP) As String

    Dim conn As ADODB.Connection, rs As ADODB.Recordset

    Dim stAns As String, stEmail As String

    Set conn = New ADODB.Connection

    conn.ConnectionString = Bwq

    conn.Open

    Set rs = New ADODB.Recordset

    stEMP = Nz(stEMP, "")

    stEmail = "%" & stEMP & "%"

    

    rs.Source = "SELECT LName, FName From tClients " & _

        "WHERE [E-Mail] like '" & stEmail & "'"

    rs.ActiveConnection = conn

    rs.Open

    stAns = ""

    Do While Not rs.EOF

    stAns = stAns & rs(0) & ", " & rs(1) & vbCrLf

    rs.MoveNext

    Loop

    

    FetchNameFrmEMailPart = stAns

    rs.Close

    conn.Close

 

End Function

 

James Charlton

Charlton Weeks LLP

1031 W Ave M14, Suite A

Palmdale, CA 93551

661-265-0969