Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Thanked 0 Times in 0 Posts

    Drill into Data (2002)

    I have a dataset with approximately 15,000 lines. There are four fields that I would like to be able to use as key fields for drilling. Let's call them A, B, C, D. In some cases, I would like to drill into the dataset using only field A, in some cases only field B, in some cases, only field C, etc. In other cases, I would like to drill into the dataset using multiple fields, ie one value from field A, two values from field B, one value from field C, no selection on field D. I have tried to do this with a "Selection" table (ie an update query), but I am having trouble there. If anyone can help, I would appreciate it. I am weak on the visual basic part of Access, so I'd like to keep it as straightforward as possible.

  2. #2
    Silver Lounger
    Join Date
    Jun 2001
    Niagara Falls, New York, USA
    Thanked 0 Times in 0 Posts

    Re: Drill into Data (2002)

    Check out the following Drill Down techniques:

    Rupert at
    Has Multiple Combo Boxes to Select records

    Candace Tripp at
    Has Cascading Combo Boxes Demo



  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Frederick, Maryland, USA
    Thanked 2 Times in 2 Posts

    Re: Drill into Data (2002)

    What you could do is setup a form with four multi-select list boxes, one list box for each dataset you want to choose from. Create a temp table or one temp table for each listbox to store data. On the after update event of each mult-select listbox, write your selection to the temp table(s). Then create a query that joins the temp table(s) to your main data table with joins on each field in the temp table(s) to act as a filter. The resultant set will be based on your selections.

    For example, you create a multiselect listbox named Listbox1 and write the data selected to a table named Temp1 and to the field named Field1.
    The code for updating a multiselect listbox is as follows:

    <pre>Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim stDocName As String
    Dim ctl As ListBox
    Dim varitem As Variant
    Dim strSql As String

    DoCmd.SetWarnings warningsoff

    'Delete all prior selections from Temp1 table
    strSql = "Delete * from Temp1;"
    DoCmd.RunSQL strSql

    'Set ctl to the the listbox
    Set ctl = Me!Listbox1

    'Set Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Select[TEMP1].Field1 from [TEMP1]")

    'Loop through items in Listbox

    For Each varitem In ctl.ItemsSelected
    With rst
    !Field1 = ctl.Column(0, varitem)
    End With
    Next varitem


    What this does is every time you make one or many selections in your listbox, the temp table will be deleted and re-populated with your selection. You can then use that for other use.

    This is just one way to do this. HTH

    (It's been a while!)

Posting Permissions

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