Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pass control to function

Posted on 2012-03-09
6
Medium Priority
?
360 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

0
Comment
Question by:JArndt42
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Author Comment

by:JArndt42
ID: 37703782
this line
strBuildRptWhere = BuildStringLists(ctlPass, strField)
is actually
strBuildRptWhere = BuildStringLists(ctl.name, strField)
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37703798
Try this instead:

        strBuildRptWhere = BuildStringLists(ctl, strField)


It's expecting the control - not the "name" property of the control.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37704784
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"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 37705045
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?
0
 
LVL 1

Author Comment

by:JArndt42
ID: 37710922
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
0
 
LVL 1

Author Closing Comment

by:JArndt42
ID: 37715114
This worked like a charm. thank you very much.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question