yingchai
10-28-2012, 07:47 PM
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
10-28-2012, 07:54 PM
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
10-29-2012, 12:49 AM
Save the values in module public variables, then you can access them from both.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.