UtterAccess Discussion Forums > Combo box control name being passed to a module

Full Version: Combo box control name being passed to a module

UtterAccess Discussion Forums > Microsoft® Access > Access Modules

jeinsel

Jan 28 2005, 07:40 PM

Hi again.

I'm putting together a application (if you can call it that) that is an Access frontend that accesses a SQL 2000 database. So far I'm using ADO to code all the forms. It seemed as though I had a combo box that would be populated by a stored procedure and this combo box existed on several forms so I wanted to save some repetitive code and just create a global sub in a module that would do this and just call that sub on the forms open event. I ran into problems. No matter what I do, the code craps out when it hits the name of the combo box. When I step through the code and mouse over the combo box name it says cboEmployee=Null

Here is what I have in the module:

CODE

Public Sub FillEmpCboBox(frm As Form, cbo As ComboBox)

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset

    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command

    cnn.Open EngCnn

    Set cmd.ActiveConnection = cnn
    cmd.CommandText = "proc_Get_Employees"
    cmd.CommandType = adCmdStoredProc

    Set rs = cmd.Execute()

    rs.MoveFirst

    While Not rs.EOF
        frm.cbo.AddItem rs!EmpID & ";" & rs!EmpName
        rs.MoveNext
    Wend

    Set cmd = Nothing
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing

End Sub

I'm calling this sub in the forms open event with this:

CODE

Call FillEmpCboBox(Form_frmReqForm_Engineering, cboEmployee)

frmReqForm_Engineering being the name of the form that has the combo box in it and cboEmployee being the name of the control.

Again, it's my limited knowledge that is really the culprit here.
Thanks so much for any help.

JeffK

Jan 31 2005, 12:50 PM

I recommend the following changes to your code:

Remove the form argument from the function
If you are passing in a reference to the control, there is no need to pass in a reference to its parent form.

This:
Public Sub FillEmpCboBox(frm As Form, cbo As ComboBox)

Should be:
Public Sub FillEmpCboBox(cbo As ComboBox)

Remove the frm from the fill line
Even if you passed in a form reference, the frm.cbo is not correct because the form does not have a cbo property.

This:
frm.cbo.AddItem rs!EmpID & ";" & rs!EmpName

Should be:
cbo.AddItem rs!EmpID & ";" & rs!EmpName

For future reference, pass in form references as Me, not the form's module name
When you pass a form reference, use the object variable that already applies to it. When you pass in the class name "Form_..." Access creates a new copy of the form that does not refer to the one you already have open.

This:
Call FillEmpCboBox(Form_frmReqForm_Engineering, cboEmployee)

Is more appropriately written as:
Call FillEmpCboBox(Me, cboEmployee)

But since you are removing the form argument from the function, should be:
Call FillEmpCboBox(Me.cboEmployee)

HTH
Jeff

jeinsel

Jan 31 2005, 02:05 PM

You probably already know this but... it worked like a charm.

Thanks so much!

JeffK

Jan 31 2005, 04:13 PM

I'm glad it worked out for you!
Happy to Help!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.