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!

-Ben
Jan 11 '12 #1
21 6361
sierra7
446 Recognized Expert Contributor
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
Lynch225
43 New Member
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
32,584 Recognized Expert Moderator MVP
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
sierra7
446 Recognized Expert Contributor
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;-
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(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.
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
Lynch225
43 New Member
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
32,584 Recognized Expert Moderator MVP
@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).
Jan 13 '12 #7
sierra7
446 Recognized Expert Contributor
@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
Lynch225
43 New Member
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
Lynch225
43 New Member
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...
Jan 15 '12 #10

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

Similar topics

3
11117
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...
3
5729
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...
0
2141
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
1
2952
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...
2
1637
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...
7
3391
imrosie
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...
2
2308
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...
10
3077
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 (@@@)
9
4715
by: Patrick A | last post by:
All, I've got a continuous form with one field on it, with a comob
8
6760
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...
0
9813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9664
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,...
0
10832
Oralloy
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...
1
10584
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7782
isladogs
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...
0
6976
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();...
1
4446
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
2
4006
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3108
bsmnconsultancy
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.