User-Selectable Fields in Datasheet View

[Chim20]

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

[ranman256]

that's complicated and needs lots o programming.
cant you just show all the fields and let them look at the ones they want?

[ssanfu]

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.

[Missinglinq]

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:

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)>

[ssanfu]

Nice bit of code!

[Missinglinq]

It was a fun working on it!

Linq ;0)>

[Chim20]

Thank you very much, Missinglinq! I will implement this when I get the chance and let you know how it works out

[Perceptus]

Great example.

[Chim20]

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

[Missinglinq]

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)>