We help IT Professionals succeed at work.

Pass control to function

Medium Priority
380 Views
Last Modified: 2012-08-13
I have the following sub I am trying to use to build a where statement to open a report. First I loop through list boxes I have tagged with "Go". If the listbox, ctl, is visible then It means there is a selection. I then do a select case to assign it a string that I pass to my function to build the In statement. I keep getting an error when I try to pass ctl.name to my function. help please

For Each ctl In Me.Controls
    If ctl.Tag = "Go" Then
        If ctl.NAME.Visible Then
            Select Case ctl.NAME
                Case "lstCustomer"
                    strField = "Customer"
                Case "lstCarrier"
                    strField = "Carrier"
            End Select
        End If
    End If
    If strField <> "" Then
        strBuildRptWhere = BuildStringLists(ctlPass, strField)
    End If
Next

Open in new window


There is the function to build the In statement

Function BuildStringLists(ByVal ctlList As Control, ByVal strField As String) As String
Dim i As Integer
Dim strIn As String
'Build the IN string by looping through the listbox
For i = 0 To ctlList.ListCount - 1
If ctlList.Selected(i) Then
    strIn = strIn & "'" & ctlList.Column(0, i) & "',"
End If
Next i
If strIn <> "" Then
BuildStringLists = " AND " & strField & " in (" & Left(strIn, Len(strIn) - 1) & ")"
End If
End Function

Open in new window

Comment
Watch Question

Author

Commented:
this line
strBuildRptWhere = BuildStringLists(ctlPass, strField)
is actually
strBuildRptWhere = BuildStringLists(ctl.name, strField)
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Try this instead:

        strBuildRptWhere = BuildStringLists(ctl, strField)


It's expecting the control - not the "name" property of the control.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
I'm, a bit surprised that it is even executing that line of code.

Why are you using the Name propert of the control in this line?

        If ctl.NAME.Visible Then

I would think that you should be using:

        If ctl.Visible Then

But I agree with mbizup on the issue of calling the function.  You must pass it the Control and you never set the value of ctlPass, so you should be using "ctl"
CERTIFIED EXPERT
Top Expert 2009

Commented:
I agree with the last comment -- dump the NAME.  It is a control that is visible or not, not the name of a control.

Also, I suspect that this code could be considerably simplified.  Can you explain in words what you are trying to do here?  Can you post the database?

Author

Commented:
Thank you all for your input. Indeed this did work. but before I close this out I would like to hear more about what Helen is talking about. I could post the database but I use pass through queries for tables so it would not do a lot of good for you. what I am trying to do is this. I have a form with multiple list boxes. each list box has a check box. If the user checks the box then the list box becomes visible. and of course vise versa. I use the code above to build a where statement to open a report bases upon their selections.  I put a screen shot as an attachment. Top-Reports.pdf

Author

Commented:
This worked like a charm. thank you very much.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.