PDA

View Full Version : Solved: Combo box list populated through query



spartacus132
08-30-2006, 10:29 AM
Hi All,
How can i populate a combo box on a form by querying a table in a database on a SQL Server.:think:

I tried searching for VBA examples of the above but couldn't find any.

Thanks,
Anupam

mvidas
08-30-2006, 11:21 AM
Anupam,

Do you have the SQL written to query the table? What about the connection string? These can be done in VBA the same as they can be done from VB, the only difference will be what you do with the results. Please let us know what you have so far so we can know where to begin helping.

If you have nothing so far, what I usually do is to query the DB using Data / Get External Data and follow the wizard. MS Query will come up and help you get what you need, and a button in MS Query will give you the SQL code used. For the connection string we can probably help with that if you don't have it, but we'll need to know more specifics about where/what you're getting first.
Putting the data into the combobox will be probably the easiest thing, but I'm going to hold off on the code to do that until I know the specifics of what you're getting from the DB.

Matt

spartacus132
08-30-2006, 11:41 AM
Hi Matt,
Thanks for your attention to this post. So far i got nothing written out code wise.

Regarding the SQL, here is an example of a query that would populate a combo box:


select DISTINCT assignee FROM Asgnmnt WHERE asgnmnt.Assignee NOT LIKE '%group%'
AND asgnmnt.Assignee NOT LIKE ''

Basically the above query will be probing the database - CallLogs - to output a list of people (their names).

My intentions are to populate a combo box with the query result. The database is on a SQL Server and the connection should use a trusted connection. I am not sure how to create the connection string, i am hoping you can help me with that well.

I got few combo boxes on a form and i want to able to use a query to populate them with queries similar to the one i have included in this reply.

Thanks,
Anupam

mvidas
08-30-2006, 12:04 PM
Hello,

I've never queried a sql server before (trusted or not), but http://www.codeproject.com/database/connectionstrings.asp is a great site for helping with connection strings, so I think we can figure it out.

Give the following a try, you'll have to change "MyServerName" to the server name your db is on:Sub LoadComboBox()
Dim vSQL As String
vSQL = "select DISTINCT asgnmnt.assignee FROM Asgnmnt WHERE " & _
"asgnmnt.Assignee NOT LIKE '%group%' AND asgnmnt.Assignee NOT LIKE ''"

With CreateObject("ADODB.Connection")
.ConnectionString = "Driver={SQL Server};Server=MyServerName;" & _
"Database=CallLogs;Trusted_Connection=yes;"
.Open
UserForm1.ComboBox1.List = .Execute(vSQL).GetRows
.Close
End With
End SubYou'll also have to change the UserForm1.ComboBox1 to the form/CoB you want populated. My fingers are crossed it works! If not, let me know the error you're getting and where, and we'll see what we can do.
The only other thing you might come across is if you have to supply your UN/PW to the db. If so, change the .open line as such:.Open UserID:="spartacus132", Password:="yourpassword"Let me know how it turns out!
Matt

spartacus132
08-30-2006, 12:36 PM
Matt,
Works exactly as desired. However, i had to make one change to the line:
UserForm1.ComboBox1.List = .Execute(vSQL).GetRows

I had to change it to:
UserForm1.ComboBox1.Column = .Execute(vSQL).GetRows

With the original, only the first row was showing up.

Thanks a ton for your short and sweet code.

mvidas
08-30-2006, 12:43 PM
Great!
The difference is between the way the array is returned from the recordset and the way the .List property wants it. You probably could have also done UserForm1.ComboBox1.List = Application.Transpose(.Execute(vSQL).GetRows)But in any case I'm glad it worked!
If you do have many queries like this I might recommend you open the connection, run each Execute command while its open, then close afterwards; may save a bit of time. Please let me know if you have any questions!
Matt