Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Storing control name in variable and changing properties

    Hi,

    In my db I have some reports, which I want to add filters to. I have created a form, which contains a list box populated from a table with the list of reports. In the form header I have various controls to act as filters for my reports.

    The filter controls have all been set to 'visible = false', and when the user clicks on a report I want the corresponding filters to show in the form header.

    My theory is if I add the control names for each filter in my table for each report, I can check the table and find the report and its associated filters.
    Except I'm having problems assigning the visible property to each control. I have placed the controls name in a variable set up as a control, but can't change the visible property.

    Can anyone help?

    Code:
    Private Sub listRpt_Click()
    Dim adoConn As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim rsADO As ADODB.Recordset
    Dim sSQL As String
    Dim Filter1, Filter2, Filter3, Filter4 As Control
     
    Set adoConn = CurrentProject.Connection
    Set adoCmd = New ADODB.Command
    sSQL = " SELECT * FROM tblReports" & _
           " WHERE RptName ='" & Me.listRpt.Value & "'"
     
        With adoCmd
            .ActiveConnection = adoConn
            .CommandType = adCmdText
            .CommandText = sSQL
            Set rsADO = .Execute
        End With
     
        Do While Not rsADO.EOF
            If Not IsNull(rsADO![Filter1]) Then
                Filter1 = rsADO![Filter1]
                Filter1.Visible = True
            End If
            If Not IsNull(rsADO![Filter2]) Then
                Filter2 = rsADO![Filter2]
                Filter2.Visible = True
            End If
            If Not IsNull(rsADO![Filter3]) Then
                Filter3 = rsADO![Filter3]
                Filter3.Visible = True
            End If
            If Not IsNull(rsADO![Filter4]) Then
                Filter4 = rsADO![Filter4]
                Filter4.Visible = True
            End If
            rsADO.MoveNext
        Loop
     
    rsADO.Close
    Set rsADO = Nothing
    End Sub
    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    So, you have a variable named filter1, and there is also a control on your form named filter1? I know I'd be confused about what you'd want to be visible...
    Inspiration Through Fermentation

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Control.Visible =

    Hi

    I am as confuses as RedNeckGeek.

    You do actually say what happens, I assume nothing !?

    Assume that Filter1 , Filter2 etc are contols on the same form as listRpt

    If so, then you do not need to declare control varables because they already exist!?

    I think you need to refer to the controls NAME property in the comarison statement, as below

    Code:
    Private Sub listRpt_Click()
    Dim adoConn As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim rsADO As ADODB.Recordset
    Dim sSQL As String
     
    Set adoConn = CurrentProject.Connection
    Set adoCmd = New ADODB.Command
    sSQL = " SELECT * FROM tblReports" & _
           " WHERE RptName ='" & Me.listRpt.Value & "'"
     
        With adoCmd
            .ActiveConnection = adoConn
            .CommandType = adCmdText
            .CommandText = sSQL
            Set rsADO = .Execute
        End With
     
        Do While Not rsADO.EOF
            If Not IsNull(rsADO![Filter1]) Then
               if Filter1.NAME = rsADO![Filter1] then 
                   Filter1.Visible = True
               else
                   Filter1.Visible = False
               end if
            End If
    
            If Not IsNull(rsADO![Filter2]) Then
                if Filter2.NAME = rsADO![Filter2] then 
                    Filter2.Visible = True
                Else
                   Filter2.Visible = False
                end IF
            End If
            If Not IsNull(rsADO![Filter3]) Then
                if Filter3.NAME = rsADO![Filter3] then 
                    Filter3.Visible = True
                Else
                   Filter3.Visible = False
                end IF
            End If
            If Not IsNull(rsADO![Filter4]) Then
                if Filter4.NAME = rsADO![Filter4] then 
                    Filter4.Visible = True
                Else
                   Filter4.Visible = False
                end IF
            End If
            rsADO.MoveNext
        Loop
     
    rsADO.Close
    Set rsADO = Nothing
    End Sub
    Is that any thing like the problem ??

    I assume that the Report recoerds in the report table as text ??

    MTB

  4. #4
    Join Date
    Sep 2005
    Posts
    240
    OK, I'll see if I can explain it clearer.

    listRpt is the name of a listbox which lists all of the report names.

    This listbox is populated from a table called 'tblReports', which contains information similar to the following:-

    ID - ReportName - ReportDescription - Filter1 - Filter2 - Filter3 - Filter4

    1 - rptShowAll - Show all records in table - txtTown - Null - txtCountry - Null

    Filter1 - 4 are fields in 'tblReports' and contain the name of the control on the form, which corresponds to the filters that can be used for that report.

    In the form when you click on an item in the listbox 'listRpt', I want it to make the controls as named in each of the Filter fields in tblReports, to visible = true.

    With my code it displays run-time error '424': Object required.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Then the value stored in the filter1 field is the name of a control on your form?

    strFldName=rsADO![Filter1]
    Forms!("Formname")(strFldName).Visible=True
    Inspiration Through Fermentation

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Do you really need to open a recordset?

    I mean, column 4-7 in the listbox contains the filter setting, don't they? Air code
    Code:
    dim lngCounter as long
    for lngCounter = 3 to 6 ' zero based
        if len(Me!listRpt.column(lngCounter)) then
            me.controls(Me!listRpt.column(lngCounter)).visible = true
        end if
    next lngCounter
    Edit: I'm thinking, prior to this, you should probably have some looping of all the possible filter controls, setting their visibility state to false?
    Last edited by RoyVidar; 01-15-07 at 11:53. Reason: Adding a comment
    Roy-Vidar

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    You do not say on which line the code fails, but I suspect, as suggested before, that you are trying to equate a string to an object, as in

    Filter1 = rsADO![Filter1] ??

    I would now suggest that the list box is based on a query that includes the filter control Names ie SELECT ID, ReportName, Filter1Field, Filter2Field, Filter3Field, Filter4Field FROM tblReports WHERE whatever

    Columns 0,2,3,4,5 set to zero width.

    Then your procedure would be something like
    Code:
    Private Sub listRpt_Click()
    
    With listRpt
        If Not IsNull(.Column(2)) Then
              Me.Controls(.Column(2)).Visilble=true
        End If
    
        If Not IsNull(.Column(3)) Then
             Me.Controls(.Column(3)).Visilble=true 
        End If
    
        If Not IsNull(.Column(4)) Then
             Me.Controls(.Column(4)).Visilble=true
        End If
        If Not IsNull(.Column(5)) Then
             Me.Controls(.Column(5)).Visilble=true
        End If
    End With
    
    End Sub
    Needless to say you table names must match the control names exactly.

    The only think I don’t like is if different reports have different filter controls and the user selects several reports one after the other, then the visible properties is not reset.

    Suggest setting all filter controls to visible = false at the start of above procedure if this can occur.

    ??

    MTB

    End Sub

    Needless to say you table names must match the control names exactly.

    The only think I don’t like is if different reports have different filter controls and the user selects several reports one after the other, then the visible properties is not reset.

    Suggest setting all filter controls to visible = false at the start of above procedure if this can occur.

    ??

    MTB
    Last edited by MikeTheBike; 01-15-07 at 11:44.

  8. #8
    Join Date
    Sep 2005
    Posts
    240
    Thanks for the help everyone, appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •