Solved

Hide Unhide Subform Columns

Posted on 2008-06-25
20
1,570 Views
Last Modified: 2013-11-28
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

Open in new window

0
Comment
Question by:dkintaudi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 3
  • +2
20 Comments
 
LVL 23

Expert Comment

by:irudyk
ID: 21868270
Try the following revised code
'hide all columns first
  For intCounter = 0 To NumFields
    Me.subformCertData.Form.Controls(lstFieldList.ItemData(intCounter)).ColumnHidden = True
  Next intCounter
 
'unhide the necessary columns
    For Each vItem In Me.lstFieldList.ItemsSelected
    Me.subformCertData.Form.Controls(lstFieldList.ItemData(intCounter)).ColumnHidden = Not (lstFieldList.Selected(intCounter))
  Next vItem

Open in new window

0
 
LVL 27

Expert Comment

by:jjafferr
ID: 21868281
Can you please explain your requirement a bit more, it is unclear to me.

thanks

jaffer
0
 
LVL 23

Expert Comment

by:irudyk
ID: 21868325
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 :)
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 75
ID: 21868334
" subform columns"

Is this subform in Datasheet View or Continuous View ?

mx
0
 

Author Comment

by:dkintaudi
ID: 21868491
The subform is in Continous View
0
 
LVL 75
ID: 21868518
The ColumnHidden  property only applies to Datasheet View.  You need to use the Visible property.

mx
0
 

Author Comment

by:dkintaudi
ID: 21868540
I tried it, and it didn't work.  
0
 

Author Comment

by:dkintaudi
ID: 21868567
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.  
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 21868594
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
0
 
LVL 75
ID: 21868643
And if you really want to hide columns, I would consider Datasheet View.

mx
0
 

Author Comment

by:dkintaudi
ID: 21868665
I've changed ti to Datasheet view, and its still not doing it.  
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 21868679
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 :)
0
 

Author Comment

by:dkintaudi
ID: 21868742
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.  
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 21868765
can you please upload a sample mdb, so that we can give the right solution.

jaffer
0
 

Author Comment

by:dkintaudi
ID: 21869845
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
0
 

Author Comment

by:dkintaudi
ID: 21869849
Hope that helps jafferr:
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21870459
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.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

'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.Controls(.ItemData(x)).ColumnHidden = True
    Next
    For j = 0 To UBound(ctlArr)
    Me.subformCertData.Form.Controls(ctlArr(j)).ColumnHidden = 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]![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
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 21870801
and another thing , you are missing a field in your subform { txtReportNum } you have to add this to the subform or delete it from the table or you will get an error.
0
 

Author Comment

by:dkintaudi
ID: 21870895
Thanks very much capricorn1.  
0
 

Author Closing Comment

by:dkintaudi
ID: 31470677
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.  
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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