How to pass combobox/textbox selected member into sql string in main module

[yingchai]

Hi VBA gurus,

I have this VBA macro which contains some combo boxes and text boxes. The code is at the ThisWorkbook under Excel Objects. My main VBA code is programmed at the Module1 under Modules folder.

My question is how can I passed the selected combobox/textbox member into thet sql string at Module1? I attached the VBA macro here.

Kindly advise.

[yingchai]

VBA code from Module1

Sub ADO_RecordSet()

Dim qry_my As String
conn_str = "Provider=SQLNCLI10;Server=mymswipmd01;Database=igbqtr;Uid=igbqtr;Pwd=Passwo rd01;"
qry_my = "select jrnl.jrnl_name, jrnl.jrnl_desc, adj.line_id, adj.unit_id, adj.amount from igbqtr.journal jrnl, igbqtr.ADJUSTMENT adj where jrnl.JRNL_ID = adj.jrnl_id"
Set db_my = New ADODB.Connection
Set rs_my = New ADODB.Recordset
db_my.Open conn_str
rs_my.Open qry_my, db_my, adOpenStatic, adLockReadOnly
Range("A16").CopyFromRecordset rs_my
rs_my.Close
Set rs_my = Nothing
db_my.Close
Set db_my = Nothing
End Sub


VBA code from ThisWorkbook

Public rs_my As ADODB.Recordset
Const conn_str As String = "Provider=SQLNCLI10;Server=mymswipmd01;Database=igbqtr;Uid=igbqtr;Pwd=Passwo rd01;"


Private Sub Workbook_Open()

Set rs_my = New ADODB.Recordset

Dim Period_Qry, Year_Qry, Version_Qry, Currency_Qry As String

Period_Qry = "SELECT mem_name FROM igbqtr.PERIOD"
Year_Qry = "SELECT mem_name FROM igbqtr.YEAR"
Version_Qry = "SELECT mem_name FROM igbqtr.VER"
Currency_Qry = "SELECT mem_desc FROM igbqtr.CURR"


Dim Period_List, Year_List, Version_List, Currency_List As String

With rs_my

.Open Period_Qry, conn_str, adOpenStatic, adLockReadOnly
Period_List = .GetString
.Close

.Open Year_Qry, conn_str, adOpenStatic, adLockReadOnly
Year_List = .GetString
.Close

.Open Version_Qry, conn_str, adOpenStatic, adLockReadOnly
Version_List = .GetString
.Close

.Open Currency_Qry, conn_str, adOpenStatic, adLockReadOnly
Currency_List = .GetString
.Close

End With

Period_List = Left(Period_List, Len(Period_List) - 1)
Year_List = Left(Year_List, Len(Year_List) - 1)
Version_List = Left(Version_List, Len(Version_List) - 1)
Currency_List = Left(Currency_List, Len(Currency_List) - 1)

ThisWorkbook.Worksheets(1).OLEObjects("ComboBox1").Object.List = Split(Period_List, vbCr)
ThisWorkbook.Worksheets(1).OLEObjects("ComboBox2").Object.List = Split(Year_List, vbCr)
ThisWorkbook.Worksheets(1).OLEObjects("ComboBox3").Object.List = Split(Version_List, vbCr)
ThisWorkbook.Worksheets(1).OLEObjects("ComboBox4").Object.List = Split(Currency_List, vbCr)

End Sub

[Bob Phillips]

Save the values in module public variables, then you can access them from both.