SQL Combobox

[jwbatey07]

i'm trying to get the combobox to come back with my list of customer name, p21_view_customer.customer_name. something is wrong and I can't figure it out. Please help.

Private Sub ComboBox1_Change()
With Application
    .Calculation = xlManual
    .EnableEvents = False
    .ScreenUpdating = False
End With
Dim stSQL As String, sBook As String, rst As ADODB.Recordset, k As Integer, vaData As Variant
Dim objConn As ADODB.Connection
Dim ConnectionString As String
ConnectionString = "Driver={SQL Server}; "
Set objConn = New ADODB.Connection
stSQL = "SELECT  [p21_view_customer].[credit_status], [p21_view_customer].[customer_name], [p21_view_customer].[customer_id] " & _
        "FROM  [dbo].[p21_view_customer] "
With objConn
    .CursorLocation = adUseClient
    .Open ConnectionString
    Set rst = .Execute(stSQL)
End With
With rst
    Set .ActiveConnection = Nothing
    k = .Fields.Count
    vaData = .GetRows
End With
ComboBox1.List = vaData
objConn.Close
Set rst = Nothing
Set objConn = Nothing
End Sub

 

[RoryA]

GetRows returns an array transposed from how you actually want it, so use:

ComboBox1.Column = vaData

and make sure that your combobox's ColumnCount property is set appropriately.

 

[jwbatey07]

Thank you for the response. i've changed "list" to "column". I'm a beginner when it comes to VBA so I'm a little lost on how to set the property on the ColumnCount. Something did occur when I changed "list" to column. I'm now getting back data but the data coming back is from field p21_view_customer.credit_status and not p21_view_customer.customer_name.

 

[jwbatey07]

i'm not sure if what i'm attempting is possible? I've tried so many things and it's not working. Maybe you can let me know if this is possible, I have a database that has customer account information. i would like a single combobox and a single textbox on a form. i want to be able to query a customer using the combobox. Once the customer name is entered or selected, I would like to bring back the customer's credit status in the textbox. i do not need the data being brought back into a sheet.

 

[RoryA]

You’ve returned three fields in your query, so the columncount should be 3. You can adjust the column widths and bound column to display/store whichever fields) you like.

 

[jwbatey07]

that makes sense. I adjusted the sequence in which I had the fields from the table in and I got the customer names to populate in the combobox because I made that the first field in the statement. I now need to figure out the how to assign columns. When a customer is selected I want to have the textbox tell me their current credit status in text form. thank you!

 

[jwbatey07]

Can you give me an idea of how I need to edit my code? It makes sense the solution but I'm having trouble with the code portion.

 

[jwbatey07]

something like this?


Private Sub ComboBox1_Change()
With Application
.Calculation = xlManual
.EnableEvents = False
.ScreenUpdating = False
End With
Dim stSQL As String, rst As ADODB.Recordset, k As Integer, vaData As Variant
Dim objConn As ADODB.Connection
Dim ConnectionString As String
ConnectionString = "Driver={SQL Server};
Set objConn = New ADODB.Connection
stSQL = "SELECT [p21_view_customer].[customer_name], [p21_view_customer].[credit_status], [p21_view_customer].[customer_id] " & _
"FROM [dbo].[p21_view_customer] "
With objConn
.CursorLocation = adUseClient
.Open ConnectionString
Set rst = .Execute(stSQL)
End With
With ComboBox1.Column
.RowSource = stSQL
.ColumnCount = 3
.BoundColumn = 1
End With
objConn.Close
Set rst = Nothing
Set objConn = Nothing
bClear = True

End Sub

 

[RoryA]

With ComboBox1
.Column = rst.GetRows
.ColumnCount = 3
   .BoundColumn = 1
end with

for example.