populating Textboxes

Hello

I have an user form in excel file. I like to know how can I populate some textboxes based on a combobox selection.
The combobox is populated from a access database. (see attachment)
I an new to sql queries so a bit of help will be appreciated.
Thank you!

ps. I try to upload the files but it is not going.
I have an userform in excel with 5 textboxes + 1 combobox
my code here:

Code:

---

Option Explicit
Private Sub ComboBox1_Change()
    PopulateTB Me.TextBox1, "materials", "name", Me.ComboBox1
End Sub

Private Sub CommandButton1_Click()

End Sub
Private Sub UserForm_Initialize()
    PopulateCB Me.ComboBox1, "materials", "name"
End Sub

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

    Dim strSql As String
    Dim rs As Recordset
    Dim db As database
   
    Set db = OpenDatabase(ThisWorkbook.Path & "\materials.mdb")
   
   
    strTable = "[" & strTable & "]"
   
    strSql = ""
    strSql = strSql & " " & "SELECT DISTINCT" & "[" & strField & "]"
    strSql = strSql & " " & "FROM " & strTable
    strSql = strSql & " " & "WHERE " & "[" & strField & "]" & " IS NOT NULL"
    strSql = strSql & " " & strCriteria
    strSql = strSql & " " & "ORDER BY " & "[" & strField & "]"
   
    Set rs = db.OpenRecordset(strSql)

    ctl.Clear

    Do While Not rs.EOF

        ctl.AddItem rs.Fields(strField)
        rs.MoveNext
       
    Loop
   
    rs.Close
                   
End Sub

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

    Dim strSql As String
    Dim rs As Recordset
    Dim db As database
   
    Set db = OpenDatabase(ThisWorkbook.Path & "\materials.mdb")
   
   
    strTable = "[" & strTable & "]"
   
    strSql = ""
    strSql = strSql & " " & "SELECT DISTINCT" & "[" & strField & "]"
    strSql = strSql & " " & "FROM " & strTable
    strSql = strSql & " " & "WHERE " & "[" & strField & "]" & " ="
    strSql = strSql & " " & strCriteria
    strSql = strSql & " " & "ORDER BY " & "[" & strField & "]"
   
    Set rs = db.OpenRecordset(strSql)

    ctl.Clear

    Do While Not rs.EOF

        ctl.AddItem rs.Fields(strField)
        rs.MoveNext
       
    Loop
   
    rs.Close
                   
End Sub

---

The combobox is populated via PopulateCB
I have error in PopulateTB, error 3061 - Too few parameters. Expected 1

the files
Attachment 10623

I Don't really understand SQL, but
If strCriteria = "" then

Code:

---

strSql = strSql & " " & strCriteria 'Returns two spaces in next line

---

First try

Code:

---

Dim CriteriaString As String
'
'
If strCriteria = "" Then
CriteriaString = "" 'Returns no spaces for next line
Else
Criteria String = " " & strCrtiteria
End If
'
'
strSql = strSql & CriteriaString

---

Hello Sam

I put the code like this (it was missing the ' from query):

Code:

---

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

    Dim strSql As String
    Dim rs As Recordset
    Dim db As database
   
    Set db = OpenDatabase(ThisWorkbook.Path & "\materials.mdb")
   
   
    strTable = "[" & strTable & "]"
   
    strSql = ""
    strSql = strSql & " " & "SELECT " & "[" & strField & "]"
    strSql = strSql & " " & "FROM " & strTable
    strSql = strSql & " " & "WHERE " & "[" & strField & "]" & " = '"
    strSql = strSql & "" & strCriteria & "'"

   
    Set rs = db.OpenRecordset(strSql)
    ctl.Text = rs.Fields(strField)
   
    rs.Close
                   
End Sub

---

but now, to populate textbox2 when the combobox is change with the value from database column(2)

I have to run. Check back in 4-5 hours.

I forget to put the solution that was fitting for me:
Populate a combobox from database column1 and fill a text box from database column2 based on combobox selection.

Code:

---

Sub PopulateTB(ByRef ctl As Control, ByRef nameCombobox As Control, ByVal strTable As String, ByVal strField As String, ByVal strOrder 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
   
On Error Resume Next
   
    Set db = OpenDatabase(ThisWorkbook.Path & "\materials.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 & "]"
    strSQL = strSQL & " " & strCriteria
    strSQL = strSQL & " " & "ORDER BY " & strOrder
   
    Set rs = db.OpenRecordset(strSQL)


 
    varRecords = rs.GetRows(totalCol)
    ctl.Value = varRecords(0, nameCombobox.ListIndex)
   
    rs.Close
    db.Close
    Set db = Nothing
    Set rs = Nothing
   
                   
End Sub
Sub PopulateCB(ByRef ctl As Control, ByVal strTable As String, ByVal strField As String, ByVal strOrder As String, Optional ByVal strCriteria As String)

    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim db As DAO.database
   
    Set db = OpenDatabase(ThisWorkbook.Path & "\materials.mdb")
   
   
 
    'strTable = "[" & strTable & "]"
   
    strSQL = ""
    strSQL = strSQL & " " & "SELECT " & "[" & strField & "]"
    strSQL = strSQL & " " & "FROM " & "[" & strTable & "]"
    strSQL = strSQL & " " & "ORDER BY " & strOrder

   

    Set rs = db.OpenRecordset(strSQL)

   
    ctl.Clear

    Do While Not rs.EOF

        ctl.AddItem rs.Fields(strField)
        rs.MoveNext
       
    Loop
   
    rs.Close
    db.Close
    Set db = Nothing
    Set rs = Nothing
                   
End Sub

---