Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-11-22
3
Medium Priority
?
262 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 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 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