dkintaudi
asked on
Hide Unhide Subform Columns
I'm trying to create a function that hides and unhides subform columns through the use of a list box. It is working incorrectly as it is hiding and unhiding the wrong columns. This is a challenge question as I've asked the question before in a seperate forum and no one has answered it. I'm hoping it has a better chance here. Thanks.
Public Function SortSubform()
On Error GoTo Err_Handler
Dim qDef As Object
Dim strSql As String
Dim vItem As Variant
Dim NumFields As Integer
Dim intCounter As Integer
NumFields = 0
' loop through selected field names
For Each vItem In Me.lstFieldList.ItemsSelected
strSql = strSql & ",[" & Me.lstFieldList.ItemData(vItem) & "]"
NumFields = NumFields + 1
Next vItem
For intCounter = 0 To NumFields
Me.subformCertData.Form.Controls(lstFieldList.ItemData(intCounter)).ColumnHidden = True
For Each vItem In Me.lstFieldList.ItemsSelected
Me.subformCertData.Form.Controls(lstFieldList.ItemData(intCounter)).ColumnHidden = Not (lstFieldList.Selected(intCounter))
Next vItem
Next intCounter
' build new StrSQL statement
strSql = "Select * " & _
"FROM tblCertificationData"
' add criteria for selected fields.
Strfield = "ReceivedDate"
strSql = strSql & " WHERE ((([ProcurementNumber]) Like [Forms]![frmSearch]![txtProcurementNumber]) And (([ReceivedDate]) Between [Forms]![frmSearch]![txtStartDate] And [Forms]![frmSearch]![txtEndDate]) AND (Isnull(PurchaseOrderNum) or ([PurchaseOrderNum]) Like [Forms]![frmSearch]![PONum]) And (([ReceivedBy]) Like [Forms]![frmSearch]![cboAnalystID]) AND (([EquipmentType]) Like [Forms]![frmSearch]![cboEquipmentType]) AND (([DistrictNumber]) Like [Forms]![frmSearch]![cboDistrict]))" & _
" Order by [ProcurementNumber];"
' save query with new StrSQL statement
Set qDef = CurrentDb.QueryDefs("qryProjectCriteria")
qDef.SQL = strSql
Set qDef = Nothing
Me.subformCertData.Form.RecordSource = strSql
' Set Recordsource of Subform
'Me.SearchLst.ColumnCount = NumFields
Exit_Err_Handler:
Exit Function
Err_Handler:
MsgBox Err.Description
Resume Exit_Err_Handler
End Function
Can you please explain your requirement a bit more, it is unclear to me.
thanks
jaffer
thanks
jaffer
Well, it looked to me that in your code you're looping through each field to hide it, but then you run another loop to hide/display all selected items in the listbox. By using the revised code, you would first hide all coulmns and then loop to display all of selected items in the listbox. There are other ways to accomplish this, but I thought I'd use the code you had and just reoreder it a bit. Give it a try to see if it works...if it doesn't you can always change it back :)
" subform columns"
Is this subform in Datasheet View or Continuous View ?
mx
Is this subform in Datasheet View or Continuous View ?
mx
ASKER
The subform is in Continous View
The ColumnHidden property only applies to Datasheet View. You need to use the Visible property.
mx
mx
ASKER
I tried it, and it didn't work.
ASKER
irudyk is there any solutions you could think of to hide unhide columns that might work better? At this point I'm open to suggestions.
the best way to do it is to Re-Arrange the fields:
Although you can set the field property to hidden (OR set its width to Zero), but that will leave an empty space in place of the hidden field (which is a column here),
so you will have to move the other columns to fill this space, thus Re-Arrange the fileds (columns).
jaffer
Although you can set the field property to hidden (OR set its width to Zero), but that will leave an empty space in place of the hidden field (which is a column here),
so you will have to move the other columns to fill this space, thus Re-Arrange the fileds (columns).
jaffer
And if you really want to hide columns, I would consider Datasheet View.
mx
mx
ASKER
I've changed ti to Datasheet view, and its still not doing it.
I agree with mx,
that would be your easiest way out, which would take you a minute to do,
and irudyk already gave you the code to hide/unhide.
jaffer
way to go mx :)
that would be your easiest way out, which would take you a minute to do,
and irudyk already gave you the code to hide/unhide.
jaffer
way to go mx :)
ASKER
Its still not hiding and unhiding the proper columns could something be preventing it from hiding and unhiding the right columns? I'm selecting the items I want from a list box, the sub form is in data sheet view, and then I click on the list box to hide and unhide the columns I want. The only problem is that it is hiding and unhiding the wrong columns.
can you please upload a sample mdb, so that we can give the right solution.
jaffer
jaffer
ASKER
The program consists of a front end and backend database. The password to get to the Administrative view of the front end database is Trial1. It must be typed in exactly as Trial1. It can be found in the tblPassword table where the user name is SysAdmin in the backend database 06-07 IT CertificationNew_be.mdb database located in the ITCertBE folder. Wow that was a mouthful lol.
The back end must be in the ITCertBE folder in order for it to work.
You won't be able to see the code unless you enter Trial1 as the password. Once the password has been entered, click the EnterAdminView button, find the frmSearch form and find the SortSubform function. That is the function I'm having trouble with.
Trial1.zip
The back end must be in the ITCertBE folder in order for it to work.
You won't be able to see the code unless you enter Trial1 as the password. Once the password has been entered, click the EnterAdminView button, find the frmSearch form and find the SortSubform function. That is the function I'm having trouble with.
Trial1.zip
ASKER
Hope that helps jafferr:
try this codes ' ** take note that i just corrected the codes for hide/unhide columns **
Public Function SortSubform()
On Error GoTo Err_Handler
Dim ctlArr(), j, x, i
Dim qDef As Object
Dim strSql As String
Dim vItem As Variant
Dim NumFields As Integer
Dim intCounter As Integer
NumFields = 0
' loop through selected field names
For Each vItem In Me.lstFieldList.ItemsSelec ted
strSql = strSql & ",[" & Me.lstFieldList.ItemData(v Item) & "]"
NumFields = NumFields + 1
Next vItem
' For intCounter = 0 To NumFields
' Me.subformCertData.Form.Co ntrols(lst FieldList. ItemData(i ntCounter) ).ColumnHi dden = True
' For Each vItem In Me.lstFieldList.ItemsSelec ted
' Me.subformCertData.Form.Co ntrols(lst FieldList. ItemData(i ntCounter) ).ColumnHi dden = Not (lstFieldList.Selected(int Counter))
' Next vItem
' Next intCounter
'hide/unhide codes
i = 0
With Me.lstFieldList
For j = 0 To .ListCount - 1
If .Selected(j) = True Then
ReDim Preserve ctlArr(i)
ctlArr(i) = .ItemData(j)
i = i + 1
End If
Next
End With
With Me.lstFieldList
For x = 0 To .ListCount - 1
Me.subformCertData.Form.Co ntrols(.It emData(x)) .ColumnHid den = True
Next
For j = 0 To UBound(ctlArr)
Me.subformCertData.Form.Co ntrols(ctl Arr(j)).Co lumnHidden = False
Next
End With
'end hide/unhide codes
' build new StrSQL statement
strSql = "Select * " & _
"FROM tblCertificationData"
' add criteria for selected fields.
Strfield = "ReceivedDate"
strSql = strSql & " WHERE ((([ProcurementNumber]) Like [Forms]![frmSearch]![txtPr ocurementN umber]) And (([ReceivedDate]) Between [Forms]![frmSearch]![txtSt artDate] And [Forms]![frmSearch]![txtEn dDate]) AND (Isnull(PurchaseOrderNum) or ([PurchaseOrderNum]) Like [Forms]![frmSearch]![PONum ]) And (([ReceivedBy]) Like [Forms]![frmSearch]![cboAn alystID]) AND (([EquipmentType]) Like [Forms]![frmSearch]![cboEq uipmentTyp e]) AND (([DistrictNumber]) Like [Forms]![frmSearch]![cboDi strict]))" & _
" Order by [ProcurementNumber];"
' save query with new StrSQL statement
Set qDef = CurrentDb.QueryDefs("qryPr ojectCrite ria")
qDef.SQL = strSql
Set qDef = Nothing
Me.subformCertData.Form.Re cordSource = strSql
' Set Recordsource of Subform
'Me.SearchLst.ColumnCount = NumFields
Exit_Err_Handler:
Exit Function
Err_Handler:
MsgBox Err.Description
Resume Exit_Err_Handler
End Function
Public Function SortSubform()
On Error GoTo Err_Handler
Dim ctlArr(), j, x, i
Dim qDef As Object
Dim strSql As String
Dim vItem As Variant
Dim NumFields As Integer
Dim intCounter As Integer
NumFields = 0
' loop through selected field names
For Each vItem In Me.lstFieldList.ItemsSelec
strSql = strSql & ",[" & Me.lstFieldList.ItemData(v
NumFields = NumFields + 1
Next vItem
' For intCounter = 0 To NumFields
' Me.subformCertData.Form.Co
' For Each vItem In Me.lstFieldList.ItemsSelec
' Me.subformCertData.Form.Co
' Next vItem
' Next intCounter
'hide/unhide codes
i = 0
With Me.lstFieldList
For j = 0 To .ListCount - 1
If .Selected(j) = True Then
ReDim Preserve ctlArr(i)
ctlArr(i) = .ItemData(j)
i = i + 1
End If
Next
End With
With Me.lstFieldList
For x = 0 To .ListCount - 1
Me.subformCertData.Form.Co
Next
For j = 0 To UBound(ctlArr)
Me.subformCertData.Form.Co
Next
End With
'end hide/unhide codes
' build new StrSQL statement
strSql = "Select * " & _
"FROM tblCertificationData"
' add criteria for selected fields.
Strfield = "ReceivedDate"
strSql = strSql & " WHERE ((([ProcurementNumber]) Like [Forms]![frmSearch]![txtPr
" Order by [ProcurementNumber];"
' save query with new StrSQL statement
Set qDef = CurrentDb.QueryDefs("qryPr
qDef.SQL = strSql
Set qDef = Nothing
Me.subformCertData.Form.Re
' Set Recordsource of Subform
'Me.SearchLst.ColumnCount = NumFields
Exit_Err_Handler:
Exit Function
Err_Handler:
MsgBox Err.Description
Resume Exit_Err_Handler
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much capricorn1.
ASKER
My real problem occurred cause I had not added the field to the subform and then I somehow got distracted and went on a tangent lol. Great help and timely.
Open in new window