PDA

View Full Version : Cascading combo boxes from SQL Server



MacDaddy
03-02-2010, 07:16 AM
Good afternoon,

I'm not sure if this needs to be in the Excel or SQL section so apologies if I've stuck it in the wrong place:

I have an Excel userform with three combo boxes with choices populated from a SQL Server database for validation purposes.

What I would like to do is make the second and third combo box dependent on the choices selected from the previous combo box selection.

I have done a good Google search for cascading combo boxes but I can't find anything on how to accomplish this if using a SQL Server database as the source.

Somehow the choice of the second and third boxes needs to be filtered in the where clause of the SQL string, but I don't know how to pass this value in the vba code.

If anyone can point me in the right sort of direction I'd be most grateful.

Thanks,

Paul

Bob Phillips
03-02-2010, 07:55 AM
I would do a retriev on the SQL database after each selection, picking those items that match the criteria. Much simpler than fancy Excel tricks to create csacading DDs.

MacDaddy
03-02-2010, 08:06 AM
Hi Xdl,

Thanks for your reply. Could you expand a bit on how to do this please?

At the moment I've got the SQL queries being fired off to the DB in the UserForm_Initialize() which populates all the drop downs simultaneously, I've tried filtering them here but either I'm doing it wrong or it just doesn't like it.

How can I set the query to only retrieve one set of results at a time and wait for the first drop down selection before populating the next?

Thanks again,

Paul

Bob Phillips
03-02-2010, 09:04 AM
I would start by just firing the initial query and populate the first combo. When something is selected there, usethat value to fir the second and so on.