Solved

Access datasheet subfrm textbox.columnhidden - dynamically setting to TRUE gives error

Posted on 2014-01-23
6
465 Views
Last Modified: 2014-03-06
I have a form A with a datasheet subform with multiple columns, plus a button "Change my column".  The user can select the columns he/she wants to see via a popup from form A.  After closing popup, I want to refresh the datasheet to show new show/hide columns.

I open the form by fteching the current column hide settings and using subform.control.columnhidden = usersetting for each field/column.
This works well.
User then selects 'change my columns' and ticks/unticks each column heading.  This is the code behind the button.
Private Sub cmdDisplaySetting_Click()
    DoCmd.OpenForm "frmSearch_Project_Fields", , , , , acDialog
    Call ShowHideColumns
    Me.subfrmTblJobSearch.Form.Requery
End Sub

Open in new window


It calls a routine ShowHideColumns which does exactly what you would expect.  For each datasheet column it sets .columnhidden.  This works well for hiding previously shown columns, but not exactly right for previously hidden columns that are now shown.

For a column that was previously hidden and is now shown it shows the column heading correctly and the column width is correct, but it does not show the data correcly.  Instead I get the #Name?  shown in every row for that field.  If you close form and reopen (where it now knows that column is not hidden) all is good.

The datasheet is a bound form to the main table with all the fields.

I thought requerying the subform might fix it but I still get the error.

So, if a bound datasheet field was HIDDEN on open, and then you change it to not HIDDEN, the columns appears correctly but it does not know about the column values.

Any ideas how to get the value to refresh?

Thanks.
0
Comment
Question by:MonkeyPie
6 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 167 total points
ID: 39805718
Strange, I am not able to replicate the behavior you described.  It works fine on my end.  Are you able to upload a copy of the database with just the applicable objects?  If so, be sure to not include any information that would be considered private.

Ron
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 167 total points
ID: 39806085
For the sake of completeness, can you post:

1.  the full code for ShowHideColumns
2.  The Recordsource property of your form
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 166 total points
ID: 39806128
Is there a reason you are not simply using the standard right click shortcut menu to allow users to hide and show columns?

I have a similar situation where when the form closes, I record what is hidden or shown and the next time the form is opened, it resets those values.  But to actually hide/reveal during a session, I simply use the standard datasheet column shortcut menu.
0
 

Author Closing Comment

by:MonkeyPie
ID: 39808067
False alarm.  I have found the problem.  It was the recordsource - it is dynamically set to only pick up fields that were previously set, so of course, when you add a new field to mix it was never in recordsource.  Thanks everyone.
0
 

Expert Comment

by:btgtech
ID: 39910420
I have a form where I am using the standard shortcut menu to allow the users to select columns to show and hide.  

When the user moved a column or hides a column, the form requires them to save the form when the leave or close out of the form.  Is this required?

And my expectation is that is each user has a separate Access front end database, that they can customize the form to their liking and it will remain that way when they come back into the form?

Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now