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;" & _
    ' 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
 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

Open in new window


Ross TurnerManagement Information Support AnalystAsked:
Who is Participating?
Jerry PaladinoConnect With a Mentor Commented:
Write the resulting recordset to its own worksheet that can be cleared and overwritten each time the ADO/SQL query is run.  Define that recordset range as a Named Range or make it a Table and use the Excel Table name as the range.  

On a separate sheet where the user will interact with the program, use a "Data Validation" rule (Data / Data Tools / Data Validation) to create a drop down list box defined by the Named Range of the query record set.

Each time the user enters a Postal code and executes the macro, it should clear the range where the record set is to be written, write the new result set in its place and now a new list will be available in the data validation drop down box.   Define that data validation cell as a named range and use that name or its cell reference wherever you need to continue processing with the users selection.
Steven HarrisPresidentCommented:
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.

Just to make sure I understand...  You want to ask the user for the input?  If yes:

Will they type in a value?
Will they select a cell?
Ross TurnerManagement Information Support AnalystAuthor Commented:
Sorry...if i wan't clear...

The returned (possible) multiple options from the query would then need to be evaluated by the user and the correct one selected.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.