Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Allowing users to decide what columns show (XP/SP2)

    I've seen many applications that allow users to decide what columns that are viewed in datasheet view. I want to include this capability in my application but I'm not sure what the best approach is. I have a form that contains a tab control. The first tab has a subform in a datasheet view. The second tab will have options for displaying or hiding columns on the subform on the first tab. Is there an easy way to accomplish this?

    Thanks for any ideas.
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    Set the record source of the subform to an SQL string instead of a table or saved query.

    Use the selection made by the user to assemble a new SQL string.
    Set the record source of the subform to the new SQL string.
    If necessary, requery the subform.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    Thanks for the quick response once again Hans. What would you use on the options tab to give the user the ability to select fields? Check boxes, multi select listbox? Ultimately I want to allow the user to save the "view". So I will probably include a drop down box on the options tab giving them the ability to refer back to a view they saved.

    Any suggestions would be greatly appreciated.
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    You can store the preferences in a table, or in the Windows registry

    If you have multiple users and you want each of them to be able to save the "view", and if you want to store the preferences in a table, you must have a way of distinguishing users. One way to do this is a split database where each user has an individual copy of the front end; you can store the preferences table in the front end. Another way is to ask the user for a user ID, and store that with the preferences in a single table (in the back end if the database is split). In both cases, you could use a subform to present the preferences, with field names and check boxes.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    Hans,

    I'm close to getting the SQL assembled in code by using user preferences but I have one problem. By changing the recordsource of the subform it still only shows the fields that are in the subform. How can I get the subform to show only the fields (columns) that are in the recordsource. Do I have to also hide and show controls on that subform? How can I create a subform that doesn't have any controls. Do you know what I'm saying??

    Thanks,
    Don

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    I must admit that I hadn't thought this through. There is a much simpler solution to let the user select columns to show/hide, but I don't know how to save these settings. I'm curious to hear what others think. The following code (with the appropriate name for the subform substituted) will display the built-in dialog:

    Me.sbfSubform.SetFocus
    RunCommand acCmdUnhideColumns

    I don't think the approach I recommended originally is going to work. Hiding controls doesn't work for datasheet view.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    Hi Don

    You may want to look at the following:

    Smart Access Feb 2001

    http://www.pinpub.com/html/main.isx?sub=62&story=473

    HTH

    John

    ==============================
    You Can Do That with Datasheets?

    By Michael Kaplan

    Everyone who uses datasheets knows they're powerful, but very few people know all of the things you can do with them. Michael Kaplan pulls out a handful of tricks from the unlocked Access 97 wizards that can help you really get the best of these useful items.

    --------------------------------------------------------------------------------

    One of the things that even the most diehard Visual Basic developer admits is that they wish that Visual Basic had a grid as powerful as Access's datasheet. And with all of that power, there are still many things that no one knows how to do with them. Datasheets are routinely dismissed by developers, who seem to favor continuous forms.

    However, datasheets provide a simple yet powerful way to display data to your users. Datasheets also allow you to provide a user interface (UI) that your users can configure to meet their needs. Most developers feel that datasheets give too much control to their users. However, by taking control of your datasheets, you can incorporate them into your applications. In this article, I'll show some of the features of these powerful UI objects that most developers don't understand.
    ===============================

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    For the interest of others reading this: the site John refers to requires registration.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    If interested take a look at the sample db attached. Form frmCustomersDatasheet displays datasheet as subform based on Northwind "Customers" table. Checkboxes (one for each column in datasheet) allow user to select which columns to display (if checked column is displayed, if not column hidden). Code uses ColumnHidden property to display/hide fields. Example:

    Private Sub HideDatasheetColumns()

    Dim strColumn As String
    Dim n As Integer
    Dim intCol As Integer

    intCol = 0

    With Me
    .Subform1.SetFocus

    For n = 1 To 11
    strColumn = .Controls("CheckLabel" & n).Caption
    If .Controls("Check" & n).Value = True Then
    .Subform1.Form.Controls(strColumn).ColumnHidden = 0
    ' Save index for 1st visible column:
    If intCol = 0 Then
    intCol = n
    End If
    Else
    .Subform1.Form.Controls(strColumn).ColumnHidden = -1
    End If
    Next

    ' Set focus to first visible field:
    If intCol > 0 Then
    strColumn = .Controls("CheckLabel" & intCol).Caption
    .Subform1.Controls(strColumn).SetFocus
    End If

    End With

    End Sub

    The checkboxes are named Check1 to Check11 for the eleven columns available. The labels are named CheckLabel1, etc, where the label caption is same as corresponding field (column) name in subform. Setting ColumnHidden to True (-1) hides column. Setting ColumnHidden to False (0) restores the field's ColumnWidth property to the value it had before the field was hidden. Form includes a button to set all checkboxes to True & display all columns. Assuming each user is using front end on local pc, there's no need to save settings anywhere - the subform "remembers" which columns are hidden or not, the column widths, etc. Also no need to change RecordSource. You do need to "synchronize" the checkboxes with columns when form is opened:

    Private Sub ResetCheckboxes()

    Dim strColumn As String
    Dim n As Integer

    With Me
    For n = 1 To 11
    strColumn = .Controls("CheckLabel" & n).Caption
    .Controls("Check" & n).Value = Not .Subform1.Form.Controls(strColumn).ColumnHidden
    Next n
    End With

    End Sub

    If some reason you are using a "shared" front end on a network you might want to rethink your strategy. The only thing that should be shared on the network is the back end. A "shared" front end is a recipe for headaches or worse.

    HTH
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    Thanks Mark for the sample database. It does give me some good ideas. I was hoping to give the user the ability to save the view with a name of their choosing remembering the columns that are shown and the column size, but it may be too much work for this project. The database is setup with a frontend on the workstation and backend on the server.

    Thanks to everyone that has helped me here. If anyone else has any ideas please share...
    Thanks,
    Don

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    Thanks! Nice example.

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    Nice job Mark

    This db is similar to the article I referenced earlier in this thread.

    In no way am I endorsing subscriptions, however just like the many good book recommendation that are suggested, there are many monthly publications that virtually pay for themselves should be a must for developers making a living delivering Access solutions.

    John

  13. #13
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allowing users to decide what columns show (XP/SP2)

    Your sample database has given me some great ideas that have worked out well so far. I bound the checkboxes to a table where I'm allowing users to save their "views". I created a table that stores the view name, other fields for storing sorting and filtering options for each view, and 26 yes/no fields that store whether the columns are shown or not. This is all working great. I'm still at work so I haven't had time to try but I also need to store the order of columns in the datasheet. So I'm thinking of creating 26 more fields in this table to store the column order of each field the datasheet. Probably using the ColumnOrder property?? That's what I'm thinking and will try later on. If you or anyone else can confirm that's the route I should take that would be great. Once again....any ideas will be accepted from anyone!!

    Thanks to all.
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •