Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-11-22
3
Medium Priority
?
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

636 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