Passing combo box control to procedure (1 Viewer)

[GregoireLeGros]

On my form I have a combo box where the user can select the sex of a person. I had set the row source of the combo to a table, tblSex and this works fine. Now I want to programatically add the table entries ( simply male or female) to the row source, having set the combobox to value list.
I cant see how to pass the combobox and tablename parameters and I am getting an error on form load "The expression you entered as the event property setting produced the following error: UserDefined type not defined"
The calling procedure from frmPeople is

'---------------------------------------------------------------------------------------
' Procedure : Form_Load
' Author    : Administrator
' Date      : 21/11/2013
' Purpose   :
'---------------------------------------------------------------------------------------
'
Private Sub Form_Load()
   On Error GoTo Form_Load_Error

Call PopulateCombo(cmbSex, "tblSex")
   On Error GoTo 0
   Exit Sub

Form_Load_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Load of VBA Document Form_frmPeople"

End Sub

The called procedure PopulateCombo() is

Public Sub PopulateCombo(ByVal sCtl As ComboBox, ByVal sTable As acTable)
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset(sTable)

With sCtl
rst.MoveFirst
Do While Not rst.EOF
.AddItem Item:=rst(0)   'the table has only one column
rst.MoveNext
Loop
End With

rst.Close
Set db = Nothing
End Sub

I think the way the parameters combobox and tablename are passed is incorrect. I want to write a robust procedure which I can use for each of the comboboxes on my form.

 

[Solo712]

On my form I have a combo box where the user can select the sex of a person. I had set the row source of the combo to a table, tblSex and this works fine. Now I want to programatically add the table entries ( simply male or female) to the row source, having set the combobox to value list.
I cant see how to pass the combobox and tablename parameters and I am getting an error on form load "The expression you entered as the event property setting produced the following error: UserDefined type not defined"
The calling procedure from frmPeople is

'---------------------------------------------------------------------------------------
' Procedure : Form_Load
' Author    : Administrator
' Date      : 21/11/2013
' Purpose   :
'---------------------------------------------------------------------------------------
'
Private Sub Form_Load()
   On Error GoTo Form_Load_Error
 
Call PopulateCombo(cmbSex, "tblSex")
   On Error GoTo 0
   Exit Sub
 
Form_Load_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Load of VBA Document Form_frmPeople"
 
End Sub

The called procedure PopulateCombo() is

Public Sub PopulateCombo(ByVal sCtl As ComboBox, ByVal sTable As acTable)
Dim db As DAO.Database
Dim rst As DAO.Recordset
 
Set db = CurrentDb()
Set rst = db.OpenRecordset(sTable)
 
With sCtl
rst.MoveFirst
Do While Not rst.EOF
.AddItem Item:=rst(0)   'the table has only one column
rst.MoveNext
Loop
End With
 
rst.Close
Set db = Nothing
End Sub

I think the way the parameters combobox and tablename are passed is incorrect. I want to write a robust procedure which I can use for each of the comboboxes on my form.

Any reason why you don't create "M" and "F" for the combo box as a value pick list ? What advantage do you see in creating a table for this ?

Best,
J.

 

[JHB]

When you call the procedure, then you've to look at what you are passing to it.
cmbSex is a control and "tblSex" is a string.

..
Call PopulateCombo(cmbSex, "tblSex")
...
Public Sub PopulateCombo(ByVal sCtl As Control, ByVal sTable As String)

 

[Mihail]

Why you need VBA in order to populate the combo ?

 

[GregoireLeGros]

I found out this is how it should be done
calling procedure

Private Sub Form_Load()
   On Error GoTo Form_Load_Error

Call PopulateCombo(cmbSex, "tblSex")
Call PopulateCombo(cmbMaritalStatus, "tblMaritalStatus")
Call PopulateCombo(cmbEthnicity, "tblEthnicity")
............

called procedure

Public Sub PopulateCombo(sCtl As ComboBox, sTable As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset

   On Error GoTo PopulateCombo_Error

Set db = CurrentDb()
Set rst = db.OpenRecordset(sTable)

rst.MoveFirst
Do While Not rst.EOF
sCtl.AddItem Item:=rst(0).Value   'the table has only one column
rst.MoveNext
Loop

rst.Close
Set db = Nothing

   On Error GoTo 0
..............................

 

[David R]

Unless this is purely for practice, you are killing a fly with a sledgehammer. You can populate a combo with typing directly in properties, or a reference table, or a Totals Query, or... practically ANYTHING is easier than doing it with VBA.

 

[GregoireLeGros]

Why you need VBA in order to populate the combo ?

Sometimes you need to edit the available values in the combo box. If you have lots of combo boxes where the values are held in a table on another database back-end for instance. You would have lots of linked tables which is untidy.

 

[GregoireLeGros]

Using Filter By Form where the combo box values are held in a linked table can produce unpredictable results. Loading the values at form OnLoad means that there are less linked tables. I have a form with 90 fields of which 25 are combo box dropdown lists. I have more control this way and anyway VBA is a lot of fun ! Sometimes.