By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

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

P: 43
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
Jan 11 '12 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2.  
  3. For Each ctl In Me.Controls
  4.     If ctl.ControlType = acTextBox Then
  5.         'Code to handle your controls here
  6.     End If
  7. Next ctl

Share this Question
Share on Google+
21 Replies


Expert 100+
P: 446
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
Jan 12 '12 #2

P: 43
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?
Jan 12 '12 #3

NeoPa
Expert Mod 15k+
P: 31,263
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.
Jan 12 '12 #4

Expert 100+
P: 446
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;-
Expand|Select|Wrap|Line Numbers
  1. If Forms!frmMain!cboDest = 1 Then
  2.    Me.txtCol1.Visible = False
  3. ElseIf Forms!frmMain!cboDest = 2 Then
  4.    Me.txtCol1.Visible = False
  5.    Me.txtCol2.Visible = False
  6. ElseIf Forms!frmMain!cboDest = 3 Then
  7.    Me.txtCol1.Visible = False
  8.    Me.txtCol2.Visible = False
  9.    Me.txtCol3.Visible = False
  10. ..
  11. ..
  12. End If
  13.  
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub View_Trailers_Click()
  2. On Error GoTo Err_View_Trailers_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stOpen As String
  6.  
  7.     stOpen = Me!cboDest
  8.  
  9.     stDocName = "Overflow"
  10.     DoCmd.OpenForm stDocName, , , , , , stOpen
  11. ...
  12.  
Then the On_Open event of Overflow would be as follows (written as a Case statement this time, a bit tidier!)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Select Case OpenArgs
  3.   Case 1   
  4.     Me.txtCol1.Visible = False
  5.   Case 2
  6.      Me.txtCol1.Visible = False
  7.      Me.txtCol2.Visible = False
  8.   Case 3 
  9.      Me.txtCol1.Visible = False
  10.      Me.txtCol2.Visible = False
  11.      Me.txtCol3.Visible = False
  12.   Case Else
  13.   'Nothing
  14. End Select
  15.  
S7
Jan 12 '12 #5

P: 43
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.
Jan 13 '12 #6

NeoPa
Expert Mod 15k+
P: 31,263
@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).
Jan 13 '12 #7

Expert 100+
P: 446
@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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer) 
  2. Select Case OpenArgs 
  3.   Case 1    
  4.     Me.txtCol1.ColumnHidden = True 
  5.   Case 2 
  6.      Me.txtCol1.ColumnHidden = True 
  7.      Me.txtCol2.ColumnHidden = True 
  8.   Case 3  
  9.      Me.txtCol1.ColumnHidden = True 
  10.      Me.txtCol2.ColumnHidden = True 
  11.      Me.txtCol3.ColumnHidden = True 
  12.   Case Else 
  13.   'Nothing 
  14. End Select 
  15.  
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
Jan 14 '12 #8

P: 43
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!
Jan 15 '12 #9

P: 43
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...
Jan 15 '12 #10

NeoPa
Expert Mod 15k+
P: 31,263
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).
Jan 15 '12 #11

Expert 100+
P: 446
@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
Jan 16 '12 #12

NeoPa
Expert Mod 15k+
P: 31,263
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
Jan 16 '12 #13

P: 43
@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.
Jan 17 '12 #14

Expert 100+
P: 446
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
Jan 17 '12 #15

NeoPa
Expert Mod 15k+
P: 31,263
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open()
  2.     With Me
  3.         If Not IsNull(.OpenArgs) Then .Controls(.OpenArgs).Visible = True
  4.     End With
  5. End Sub
PS. The code to open [Overflow] would need to be like :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.OpenForm(FormName:="Overflow", OpenArgs:=Me.cboDest)
Jan 18 '12 #16

P: 43
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open()
  2.      With Me
  3.          If Not IsNull(.OpenArgs) Then .Controls(.OpenArgs).Visible = True
  4.      End With
  5. 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.
Jan 18 '12 #17

NeoPa
Expert Mod 15k+
P: 31,263
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2.  
  3. For Each ctl In Me.Controls
  4.     If ctl.ControlType = acTextBox Then
  5.         'Code to handle your controls here
  6.     End If
  7. Next ctl
Jan 18 '12 #18

P: 43
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. Dim ctl As Control
  4. Dim cboSelection As Control
  5.  
  6. cboSelection = "ctl" & Forms!Main_Menu!DestMenu
  7.  
  8. With Me
  9.     If Not IsNull(.OpenArgs) Then Controls(.OpenArgs).Visible = True
  10. End With
  11.  
  12. For Each ctl In Me.Controls
  13.     If ctl.ControlType = acTextBox Then
  14.         Me.Door.ColumnHidden = False
  15.         Me.DestNumber.ColumnHidden = False
  16.         Me.DestName.ColumnHidden = False
  17.         Me.Trailer_Number.ColumnHidden = False
  18.         Me.cboSelection.ColumnHidden = True
  19.     End If
  20. Next ctl
  21.  
  22. End Sub
  23.  
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.
Jan 20 '12 #19

P: 43
Oops, meant to post this code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. Dim ctl As Control
  4.  
  5. With Me
  6.     If Not IsNull(.OpenArgs) Then Controls(.OpenArgs).Visible = True
  7. End With
  8.  
  9. On Error Resume Next
  10. For Each ctl In Me.Controls
  11. ctl.ColumnHidden = True
  12. Next ctl
  13.  
  14. Me.Door.ColumnHidden = False
  15. Me.DestNumber.ColumnHidden = False
  16. Me.DestName.ColumnHidden = False
  17. Me.Trailer_Number.ColumnHidden = False
  18.  
  19. For Each ctl In Me.Controls
  20.     If ctl.ControlType = acTextBox Then
  21.         Select Case OpenArgs
  22.             Case 61
  23.             If Forms!Main_Menu!DestMenu = 61 Then
  24.             Me.Ctl61.ColumnHidden = False
  25.             End If
  26.             ... 'Multiple Cases'
  27.         End Select
  28.     Else: 'Nothing
  29.     End If
  30. Next ctl
  31.  
  32. End Sub
  33.  
Jan 20 '12 #20

NeoPa
Expert Mod 15k+
P: 31,263
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)?
Jan 23 '12 #21

P: 43
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!
Jan 25 '12 #22

Post your reply

Sign in to post your reply or Sign up for a free account.