Access Diva Drill Down (for Continuous Forms)

Drill Down (for Continuous Forms)...

When using Microsoft^® Excel (see below) you can drill down to what you want by using the Filter drop down. This is especially helpful when the list is a couple of hundred rows. However, no such Filter exists in Access but continuous Forms with a couple of hundred records do. Here's a way to simulate that functionality in Excel in Access.

    DoCmd.RunCommand acCmdSave
    If DCount("sbAddToTag", "tblScrapBundles", "sbAddToTag = True") = 0 Then
        Forms!frmScrapBundles.RecordSource = "qryScrapBundles"
        Forms![frmScrapBundles]![cmdFilterSelected].Caption = "SHOW ALL"
        Forms![frmScrapBundles]![cmdFilterSelected].BorderColor = RGB(192, 199, 222)
    End If

On Error GoTo Err_cmdSetFilter_Click
 
    If DCount("sbAddToTag", "tblScrapBundles", "sbAddToTag = True") > 0 Then
        Select Case Forms![frmScrapBundles]![cmdFilterSelected].Caption
            Case "FILTERED"
                DoCmd.RunCommand acCmdSave
                Forms!frmScrapBundles.RecordSource = "qryScrapBundles"
                Forms![frmScrapBundles]![cmdFilterSelected].Caption = "SHOW ALL"
                Forms![frmScrapBundles]![cmdFilterSelected].BorderColor = RGB(192, 199, 222) 'Pale Blue
                DoCmd.Close acForm, Me.Form.Name
            Case "SHOW ALL"
                DoCmd.RunCommand acCmdSave
                Forms!frmScrapBundles.RecordSource = "qryScrapBundlesSelected"
                Forms![frmScrapBundles]![cmdFilterSelected].Caption = "FILTERED"
                'Change color to let User know the Form is in filtered view.
                Forms![frmScrapBundles]![cmdFilterSelected].BorderColor = RGB(255, 194, 14) 'Orange
                DoCmd.Close acForm, Me.Form.Name
        End Select
    Else
        DoCmd.Close acForm, Me.Form.Name
    End If
 
Exit_cmdSetFilter_Click:
    Exit Sub
 
Err_cmdSetFilter_Click:
    MsgBox Err.Description
    Resume Exit_cmdSetFilter_Click

Add Command Button cmdFilter in the Header section of frmScrapBundles above the column you want to apply the Filter to. (You can add the cmdFilter to as many columns as you like just use the same line to open the sfrSelectCoilForBrundle.) Then add...

DoCmd.OpenForm "sfrSelectCoilForBundle"

to the On_Click event procedure. Then add cmdFilterSelected to the very top, above the labels. And add the below code to the On_Click event procedure.

On sfrSelectCoilForBundle which should be set as Pop-Up to Yes add a cmdSetFilter in the Header section and add the code below to the event procedure.

Main Form	Recordource (or Code)
frmScrapBundles (Initial Form)	qryScrapBundles (Set as Recordsource) qryScrapBundlesSelected
-cmdFilterSelected	Click here to jump to code
-cmdFilter (Little blue down arrow)	DoCmd.OpenForm "sfrSelectCoilForBundle"

Pop-Up Form	Recordource (or Code)
sfrSelectCoilForBundle (Pop-up Form called from cmdFilter)	SELECT crCoilID, adAlloy, sbAddToTag, etc...
-cmdSetFilter	Click here to jump to code

These are the names of the Objects, Controls and Record Sources used in the this example.

I also added a Filter at the top so Users could quickly drill down to a specific Alloy or find a specific Coil.

On Error Resume Next
 
    If DCount("sbAddToTag", "tblScrapBundles", "sbAddToTag = True") > 0 Then
        Select Case Me.cmdFilterSelected.Caption
            Case ShowFiltered
                Me.RecordSource = "qryScrapBundles"
                Me.cmdFilterSelected.Caption = ShowAll
                Me.cmdFilterSelected.BorderColor = RGB(192, 199, 222)
            Case ShowAll
                Me.RecordSource = "qryScrapBundlesSelected"
                Me.cmdFilterSelected.Caption = ShowFiltered
                Me.cmdFilterSelected.BorderColor = RGB(255, 194, 14)
        End Select
    Else
        MsgBox "Nothing to filter!", vbInformation + vbOKOnly, "Filter Selected"
        DoCmd.CancelEvent
    End If

At the very top of VB Editor of frmScrapBundles Module under your Declarations add...

Const ShowAll = "SHOW ALL"
Const ShowFiltered = "FILTERED"

Note, if you are using an image on your Command Button the text won't show but is still needed to trigger the Filter. Then Debug > Compile and close frmScrapBundles to save.

And finally, add this code in the On_Unload event procedure of the sfrSelectCoilForBundle. This is to confirm in the case no Filter has been applied to leave frmScrapBundles alone.

Always try new stuff in a copy of your database. Be sure to pay close attention when substituting names used in this example to match your database objects. And always read ALL the instructions before implementing.