Solved

Hide  / collapse columns in Form as needed

Posted on 2013-01-07
8
992 Views
Last Modified: 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?
0
Comment
Question by:peispud
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 84
ID: 38750446
Is your continuous form in Datasheet view? If so, you can use the ColumnHidden method:

Me.Text0.ColumnHidden = True
0
 

Author Comment

by:peispud
ID: 38750466
Nope..    Form is not in Datasheet view.


I have set the view to continuous form.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38750614
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.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:peispud
ID: 38751321
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38751376
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 38751553
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
0
 

Author Closing Comment

by:peispud
ID: 38752681
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38752830
Thanks,

As fyed will also tell you, we are big proponents of not only trying to let you see the many ways of doing something, but to also suggest alternatives.

JeffCoachman
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question