Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-23
6
Medium Priority
?
491 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
[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
6 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 501 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 501 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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 498 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

715 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