If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

Control.Visible =

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

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.