populating Textboxes

[white_flag]

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:

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

[white_flag]

the files
book-database.zip

[SamT]

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

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

First try

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

[white_flag]

Hello Sam

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

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)

[SamT]

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

[white_flag]

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.

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