Results 1 to 10 of 10
  1. #1
    Chim20 is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    8

    Question User-Selectable Fields in Datasheet View

    Hello, I have a subform that is currently displaying two fields in datasheet view. However, I would like to add a third field option that allows the user to select which field is displayed from a list of the remaining fields in the table. The table has far to much information to display all fields at once in datasheet view, and those fields are displayed compactly elsewhere, but it would be useful if the user could display a field of their choice in this subform. Is something like this possible?



    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    that's complicated and needs lots o programming.
    cant you just show all the fields and let them look at the ones they want?

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You can hide and show fields in a datasheet by selecting a field, right clicking and selecting "Hide". You can right click and select "Unhide"

    You should be able to open the datasheet with two fields visible, and use code to allow other fields to be visible. Might require a combo box, a button and code.
    Or, you could utilize the right click option.

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    An interesting little problem! As Steve suggested, you can do this with a Combobox, but being a Datasheet View Form, you can't use a Command Button, so we'll just use the AfterUpdate event of the Combobox.

    Assuming that all of the Fields are present, on the Form:

    1. Add a Combobox to your Form
    2. Name it FieldListCombo
    3. Set the Row Source to the Form’s underlying Table/Query
    4. Set the Row Source Type to Field List

    Now place this code in the AfterUpdate event of the Combobox:
    Code:
    Private Sub FieldListCombo_AfterUpdate()
     
     Dim ctl As Control
    
     For Each ctl In Me.Controls
       
       If ctl.Name = Me.FieldListCombo Then
       
        If ctl.ColumnHidden Then
          
          ctl.ColumnHidden = False
        
        Else
          
          ctl.ColumnHidden = True
        
        End If
      
       End If
     
     Next
    
    Me.FieldListCombo = ""
    
    End Sub
    When you select a Field from the Combobox, it will change/toggle its Hidden state…if it’s currently Hidden, it will Unhide it, and vice versa, so you can show a column then hide it.

    Because the Combobox was added last, it will always be the right-most Column. And Access will ask the user, when closing the Form, if they want to save the changes to the design, so they can either leave the Columns as they were changed, or return to the original state.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Nice bit of code!

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    It was a fun working on it!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Chim20 is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    8
    Thank you very much, Missinglinq! I will implement this when I get the chance and let you know how it works out

  8. #8
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Great example.

  9. #9
    Chim20 is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    8
    One question I have is that since you said the user will be asked if they want to save changes to the design if changing the column, will that be a problem if they are using the Access runtime? Thanks

  10. #10
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Have never had the occasion to use Runtime, and really can't say! Hopefully someone who does have experience with it can answer that!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-15-2015, 12:23 PM
  2. Replies: 6
    Last Post: 11-21-2012, 05:10 PM
  3. Replies: 1
    Last Post: 07-16-2012, 01:26 PM
  4. Replies: 2
    Last Post: 09-21-2011, 07:23 AM
  5. datasheet view adding fields drop-down box
    By techexpressinc in forum Access
    Replies: 2
    Last Post: 09-09-2009, 05:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums