Ross Turner
asked on
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
ASKER
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
The returned (possible) multiple options from the query would then need to be evaluated by the user and the correct one selected.
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?