oOo.oOo.oOo.oOo.oOo.oOo

Filtering Forms…

Here are three ways to filter records on a Single Form or a Continuous Form. (In these examples no Subforms were used.)

Example 1
To toggle back and forth between (Yes/No Data Type) All and Completed (Tasks, Projects, Orders, etc.)…

  • Step 1

In the Header of you Form place an UNBOUND Check Box and name it chkMyFilter

  • Step 2

In the After_Update event of the UNBOUND Check Box copy/paste everything between the Private Sub and End Sub lines below. After you have copied it change pDone to the name of your Yes/No field in your Table, not the name of the field Control name on the Form.

‘Private Sub chkMyFilter_AfterUpdate()

    If Me.chkMyFilter = True Then
       Me.Filter = “pDone = ” & vbTrue
       Me.FilterOn = True
   Else
      Me.Filter = “”
       Me.FilterOn = False
   End If

‘End Sub

Close and save (or save and then close) and try it out.

 

Example 2
In this example you can use values from a look-up table to filter your records. This allows a lot more flexibility because I can add/edit and/or subtract items from the list via the table. My table is…

tblSources

  • Step 1

In the Header of you Form place an UNBOUND Combo Box and name it cboSSourceID. (The double S is not a mistake, it is because in the Detail section of my Form I have a Combo Box named cboSourceID, so, I put an additional S to indicate this is my cbo*Search*SourceID used to Filter my records.

  • Step 2

Set the Row Source of cboSSourceID the same as the Combo Box in the Detail section of your Form. For this example I used…

SELECT tblSources.sSourceID, tblSources.sSource
FROM tblSources
WHERE (((tblSources.sActive)=True))
ORDER BY tblSources.sSortOrder;

Other settings:

Column Count =2
Bound Column = 1
Column Widths – 0″;1″

  • Step 3

In the After_Update event of the UNBOUND Combo Box copy/paste everything between the Private Sub and End Sub lines below. After you have copied it change pSourceID to the field name in your Table, not the name of the field control name on the Form and in your Table and change the cboSSourceID to the name of your UNBOUND Combo Box in the Header.

‘Private Sub cboSSourceID_AfterUpdate()

   Me.Filter = “[pSourceID] = ” & Me.cboSSourceID
   Me.FilterOn = True

‘End Sub

    • Step 3a

To clear the Filter you can use the Double_Click of cboSSourceID

 Private Sub cboSSourceID_DblClick(Cancel As Integer)

   Me.Filter = “”
   Me.FilterOn = False
   Me.cboSSourceID = “”

End Sub

…or add the above code a Clear Filter Command Button.

Close and save (or save and then close) and try it out.

 

Example 3
This example allows you to create a Query as much or as little criteria as you like for the filter. You can use one Command Button to toggle back and forth between All/Filtered, for this example I am using one Command Button using the Caption property of the Command Button to toggle back and forth.

  • Step 1

Create two Record Sources, one that will show all the Records, such as the Table and another that shows just the filtered Records such as a Query, i.e.:

tblPending for all the Records
qryPending for the filtered Records.

qryPending
SELECT tblPending.pPendingID, tblPending.pActionID, tblPending.pQuantity, tblPending. pReceiveDate, etc…
FROM tblPending
WHERE (((tblPending.pActionID) Is Null) AND ((tblPending.pReceiveDate) Is Null));

  • Step 2

Set the Record Source of your Form using the *filtered* query. In this example that would be qryPending. (If you want to open to showing ALL records you will need to reverse the code!)

  • Step 3

ShowAllFiltered

Place a Command Button in your Forms Header and name it cmdShowAll. If the Wizard pops up, just press Cancel, we are going to put our own code there so we won’t be using any of the Wizards code. Change the Caption property to say “SHOW ALL”. For my Command button I used these settings…

Font Name= Tahoma
Font Size = 7
Font Weight = Bold

Side note:  The buttons will change Caption depending on which Record Source is showing, which is why you only need to add one Command Button.  Make sure you follow Step 4 and Step 5 for the Caption to toggle properly.

  • Step 4

At the top of your Forms Module under…

Option Compare Database
Option Explicit

Side note: If those two lines are not at the top of your Module put them there, particularly, Option Explicit which will *stop* when it finds an undeclared variable(s).

Mine says SHOW ALL or COMPLETED ONLY but yours can say whatever you want. Just remember to make sure the Command Button is wide enough to show the longest Caption.

…place these two lines 

Const ShowAll = “SHOW ALL”
Const ShowFiltered = “COMPLETED ONLY”

  • Step 5

In the On_Click event of the Command Button copy/paste everything between the Private Sub and End Sub lines below.

‘Private Sub cmdShowAll_Click()
On Error Resume Next

   Select Case Me.cmdShowAll.Caption
       Case ShowFiltered
           Me.RecordSource = “qryPending”
           Me.cmdShowAll.Caption = ShowAll
       Case ShowAll
           Me.RecordSource = “tblPending”
           Me.cmdShowAll.Caption = ShowFiltered
   End Select

‘End Sub

Close and save (or save and then close) and try it out.

 

Here are more filtering examples…

Allen Browne’s Search Form
Allen Browne’s Filter a Form on a Field in a Subform

95 total views, 1 views today

Share Button

Excel Tutorials for FREE…

 

EasyExcelA new link has been added to the Microsoft Excel Tips links on the left.  However, after visiting the site…

I have to say, this has to be one of best websites dedicated to Microsoft Excel that I have come across in a long time.  Hmm, maybe I should rephrase that…  This has to be one of best websites dedicated to Microsoft Excel that has come across me by way of Niels Weterings (big *THANK YOU* for dropping me a line).  I am truly impressed with the clear, concise instructions that made even Pivot Tables look easy!

Never mind that it’s FREE, it’s a great site for beginners right on up to advanced Users.  You get sample code, clear definitions and, in some cases, downloadable sample files.  Truly, a great site!

120 total views, no views today

Share Button

Conditional Row Shading on a Continuous Form

Row Shading has come a long way since Access 2003 where you had to use code to get it to happen. Remember this… 

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

   If bluebar Then
       Detail.BackColor = 16777215
   Else
       Detail.BackColor = 13888992
   End If

bluebar = Not (bluebar)

End Sub

Then came Access 2007 and we got Alternate Back Color…

Property Sheet Alternate Back Color

Property Sheet Alternate Back Color

While that was way cool, what was still missing was a way to conditionally shade a row. Here’s a way to do just that with no VBA…

Step 1

Note: In this example the Control names are chkInStock, a Check Box and txtInStock, an unbound Text Box.

Identify a Control (*trigger*) that will let Access know you want to highlight that row. For this example we’re using the Check Box, chkInStock.  Then in your unbound Text Box and type… 

=IIf([chkInStock]=True,”████████████████████████████████████████████████████████████”,””)

Stretch the Control to the full length of the Row, set the Font Size to insure it goes the Height of the Row and select the color of the Font for your Control (this is what determines the color of the highlighting).  In this example I used #F6FBDD.

Row Shading (Design Mode)

Row Shading (Design Mode)

Side note: For Bar (Full *Black* Block) use *Arial U + 2588 Full Block*.  To insert the Full Block…

   a.  Open your Windows Character Map, found under Accessories > System Tools and select the Arial Font from the Font drop down.  

Character Map

Character Map

   b. Once selected click Copy and then navigate back to Access

Character Map (Copy)

Character Map (Copy)

   c. In Access, paste the Full Block within your Statement on the Control Source line… 

Property Sheet Control Source

Property Sheet Control Source

Step 2

You can set your Controls to anything you like, however, I would recommend you give your Controls some sort of Border.  This will not only help ditinguish Columns but also Rows.  In this example all the Controls in the Row are set to… 

Back Style:  Transparent
Back Color:  No Color
Border Width:  Hairline
Border Color:  #C0C0C0
Special Effect:  Etched

Step 3 (Optional)

In this example the Detail section of the Form has been set to alternate colors, you can choose whatever option suits your database.  To set the Rows in the Detail section to alternate colors, in the Properties window for the Detail section set the…

Back Color:  Background 1 (or White)
Alternate Back Color:  #F5F4F1

Step 4

Save and you’re all done.  Now, when you select/deselect the In Stock Check Box the row will toggle according. Enjoy!

Download the sample file here.

Row Shading

Row Shading

 

267 total views, 5 views today

Share Button

Compile on Demand check on uncheck?

CompileOnDemand

Recently, while trying to Debug some code a colleague of mine suggested I leave Compile on Demand1 unchecked.  Since in all my years of developing I have always left it checked, I asked why and here’s what he said got…

Many years ago, Michael Kaplan mentioned that this setting was known to cause problems, at a Pacific NW Access Developer’s Group meeting.

Here is how having this Option checked can bite you in the patooka’s!  Suppose you have unfinished code, or some other compile error, in code that you are not currently using. You forget to do a final Debug > Compile, to force all code to Compile. You deliver your app. to your customer. They immediately get a Run-Time error, with the same unused code. Why?  Because this setting does not “travel” with your database; it is a global VBA environment setting. With this Option unchecked, ALL code must be compilable before any code can be run. Checking this option (the stupid default setting), allows for partial compliable; in other words, only the Modules that have code that you are running must be compilable. You could still have that experimental new module with code that does not Compile. Personally, I’d rather find these issues myself, instead of having a customer find them.  <smile>

Additional comments here…  Say it aint’s so, Joe.

Now, with that being said, I am still leaving mine checked (and I am not alone), not because I like to skirt danger but because I Compile my code if I just look at it.  (Though nothing to do if you make no change, I check anyway.)  So, the choice is up to up but I would heed the advice if you are not *religious* about Compiling your code!

3,425 total views, 31 views today

Share Button

Sort by Descriptions is gone in Access 2007 and up but…

…you can see them, thanks to a post by JoelS_999 in the Microsoft Community Forums.  And, while this may not be exactly what you want, this neat little piece of code will allow you to see them in a table, tblSysObjProps.  This is especially helpful when upgrading from Access 2003 and below, where you have used Descriptions to store information pertinent to your Object. 

‘***Start of code
Public Function ListObjProps()
‘ Created by Joel S. on 12/05/2013

‘ This function lists MS Access 2010 objects (i.e. tables, queries, forms, reports,
‘ etc.) and their top-level properties as shown in the navigation pane. The output
‘ is written into a table, which allows an open, grid-like view of the object
‘ properties, similar to the navigation pane in Access 2000-2003.  Also, the output
‘ table makes it possible to sort on the Description field, which the Access 2010
‘ navigation pane does not allow.

‘ CAVEATS: The DateCreated and LastUpdated fields will reflect the values stored
‘ in the MSysObjects system table, not the values shown in the navigation pane
‘ (which sometimes appear to be incorrect).  Also, the Type field will show “Tables” not
‘ “Queries” for query objects, so this could cause confusion if some of the tables
‘ and queries in the database use exactly the same names.  Finally, this function should
‘ be run whenever the user deems he/she has added/deleted/modified enough
‘ MS Access objects to warrant refreshing the output table.

On Error GoTo Err_ListObjProps

Dim db As Database ‘Variable for current database
Dim ctnr As Container ‘High-level containers for MS Access objects
Dim obj As Document ‘Individual MS Access Objects–tables, queries, forms, etc.
Dim prop As DAO.Property ‘Object properties
Dim tdf As TableDef ‘Output table definition
Dim strTbl As String ‘Output table name
Dim rst As Recordset ‘Recordset for updating output table
Dim boolRstOpen As Boolean ‘Flag to indicate whether recordset is open

‘Turn off warnings
DoCmd.SetWarnings False
DoCmd.Hourglass True

boolRstOpen = False ‘Initiate flag to indicate recordset hasn’t been opened.
Set db = CurrentDb()
strTbl = “tblSysObjProps” ‘Name output table. (Can be renamed if user wishes.)

On Error Resume Next
‘Close and delete previous output table if needed
DoCmd.Close acTable, strTbl, acSaveNo
DoCmd.DeleteObject acTable, strTbl
On Error GoTo Err_ListObjProps

‘Create new output table definition
Set tdf = db.CreateTableDef(strTbl)
With tdf
  .Fields.Append .CreateField(“Name”, dbText)
  .Fields.Append .CreateField(“Type”, dbText)
  .Fields.Append .CreateField(“Description”, dbText)
  .Fields.Append .CreateField(“DateCreated”, dbDate)
  .Fields.Append .CreateField(“LastUpdated”, dbDate)
End With
‘Append new output table definition to database
db.TableDefs.Append tdf

‘Open output table for updating
Set rst = db.OpenRecordset(tdf.Name)
boolRstOpen = True
If (rst.RecordCount > 0) Then
    rst.MoveLast
End If

‘Cycle through all container types–tables, queries, forms, reports, etc.
For Each ctnr In db.Containers
  ‘Cycle through each object in the container–individual tables, queries, etc.
  For Each obj In ctnr.Documents
    ‘Create new record in output table
    rst.AddNew
      obj.Properties.Refresh
      ‘Print object name to immediate window if needed
      ‘Debug.Print obj.Name
      ‘Cycle through each top-level property for the object in question
      ‘and write the value for each specified property into output table.
      For Each prop In obj.Properties
        Select Case prop.Name
          Case “Container”
            rst!Type = prop.Value
          Case “DateCreated”
            rst!DateCreated = prop.Value
          Case “Description”
            rst!Description = prop.Value
          Case “LastUpdated”
            rst!LastUpdated = prop.Value
          Case “Name”
            rst!Name = prop.Value
        End Select
        ‘Print object properties to immediate window if needed
        ‘Debug.Print vbTab & prop.Name & ” = ” & prop.Value
      Next prop
      ‘Update output table for last record entered.
      rst.Update
      rst.MoveLast
  Next obj
Next ctnr

‘Clean up and close function
If boolRstOpen Then
    rst.Close
End If
‘MS Access quirk requires the hidden output table to be explicitly hidden then unhidden.
Application.SetHiddenAttribute acTable, strTbl, True
Application.SetHiddenAttribute acTable, strTbl, False
db.Close
DoCmd.Hourglass False
DoCmd.SetWarnings True
MsgBox (“ListObjProps function completed successfully. See table ” _
  & strTbl & ” for output.”)
Exit Function

Err_ListObjProps:
  ‘Clean up and close function
  On Error Resume Next
  If boolRstOpen Then
    rst.Close
  End If
  db.Close
  DoCmd.Hourglass False
  DoCmd.SetWarnings True
  MsgBox (“ERROR: ListObjProps function did not complete successfully.”)
 
End Function
‘***End of code

 
Still missing the Database Window?  Here are some options…
AAD Consulting’s Database Window for Access 2007 and Access 2010
Avenius Gunter’s Database Window for Access 2007, Access 2010 and Access 2013
Access Junkie’s Database Window for Access 2007

344 total views, 3 views today

Share Button