Solved

Pass control to function

Posted on 2012-03-09
6
341 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 500 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 47

Expert Comment

by:Dale Fye (Access MVP)
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now