Checkboxes

[JReagan]

I am trying to create a form that users can use to select what fields they want to view of a table. I was going to build the form and have it show "Checkboxes" associated with each field and at the bottom there would be a button that when they click it, it will open the table with only the fields they have selected or boxes that have checks in them.

I have looked everywhere and can't seem to find any simple examples on this. I know that this will involve using either macros or VBA but I just need something that shows examples. Any ideas?

Jim

 

[pbaldy]

This type of thing is fairly rare, in part because the desire to do it often (if not usually) indicates a design problem. What do the fields represent?

 

[JReagan]

Well then its a good thing I have not designed it yet. I guess for example let's say we have a customer table. In this table the fields are "Customer Name", "Customer Number", "Sales Manager", "Cust Phone Number", "Contact", "Customer Address Line", "City", "State", and "Zip Code".

So then what I need to set up is something where the user can go in and decide which field they want to view. Fields such as "Customer Name" and "Customer Number" would always come up, so based on the source table the number of records or rows (No duplicates) should not change. So the user could look at the customers and perhaps only want to view the "Sales Manager" field and "State". Does this make more sense?

I would think this is easy, just how or what would be the best method of design? The users don't use access so asking them to learn to write queries would be like pulling teeth. I need it to be as simple as checking a box for the fields and running a report or opening a table or running a query.

Jim

Jim

 

[pbaldy]

Actually that looks fine from a design perspective. I was worried you had fields like January, February or something along those lines. It is fairly common to offer users the ability to filter based on optional criteria (a particular sales manager, a particular state, some combination of criteria, etc). It is uncommon to choose what fields are displayed.

Building the SQL of a query wouldn't be too tricky; having a report display that query would be pretty involved. Though most of us don't normally let users view tables or queries directly, but if you wanted to build a query and display it for the users, that wouldn't be too tough.

 

[JReagan]

Unfortunately I do want to the users to have the ability to choose which fields are viewed. That is where I'm stuck. What would be the recommendation (if any) to do this?

In a query in design view there is a row called "Criteria" and above that a row called "Show". The "Show" row has check boxes for each of the fields in the query, would it be possible to link these check boxes to a control or check box on a form?

Jim

 

[pbaldy]

Not really; you'd have to build the SQL in code. Here's a tutorial on that, including a sample db:

http://www.baldyweb.com/BuildSQL.htm

In your case you'd also be building the SELECT clause based on user selections (I would lean towards a multi-select listbox rather than the checkboxes). You'd probably also want to use a QueryDef object to change the SQL of a saved query rather than using the subform as the example does.

 

[JReagan]

So with a list box, they would still be able to choose the fields they want to view?

Jim

 

[pbaldy]

Sure; you'd populate the list box with the fields. It's even one of the options for the Row Source Type.

 

[SOS]

Here's a sample.

 

[pbaldy]

FYI, I can't remember what version it appeared in, but AddItem is not available in 2000 or earlier. I think it came in with 2002 or 2003. Personally I would probably just use the Row Source Type option anyway.

 

[SOS]

FYI, I can't remember what version it appeared in, but AddItem is not available in 2000 or earlier. I think it came in with 2002 or 2003. Personally I would probably just use the Row Source Type option anyway.

Okay, quick modification to the load code:

Private Sub Form_Load()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strValList As String
    Set db = CurrentDb
    Set tdf = db.TableDefs("Orders")
 
    For Each fld In tdf.Fields
        strValList = strValList & fld.Name & ";"
    Next fld

    strValList = Left(strValList, Len(strValList) - 1)

    Me.lstSelectFields.RowSource = strValList
 
End Sub

 

[JReagan]

Well if it helps any, I'm using 2007. So let me know if that throws any more details you can provide. Thanks

Jim

 

[SOS]

Well if it helps any, I'm using 2007. So let me know if that throws any more details you can provide. Thanks

Jim

My sample should work in 2007 as well.

 

[JReagan]

Anyway to view more than one field at a time?

 

[JReagan]

Where the highlighted field stays highlighted once its selected or clicked on?

 

[SOS]

Anyway to view more than one field at a time?

All you have to do is hold your control key down when selecting fields in the listbox. Or start at one spot and hold shift and click a field below to capture all of them in between. Just like the normal multi-select listbox functionality.

 

[JReagan]

So holding the Ctrl key down works for selecting multiple fields. Is there somewhere in the properties where I wouldn't have to use the Ctrl key to do this?

 

[SOS]

So holding the Ctrl key down works for selecting multiple fields. Is there somewhere in the properties where I wouldn't have to use the Ctrl key to do this?

Change the listbox's MULTI-SELECT property from EXTENDED to SIMPLE.