473,836 Members | 1,578 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

43 New Member
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!

Jan 11 '12
21 6361
32,584 Recognized Expert Moderator MVP
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
446 Recognized Expert Contributor
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 ?

Jan 16 '12 #12
32,584 Recognized Expert Moderator MVP
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
43 New Member
@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
446 Recognized Expert Contributor
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?
Jan 17 '12 #15
32,584 Recognized Expert Moderator MVP
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
43 New Member
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
32,584 Recognized Expert Moderator MVP
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.

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
  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
43 New Member
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)
  3. Dim ctl As Control
  4. Dim cboSelection As Control
  6. cboSelection = "ctl" & Forms!Main_Menu!DestMenu
  8. With Me
  9.     If Not IsNull(.OpenArgs) Then Controls(.OpenArgs).Visible = True
  10. End With
  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
  22. 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.
Jan 20 '12 #19
43 New Member
Oops, meant to post this code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  3. Dim ctl As Control
  5. With Me
  6.     If Not IsNull(.OpenArgs) Then Controls(.OpenArgs).Visible = True
  7. End With
  9. On Error Resume Next
  10. For Each ctl In Me.Controls
  11. ctl.ColumnHidden = True
  12. Next ctl
  14. Me.Door.ColumnHidden = False
  15. Me.DestNumber.ColumnHidden = False
  16. Me.DestName.ColumnHidden = False
  17. Me.Trailer_Number.ColumnHidden = False
  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
  32. End Sub
Jan 20 '12 #20

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

Similar topics

by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I have a primary form named TestResults, which is connected to data in a table named TestResults. There are basically two other tables that are related to the TestResults table (and the primary form) named Names-Normalized and SiteAddresses. The...
by: Midiman69 | last post by:
Can any one help with a problem I have with a tab control form I have a form with a two page tab control - page 1 being the main form. I have a data sheet view subform on page 2.(linked by "Partype" There is a part type combobox (from tblpartype) on page 1 and I wish to hide columns on the data sheet depending on what part type is displayed on page 1. I have tried this on the Partype after update event If Me!partype = "Resistor 0.25W...
by: ROO | last post by:
Hi Everyone, I have a database table that have 4 field( C1, C2, M1, M2) on my form i have two combo box ComboC and ComboM C1 C2 M1 M2 1 Sales 1 Monthly Terms 1 Sales 2 Annual Terms
by: Benaron | last post by:
I'm a javascript newbie - so although I can follow scripts and write simple ones, I get stuck when things don't work. I'm trying to have a help section on my web-site where each question/FAQ will be on a drop-down box (Option Select). Beneath this I've set a textarea and I wanted this to be populated with the answer based on the user selection. I've created an array of my answers in the header area and tried to get an ONCHANGE on the...
by: kennygee | last post by:
I am trying to learn VB after many years programming in Access. I am trying to do something in VB that is simple in Access. How do I display record data on a form based on the selection made in a combo box? My problem may lie in not understanding which event is triggered when a new value is selected in the combo. I am working on the basis that when the new value is selected, I can then use the new value to reposition the data in the forms...
by: imrosie | last post by:
Hellol, I'm again in need of your help with my Order processing system.. My Order form is based on (query) of Customer and Order tables (includes a subform for the product data)...has a listbox (values are check or credit card)...to store the payment type. What I'm looking for is a way to pop up a form based off either a credit card or a check selection. Either form would then store their data into the Payments table (used for report...
by: Sanjaylml | last post by:
I have made a form in Access 2000. In addition to just simply enter the data through form, I have appended sub-form in main form to show the all the entered records as Sub-Form (DataSheet), which is perfectly working. My problem is that now I want to show the sub-form based on my selection in Combo box field. Is it Possible to show the sub-form based on selection & if so, with Serial Number / Line Number of selected values in Sub-form...
by: Beatrice | last post by:
I need to open a form selecting all data from a previous form i.e: Form 1) combo box 1 named "cboYearSelect" displayed as "Year" based on qry QryYearList ( only one field "Year") combo box 2 named "cbowwselect" displayed as "wk" based on qry Qry WkList (field: Year, wk) displaying only wk field. OPEN FORM (2) BUTTON (@@@)
by: Patrick A | last post by:
All, I've got a continuous form with one field on it, with a comob
by: Jharp | last post by:
Ok, so im really new to access and programing in general, what i am doing is building a inventory(something simple), the way ive got it set up now is, my entire inventory is in a combo box, and what i want to be able to do is select an item from the combo box and have it open a window(form?). ok so i got this far, i have it Onclick open a newform, and that works fine but it returns everything in inventory, so what i need it to do is...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.