How do you hide columns in form based on combo box selection?

[Lynch225]

Hey everyone,

I have a form called Overflow based on a query called Trailers_Unloading_All. On my main menu form, there is a combo box called cboDest. It has two columns, DestNumber and DestName, based on a table called Unl_Seq_Dest where DestNumber is the bound column. The values in DestNumber are the column numbers in the Overflow form (as well as the Trailers_Unloading_All query). After making the selection, the user clicks on the View_Trailers button, which will open the Overflow form.

Is there a way to filter the Overflow form based on the selection in the combo box so that it hides every column but the columns called Door, Trailer_Number, and the value selected in the combo box? I'm not sure if this is possible, as I do not have much experience with VBA.

Hopefully someone is able to help, thanks!

-Ben

[sierra7]

Is there a way to filter the Overflow form based on the selection in the combo box so that it hides every column but the columns called Door, Trailer_Number, and the value selected in the combo box?

Normally, when you open a form after selecting a parameter from a combo-box, you filter the records to display only those that match the parameter. You would not normally Hide/Show different fields.

As you say you are not familiar with VB, then the simplest way to achieve what (I think) you are asking is to have two versions of the Overflow form; one with all the fields and one with just the three you mention. Just call Overflow or Overflow_Short as required.

S7

[Lynch225]

Thanks S7,

I guess my main issue is that I want the combo box to filter the columns of the form and not the records (usually the rows). Is there a better way to do this than a combo box?

[NeoPa]

For a form in standard form view, this could be done by creating controls for each possible field from your ComboBox which are all set as hidden. When a field is selected from the ComboBox all are again hidden but then the selected control is made visible and active.

If the form is in datasheet view then this approach wouldn't work and a more complicated one of changing the form's RecordSource would be required.

[sierra7]

The values in DestNumber are the column numbers in the Overflow form (as well as the Trailers_Unloading_All query). After making the selection, the user clicks on the View_Trailers button, which will open the Overflow form.

I read this a number of times this morning but mis-understood what you were asking.
If your main menu form (say frmMain) stays open while form Overflow is opened, then you could put something like this in the On_Open event of Overeflow;-

If Forms!frmMain!cboDest = 1 Then
   Me.txtCol1.Visible = False
ElseIf Forms!frmMain!cboDest = 2 Then
   Me.txtCol1.Visible = False
   Me.txtCol2.Visible = False
ElseIf Forms!frmMain!cboDest = 3 Then
   Me.txtCol1.Visible = False
   Me.txtCol2.Visible = False
   Me.txtCol3.Visible = False
..
..
End If
 

where txtCol1, txtCol2 & txtCol3 are the names of the controls(columns) to be hidden. You could write this as a Case statement if you prefer.

If your main menu closes when Overflow is opened then you would have to pass the value of cboDest as an OpenArgs parameter in the code under the View_Trailers button.

Private Sub View_Trailers_Click()
On Error GoTo Err_View_Trailers_Click
 
    Dim stDocName As String
    Dim stOpen As String
 
    stOpen = Me!cboDest
 
    stDocName = "Overflow"
    DoCmd.OpenForm stDocName, , , , , , stOpen
...
 

Then the On_Open event of Overflow would be as follows (written as a Case statement this time, a bit tidier!)

Private Sub Form_Open(Cancel As Integer)
Select Case OpenArgs
  Case 1   
    Me.txtCol1.Visible = False
  Case 2
     Me.txtCol1.Visible = False
     Me.txtCol2.Visible = False
  Case 3 
     Me.txtCol1.Visible = False
     Me.txtCol2.Visible = False
     Me.txtCol3.Visible = False
  Case Else
  'Nothing
End Select
 

S7

[Lynch225]

Thank you guys, I haven't been able to try this yet (work has been pretty busy), but I feel like this well point me in the right direction.

[NeoPa]

@S7 - I'm not sure I follow your code logic. There appears to be no lines where any control is set to .Visible = True. I'm also curious why the number of controls effected after different selections seems to change. Maybe I'm misunderstanding something.

As an alternative to the OpenArgs scenario though, the calling code has access to the called form and its properties once control has returned from the DoCmd.OpenForm() call, so the code to make the changes could stay in the originating module and run before the form allows itself to close. Not that using OpenArgs wouldn't also work, but it just makes the coding a little more complex (Handling OpenArgs properly and splitting the code between two places).

[sierra7]

@NeoPa,
Yep, I don't understand why Mr Lynch wants to hide different columns depending upon a selection in a combo either, but I'm sure he has good reason. I was uncertain first time round, which was why my first post talked about records and fields.

One possibility is that he is viewing in DataSheet mode and wants the visible columns to close-up. i.e if his query returned 12 colums but his form was only wide enough to display 4 he could then perm any 4 from 12. But if that was the case he would have to use the ColumnHidden property, not the Visible property, which seems to have no effect in Datasheet mode (setting the Width is also ignored)e.g.

Private Sub Form_Open(Cancel As Integer) 
Select Case OpenArgs 
  Case 1    
    Me.txtCol1.ColumnHidden = True 
  Case 2 
     Me.txtCol1.ColumnHidden = True 
     Me.txtCol2.ColumnHidden = True 
  Case 3  
     Me.txtCol1.ColumnHidden = True 
     Me.txtCol2.ColumnHidden = True 
     Me.txtCol3.ColumnHidden = True 
  Case Else 
  'Nothing 
End Select 
 

He would not need to set Visible = True (or ColumnHidden = False) in the code, if the default state of the columns was Visible = True (not hidden). If the users wants to see other columns they would have to close and then re-open the form having made the appropriate selection in the combo-box.

Anyway, Mr Lynch seems happy he has a solution!
S7

[Lynch225]

Yeah it seems as though the more you guys talk back and forth, the more it seems you are understanding what I am looking for. S7 you are on the right track with the number of controls. I have about 50 controls, and my form (as of now) is in Datasheet mode. I want to tidy it up a bit and keep my form to 4 columns- 3 that are always present and the 4th that is visible with the selection from the combo box.

And NeoPa, you are right in that the number of controls affected after each selection will not change. Maybe I'm a bit over my head in creating this database, but I can't thank you guys enough for your time in helping me out!

[Lynch225]

Maybe I'm trying to do too much and I should just open up the Trailers_Unloading_All query when I click the View_Trailers button and filter the records based on the combo box that way...

[NeoPa]

Why would you feel you may be doing too much if the code's doing what you require (or at least a modified version that suits you is)? I think we both got the impression all was working for you now.

@S7. Ultimately you're right. My understanding was that your code didn't quite match the question, but frankly, if the OP is happy with it then who am I to question it. They may well feel that your code illustrated the way clearly enough for them to 'take it from there' as it were. My suggestion wasn't vastly different from your code anyway (It simply enabled only one column for whichever field was selected from the ComboBox), and I knew nothing of the ColumnHidden property for Datasheet mode (so thanks for that tip).

[sierra7]

@NeoPa.

I knew nothing of the ColumnHidden property for Datasheet mode (so thanks for that tip).

Frankly neither did I, but when I tried Visible = False in normal Continuous Form mode in a test database,the result was that the columns were still there but void of data.

I then converted the form to DataSheet mode (something I don't think I have ever used) so imagine my surprise when the columns were still visible.

I then thought I would be clever and set the 'width' to zero but again this was ignored. The air was getting blue by that time. I then had to re-phrase quite a few questions to MSDN before I came across the ColumnHidden property. This fitted the bill exactly in the context of what Mr Lynch is trying to do.

@Lynch225. Ben I am still as confused as Neopa about aspects of this. When I first read your query I registerted 'Trailers' and 'Destination' and my brain automatically assumed that the aptly named cboDest combobox was going to filter all Trailers for a certain destination. However, your description then went on to require hiding columns, hence the development of the proposed solution.

But your last post has now set alarm bells ringing! You have 50 fields but you want to show 3 fixed and one variable (?) Are the 47 (variable) different destinations?? Do we have a situation of 'repeat fields'. I have a strange feeling that they are, in which case you should consider normalising your data structure. Do you understand any ofthese terms ?

S7

[NeoPa]

Good for you S7. They're exactly the sort of steps I too would have fallen over to get where you got to, so I'm even more grateful now that you saved me the trouble :-D

[Lynch225]

@NeoPa- I haven't physically tried the code itself, but based on what you guys have posted, it makes sense why it would work. As soon as I can, I will try this out and make sure it works.

@S7, you are right in that there are 50 different destinations. In order to make the form easier to read, I want the destination chosen by the user to be the only visible destination. The combo box will essentially filter the boxes going to each destination for all trailers. There are also 2 other fields that I want present at all times. Hopefully this clears things up at least a little bit.

[sierra7]

My mind is still boggling! So you want to hide 48 of the 50 column and just reveal an extra column depending upon the selection of Destination.

The code I have proposed will do this (except that the columns must be set to hidden in design mode and made visible as required, converse to the what was shown), plus the Case statement will have 50 or so stages (!?!) unless I have it totally wrong.

I really feel that the database design must be wrong here. What happens if you want a 51st destination?

Neopa, perhaps you could add a link to you article on Database Normalisation?
S7

[NeoPa]

More than happy to S7 (Database Normalisation and Table Structures), though it's Mary's (MMcCarthy's) article. I just recognise the value of it ;-)

Actually, while the S7 code is fundamentally OK (particularly for what S7 was trying to do), it doesn't quite fit the requirement as far as I see it.

My initial reading of the question overlooked the request to pass the selection of the control from the main form into [Overflow]. Hence post #3 suggested the need to reset all unused controls as the current state was not determined. I believe it makes more sense for such a selection to be on the same form personally, but it is still possible to handle it across two if necessary.

Code to handle taking the value (This would be the name of the control specifically - not the name of the field) and ensuring just that control extra were shown, is not too complicated, but the reference to Me.OpenArgs must happen early as it is only available in the Form_Open() event procedure :

Private Sub Form_Open()
    With Me
        If Not IsNull(.OpenArgs) Then .Controls(.OpenArgs).Visible = True
    End With
End Sub

PS. The code to open [Overflow] would need to be like :

Call DoCmd.OpenForm(FormName:="Overflow", OpenArgs:=Me.cboDest)

[Lynch225]

First, I did want to say that the Database Normalization article was a very good and informative article- thank you!

I am having problems with the form in datasheet view. I set the properties of the controls to hidden in design view (as per S7) but the controls are still visible. Also, when I open the form, I get a Runtime 2645 error - Microsoft Access can't find the field '5' referred to in your expression. It goes to the following section:

Private Sub Form_Open()
     With Me
         If Not IsNull(.OpenArgs) Then .Controls(.OpenArgs).Visible = True
     End With
End Sub

As has been discussed throughout the thread, my design may be flawed, as there may be a more efficient way of doing what I need.

[NeoPa]

Ben:
Runtime 2645 error - Microsoft Access can't find the field '5' referred to in your expression

It sounds like that must be line #3 where the error occurred (Always helpful to include that info). The value 5 reflects what you passed to the form as OpenArgs. Having a value of 5 indicates to me that you passed the wrong information. The value passed should be the name of the control. This would lead me to believe that something is wrong with your ComboBox control where you select the value from - possibly the wrong column is bound or maybe even the data populating the control is wrong.

Ben:
I am having problems with the form in datasheet view.

If you are using datasheet view then you should have said much earlier. The point was brought up, but you never responded (until now) to indicate this is what you were doing. Strangely enough, knowing what your question really is can help us to tailor our answers so much more directly.

As I say, this was discussed in posts #8 onwards, and the upshot is that these controls must use the .ColumnHidden property instead of the .Visible one. This is different because I know of no way to set this manually. Only with code. Code would need to use your understanding of which controls are effected. We don't have anything yet to work with on that score but a basic set of code that treats all TextBox controls on the form would be :

Dim ctl As Control
 
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        'Code to handle your controls here
    End If
Next ctl

[Lynch225]

Yeah NeoPa, I guess I had a brain fart with the bound column. For some reason I forgot that the primary key was hidden, so I am not getting that error any more. It seems that I can get any column to hide using the tips that you guys have given me. Now I'm stuck on how to clean the code up so I don't have to write 50 different cases for each selection in the combo box. This is what I have so far:

Private Sub Form_Open(Cancel As Integer)
 
Dim ctl As Control
Dim cboSelection As Control
 
cboSelection = "ctl" & Forms!Main_Menu!DestMenu
 
With Me
    If Not IsNull(.OpenArgs) Then Controls(.OpenArgs).Visible = True
End With
 
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        Me.Door.ColumnHidden = False
        Me.DestNumber.ColumnHidden = False
        Me.DestName.ColumnHidden = False
        Me.Trailer_Number.ColumnHidden = False
        Me.cboSelection.ColumnHidden = True
    End If
Next ctl
 
End Sub
 

Obviously this isn't working (Compile error with line 18- cboSelection is highlighted), but I used it as an example so hopefully you guys can see what I am looking for. I don't know if there is a way to declare a control in the way I showed with cboSelection or not.

I should also note that I would also set all of the 50 controls to hidden but I really didn't want to add all of that code on this page.

[Lynch225]

Oops, meant to post this code:

Private Sub Form_Open(Cancel As Integer)
 
Dim ctl As Control
 
With Me
    If Not IsNull(.OpenArgs) Then Controls(.OpenArgs).Visible = True
End With
 
On Error Resume Next
For Each ctl In Me.Controls
ctl.ColumnHidden = True
Next ctl
 
Me.Door.ColumnHidden = False
Me.DestNumber.ColumnHidden = False
Me.DestName.ColumnHidden = False
Me.Trailer_Number.ColumnHidden = False
 
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        Select Case OpenArgs
            Case 61
            If Forms!Main_Menu!DestMenu = 61 Then
            Me.Ctl61.ColumnHidden = False
            End If
            ... 'Multiple Cases'
        End Select
    Else: 'Nothing
    End If
Next ctl
 
End Sub
 

[NeoPa]

Ben, I'm struggling to see how your two most-recent posts continue the conversation. They just seem like statements out of the blue, with no reference made to anything that went before. I really don't want to have to try to reverse engineer where you think you're going with this. Without reference points it's so much harder work.

Do you have a continuation of the question or is this a new question (If the former, then it would help if your posts made that clear)?

[Lynch225]

Yeah NeoPa, I should have clarified that my last 2 posts were related to post #14.

So you want to hide 48 of the 50 column and just reveal an extra column depending upon the selection of Destination.

The code I have proposed will do this (except that the columns must be set to hidden in design mode and made visible as required, converse to the what was shown), plus the Case statement will have 50 or so stages (!?!) unless I have it totally wrong.

At the same time, I spent the 5 minutes and wrote out the case statements and it is working exactly like I had hoped. You're right in that I probably should have posted that last part as a new question since you have both answered my question. Thank you very much for all of your input!