Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.
Course Of The Month
2 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Hide / collapse columns in Form as needed
Posted on 2013-01-07
Hi
I am using Access 2010. This question is about a continuous form. The underlying table accepts movement of inventory from source to destination.
When the user opens the form, he/she will use an unbound combobox to choose whether the data entry will reflect items sold or items transferred. If the user chooses "transfer", then there is no need to enter information in some fields... ie [price], [discount] etc.
In such cases (user has chosen "transfer"), I would like the unwanted fields to disappear. That is easy enough. I also want to collapse the empty space so that the resultant form does not look like a patchwork quilt. It would be great if I could put these fields on the outermost right of the form, but this is not possible. I would like to emulate the way that Excel does it when you hide a column.
At this time, the best that I can come up with is to make them invisible, set their width to one pixel and move all the other columns next to the invisible column. I haven't tried this yet. It seems like a complex way to solve a simple problem.
I really would like to perform both tasks in one form. Is there a better way?
I am using Access 2010. This question is about a continuous form. The underlying table accepts movement of inventory from source to destination.
When the user opens the form, he/she will use an unbound combobox to choose whether the data entry will reflect items sold or items transferred. If the user chooses "transfer", then there is no need to enter information in some fields... ie [price], [discount] etc.
In such cases (user has chosen "transfer"), I would like the unwanted fields to disappear. That is easy enough. I also want to collapse the empty space so that the resultant form does not look like a patchwork quilt. It would be great if I could put these fields on the outermost right of the form, but this is not possible. I would like to emulate the way that Excel does it when you hide a column.
At this time, the best that I can come up with is to make them invisible, set their width to one pixel and move all the other columns next to the invisible column. I haven't tried this yet. It seems like a complex way to solve a simple problem.
I really would like to perform both tasks in one form. Is there a better way?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
2- +1
8 Comments
Active today
Is your continuous form in Datasheet view? If so, you can use the ColumnHidden method:
Me.Text0.ColumnHidden = True
Me.Text0.ColumnHidden = True
Active 1 day ago
Since you are in a continuous form, the problem will be that some records may say "Transfer" and others may reflect another value, so depending on what record you are on, you may want to suppress certain values while displaying those same values in the other records.
It is not possible to hide values in one record, but not in another in a continuous form. It is one or all.
It is not possible to hide values in one record, but not in another in a continuous form. It is one or all.
There will be no confusion in the record
Each record will have a field which will say "Transfer" or "Sale".
If the current record represents a transfer, then fields like [Price} and [Discount} do not apply.
The user will be entering this information in batches (~200).
I'm starting to think that the only way is to create two forms.
Each record will have a field which will say "Transfer" or "Sale".
If the current record represents a transfer, then fields like [Price} and [Discount} do not apply.
The user will be entering this information in batches (~200).
I'm starting to think that the only way is to create two forms.
Active 1 day ago
Have you tried the method described above:
1. Open the form in design mode
2. Select the [Price] and [Discount] controls
3. Click the Conditional Formatting button on the ribbon or menu option (depending on version).
4. Set the Condition1 combo box to "Expression Is", then set the expression like:
[FieldName] = "Transfer"
5. Then change the foreground and background colors so that they are the same as the detail section background color (or as close as you can get).
6. Click OK
7. Then select the [Price] control, select the "GotFocus" event from the Properties -> Event tab, and select [Event Procedure], and then go to that event in the VBA window.
8. In the GotFocus event, add some code to change the focus from that control to another control, one that can change.
1. Open the form in design mode
2. Select the [Price] and [Discount] controls
3. Click the Conditional Formatting button on the ribbon or menu option (depending on version).
4. Set the Condition1 combo box to "Expression Is", then set the expression like:
[FieldName] = "Transfer"
5. Then change the foreground and background colors so that they are the same as the detail section background color (or as close as you can get).
6. Click OK
7. Then select the [Price] control, select the "GotFocus" event from the Properties -> Event tab, and select [Event Procedure], and then go to that event in the VBA window.
8. In the GotFocus event, add some code to change the focus from that control to another control, one that can change.
Hiding Columns seems like something that should be simple, in fact it can be quite complex:
Record the column name and width
Detect Reduce the field width to zero
Move the remaining fields over (by the same amount that the field was reduced) to eliminate the blank space
...This is all for only one filed, doing this for multiple, disparate fields, can get hairy...
What about just creating a separate form just for these situations...
You can see this sample I made a while back to dynamically hide/show controls/Fields in a Report.
http://filedb.experts-exchange.com/incoming/2009/11_w46/202728/Access--Basic-SampleSelectHideSh.mdb
JeffCoachman
Record the column name and width
Detect Reduce the field width to zero
Move the remaining fields over (by the same amount that the field was reduced) to eliminate the blank space
...This is all for only one filed, doing this for multiple, disparate fields, can get hairy...
What about just creating a separate form just for these situations...
You can see this sample I made a while back to dynamically hide/show controls/Fields in a Report.
http://filedb.experts-exchange.com/incoming/2009/11_w46/202728/Access--Basic-SampleSelectHideSh.mdb
JeffCoachman
Thank you again sir.
I had considered this approach but wanted to make sure that I didn't miss a whole new angle to the problem.
I will likely create a second form.
I had considered this approach but wanted to make sure that I didn't miss a whole new angle to the problem.
I will likely create a second form.
Featured Post
Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder.
But why not let Windows retr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month2 days, 19 hours left to enroll
622 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.