Solved

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

Posted on 2013-11-22
3
251 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Moving Excel to AaaS 4 36
Compile Error 7 40
How to add the word OR to the front of each Excel value 4 36
Fixing a embedded format 7 29
This article will show you how to use shortcut menus in the Access run-time environment.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

914 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

13 Experts available now in Live!

Get 1:1 Help Now