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_Unload ing_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_Unload ing_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_Unload ing_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(column s) 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 misunderstandin g 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_Unload ing_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