Solved

Pass control to function

Posted on 2012-03-09
6
339 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
Comment Utility
this line
strBuildRptWhere = BuildStringLists(ctlPass, strField)
is actually
strBuildRptWhere = BuildStringLists(ctl.name, strField)
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
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)
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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
Comment Utility
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
Comment Utility
This worked like a charm. thank you very much.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

762 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

6 Experts available now in Live!

Get 1:1 Help Now