The difficult I do immediately, the impossible takes a little bit longer.
Forms
Tips (Main)
Home
Let your Client customize the form labels
Linking Subforms to an Unbound Control(s) on the Main (Parent) Form
Customize your Main Menu or Switchboard
Auto-Add new child records when adding a new record on the Main (Parent) Form
Simple Bar Chart using Shapes
Simulate a Drop Down box (not a Combo Box) as seen on Web Pages
Implement a Log-On form to control what the User sees
Make Controls resize with a Form
Switch Windows
Assign *Permissions* based on Users Levels
New User Form
Add *Favorites* to a Continuous Form
Custom Record Selectors
Searching Records
Highlight Required Controls (End-User Defined)
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.
Click to send feedback...
    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.
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.