CODEWITHSUNDEEP
sqlexcelvbacombobox
white_flag
white_flag

Reputation: 71

vba to populate a textbox from SQL queries when combobox is change it

I have a userform with one textbox and one combobox in EXCEL. This userform is connected to a small data base (one table with 2 columns) Combobox is populated with the values from the first column of databasqe table I like when the combobox is changing the textbox to be automatic populated with the corespondent value from the second column. I have the following code but it is not working: Please, can someone help me?

Sub PopulateTB()

    Dim rs As Recordset
    Dim db As database
    Dim SQL As String

    Set db = OpenDatabase(ThisWorkbook.Path & "\materiale.mdb")

    SQL = "SELECT values_col2 FROM table_db WHERE values_col1 = " & UserForm1.ComboBox1.Value & ";"
    Set rs = db.OpenRecordset(sql)


 Do Until rs.EOF = True
    UserForm1.TextBox1.Value = rs.Fields(SQL)
    rs.MoveNext
 Loop

   rs.Close
    Set db = Nothing
    Set rs = Nothing

End Sub

Thank you!

Upvotes: 0

Views: 5207

Answers (1)

white_flag
white_flag

Reputation: 71

I putted like this and it is ok

Sub PopulateTB(ByRef ctl As Control, ByVal strTable As String, ByVal strField As String, Optional ByVal strCriteria As String)

    Dim strSQL As String
    Dim strSQLcount As String
    Dim rs As Recordset
    Dim db As Database
    Dim rsCount As Recordset, totalCol As Long
    Dim varRecords As Variant

    Set db = OpenDatabase(ThisWorkbook.Path & "\materiale.mdb")

    strSQLcount = ""
    strSQLcount = strSQLcount & " " & "SELECT COUNT(*) AS Total FROM " & "[" & strTable & "]"
    Set rsCount = db.OpenRecordset(strSQLcount)
    totalCol = rsCount!Total
    rsCount.Close
    Set rsCount = Nothing



    strSQL = ""
    strSQL = strSQL & " " & "SELECT" & "[" & strField & "]"
    strSQL = strSQL & " " & "FROM " & "[" & strTable & "]"

    Set rs = db.OpenRecordset(strSQL)



    varRecords = rs.GetRows(totalCol)
    ctl.Value = varRecords(0, Me.ComboBox1.ListIndex)

    rs.Close
    db.Close
    Set db = Nothing
    Set rs = Nothing


End Sub

Upvotes: 0

Related Questions