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
10623

[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