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!!!
Cheers
Ross
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
Cheers
Ross
Who is Participating?
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?
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.
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.