EXCEL VBA: Multiple Results from Database Selection Formula....Help

Hi EE,

I'm trying to basically create a function that looks to a cell reference (uk postcode) and using that value call  a ADODB Connection to MS SQL database using that value in a query and return a list of possible addresses.

This would be fine normally because it would be only one distinct value, however i can have multiples so i need some way of querying (e.g dropdown / userform popup)  
from which i can select the address i want and save as the value.

I've added what I  got so far..... any help would be Fantastic!!!

Public Function ADDRESS(POSTCODE As String) As String
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    
    
 
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source='SQLDATABASE';" & _
                  "Initial Catalog=SOMEDATABASE;" & _
                  "User Id=sa;" & _
                  "Password=password"
                  
    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open the connection and execute.
    conn.Open sConnString
    

    Set rs = conn.Execute("SELECT ADDRESS as Value FROM [VIEW_ADDRESS] Where [POSTCODE] =" + POSTCODE)
    
    
    
    '------------------------------------------------------------------------------------------------------
    '
    'Need Some User Selection Here
    '
    '-------------------------------------------------------------------------------------------------------
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
       ADDRESS = rs.Fields("Value").Value
       
      
    ' Close the recordset
        rs.Close
    Else
 ADDRESS = rs.Fields("Value").Value
        
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    
End Function

Select all Open in new window

Cheers

Ross