how to automatically update sql results in ActiveX Combobox?

[yingchai]

Hi,

I have a macro file here (attached in this thread) where it contains the 'Generate' button, 'Clear' button and an ActiveX Combo box.

The source data for the combo box is pulling from the SQL database.

Each time when I open the macro file, the combo box does not show any results. I need to enable the Developer tab from the Excel ribbon > enter Design mode > double-click on the combo box to bring up the VBA code screen > Run Sub/UserForm (F5) in order to refresh the data set. After doing this sequence, the data will appear in the ActiveX Combo box.

Is there any way to automatically display the data inside ActiveX Combo box once the macro file is opened?

Appreciate if anyone can assist. Thanks

[yingchai]

Attach macro.

[mohanvijay]

Try this code on workbook open event

[vba]
Private Sub Workbook_Open()

Const conn_str As String = "Provider=SQLNCLI10;Server=mymswipmd01;Database=ctrain;Uid=ctrain;Pwd=Passw ord01;"
Dim rs_my As ADODB.Recordset

Set rs_my = New ADODB.Recordset

Dim QQry As String

QQry = "SELECT mem_name FROM ctrain.PERIOD"

Dim S_List As String

With rs_my

.Open QQry, conn_str, adOpenStatic, adLockReadOnly

S_List = .GetString

.Close

End With

S_List = Left(S_List, Len(S_List) - 1)

ThisWorkbook.Worksheets(1).OLEObjects("ComboBox1").Object.List = Split(S_List, vbCr)

End Sub
[/vba]

[yingchai]

Try this code on workbook open event

[vba]
Private Sub Workbook_Open()

Const conn_str As String = "Provider=SQLNCLI10;Server=mymswipmd01;Database=ctrain;Uid=ctrain;Pwd=Passw ord01;"
Dim rs_my As ADODB.Recordset

Set rs_my = New ADODB.Recordset

Dim QQry As String

QQry = "SELECT mem_name FROM ctrain.PERIOD"

Dim S_List As String

With rs_my

.Open QQry, conn_str, adOpenStatic, adLockReadOnly

S_List = .GetString

.Close

End With

S_List = Left(S_List, Len(S_List) - 1)

ThisWorkbook.Worksheets(1).OLEObjects("ComboBox1").Object.List = Split(S_List, vbCr)

End Sub
[/vba]

Hi Mohan,

I had insert your code into my macros, but it still does not update the data in the combo box. Here is what I done. I copied the Workbook_Open() function into my main macro editor and use the Main() function to call that function.

[VBA]
Sub Main()
Call Workbook_Open
Call ADO_RecordSet
Call Write_header
End Sub
[/VBA]

Please advise.

[mohanvijay]

Paste the code in workbook open event which you can can see in Thisworkbook module

[yingchai]

Paste the code in workbook open event which you can can see in Thisworkbook module

Hi Mohan,

I am able to make it works after copying the code into the Thisworkbook module.

If I wish to create another combo box that pulls the members from other table, what is the best way to do it? Should I declare another function in the Thisworkbook module? Eg; Private Sub Workbook2_Open() ?

Thanks.

[mohanvijay]

You can open new Query after closing existing and save the string into another variable like below

[vba]

With rs_my

.Open QQry, conn_str, adOpenStatic, adLockReadOnly

S_List = .GetString

.Close

QQry = "Second combobox query"

.Open QQry, conn_str, adOpenStatic, adLockReadOnly

S_List_2 = .GetString

.Close

End With
[/vba]

[yingchai]

You can open new Query after closing existing and save the string into another variable like below

[vba]

With rs_my

.Open QQry, conn_str, adOpenStatic, adLockReadOnly

S_List = .GetString

.Close

QQry = "Second combobox query"

.Open QQry, conn_str, adOpenStatic, adLockReadOnly

S_List_2 = .GetString

.Close

End With
[/vba]

Thanks Mohan.

I will put that in. One more question, how can I pass the member selected in the combo box to the main sql query inside Module1 module?

This is something like "Select * from <table> where period = 'combobox.selected.value'?