Solved

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

Posted on 2013-11-22
3
250 Views
Last Modified: 2014-03-20
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

Open in new window


Cheers

Ross
0
Comment
Question by:Ross Turner
3 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39669166
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?
0
 
LVL 7

Author Comment

by:Ross Turner
ID: 39669267
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.

Cheers
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 39671227
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now