Improve company productivity with a Business Account.Sign Up

x
?
Solved

Pass control to function

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

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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

You can use the network upload option and the Office 365 Import service to bulk-import PST files to user mailboxes. Network upload means that you upload the PST files a temporary storage area in the Microsoft cloud.
The most emerging queries of the user is to upload Outlook OST mailboxes to Office 365 account. So, here we will provide the manual as well as an automated solution for to transfer OST file to MS Office 365 in an absolute manner.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

579 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