Solved

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

Posted on 2014-01-23
6
475 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

816 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

8 Experts available now in Live!

Get 1:1 Help Now