This post is just for me. I do occassional work on some Access databases, not as much as I used to do, and now I find that I can’t quite remember how to get the results I need. I have decided that I will copy bits of code into this blog so I can easily find exmples of what I want. Years ago I had a book that I copied code snippets into but now since wonderful people are putting more information on the internet it is usually quite easy to find what I am looking for, however, today I spent hours playing with a filter to get it to return all results with part of a word I typed into a text box – the solution wasn’t easy to find through Google and what worked last week wasn’t working today.
Enough for wasting that time – I have the solution so it is going in here. At the same time I will put in other filter samples from this database so I can use them as a reference next time I need it.
In Access there are often many ways to get to the same outcome. Here I am putting what works for me. There are probably more elegant ways to do all of these things, and I will add them as alternatives as I find them.
DECLARING VARIABLES
Ensure the Option Explicit is at the top of all procedures. Either type it in if it is missing in exisitng procedures and then set the database to declare variables in new procedures.
Go to the VBA screen, TOOLS, OPTIONS, tick declare variables.
FILTERS
FILTER FROM A TEXT BOX ON A FORM WITH WILDCARD
On the “AfterUpdate” property of the text box or “on click” from a button next to the text box.This filters the records and returns the focus to the search box. If you want the focus to be elsewhere then specify it here.
Me.FilterOn = False
Me.Filter = “ LIKE ” & “‘” & “*” & & “*” & “‘”
Me.FilterOn = True
lookup.SetFocus
… and to go with that a “GotFocus” function to clear the field when it is clicked into:
lookup = “”
It can also be done as a search string
stLinkCriteria = “ LIKE ” & “‘” & “*” & Me! & “*” & “‘”
FILTER FOR MORE THAN ONE CRITERIA USING NULL AND NOT NULL
Me.FilterOn = False
Me.Filter = “Is Null AND status = ‘win'”
Me.FilterOn = True
OR
Me.FilterOn = False
Me.Filter = “Is Not Null AND status = ‘win'”
Me.FilterOn = True
FILTER FOR MORE THAN ONE CRITERIA USING THE RESULTS FROM A CHECK BOX IN ONE OF THE CRITERIA
Me.Filter = “((Ended=False)) AND = ‘Active’ ”
Me.FilterOn = True
Me.OrderBy = “CompanyName ASC”
FILTER FROM THE RESULTS OF A RECORD IN A COMBO BOX
Private Sub Combo30_AfterUpdate()
Me.FilterOn = False
Me.Filter = “ ='” & Me.Combo30 & “‘”
Me.FilterOn = True
FILTER FOR TWO CRITERIA AND A COMBO BOX SELECTION
Private Sub SalesCombo_AfterUpdate()
Me.FilterOn = False
Me.Filter = “ = ‘” & Me.SalesCombo & “‘ AND reason=’Follow-Up’ AND Type = ‘Sales'”
Me.FilterOn = True
Me.OrderBy = “DueDate DESC”
FILTERS FROM BUTTONS
I usually use these from labelled buttons, eg show prospects only. This one also sorts the results by company name in alpha order.
Me.Filter = “status=’prospect'”
Me.FilterOn = True
Me.OrderBy = “CompanyName ASC”
This filter shows only records that don’t meet the criteria “closed”
Me.Filter = “<>’Closed'”
Me.FilterOn = True
CLEAR FILTERS OR SHOW ALL RECORDS
This is useful to clear any existing filters on the form, I often include a button for “Show All”. It is also useful On_Load for previously filtered forms.
Me.FilterOn = False
Another way to achieve the same results:
DoCmd.ShowAllRecords
FILTER ON A SUB FORM
This also sorts the returned data by the due date in descending order
Me.Filter = “(.reason)=’Follow-up'”
Me.FilterOn = False
Me.OrderBy = “DueDate DESC”
COMBO BOX TO FIND A RECORD IN THE DATA.
This is the simplest way that I know to achieve this, it sets the focus on the field that you want to search in, then tells it to look for a record that matches the combo box and returns to cursor to the combo box. The quality of this is always in where the records in the combo box a gleaned from. It is better to have a couple of columns in the combo so the right data can be selected. Make sure that the bound field is the same data that is in the searched field.
Combo15_AfterUpdate()
CompID.SetFocus
DoCmd.FindRecord Combo15
Combo15.SetFocus
REAL BASIC STUFF – EVENT PROCEDURES “ONCLICK”
OPEN FORM –
Use in on click for a button or double click on a field – eg you can double click on a comapany name in a table and open the details form for that comapany
opens the form to show all records:
DoCmd.OpenForm “AAReports”
Opens the form to show specific data:
stDocName = “Customers”
stLinkCriteria = “=” & “‘” & Me! & “‘”
DoCmd.OpenForm stDocName, , , stLinkCriteria
QUIT DATABASE
DoCmd.QUIT
SET FOCUS ON A FIELD
This is useful on Form_Open to set the focus on a particular field
.SetFocus
NEW RECORD
This clears the form for the entry of a new record, usually on a button but can also be set as an on_load event. This one then set the focus on the first field for data entry:
DoCmd.GoToRecord , , acNewRec
EmployeeID.SetFocus
TO SET THE CURSOR IN A NOTES FIELD TO THE END OF THE EXISTING DATA.
This is useful because all data in a notes field is usually selected and a simple “ENTER” deletes everything already there. This takes the curser to the end of the existing data.
Use it on GotFocus()
Me.Comment.SelStart = Me.Comment.SelLength
PRINT RECORD
Used on a form designed to be printed. It shows the form to allow for checking the lyaout and data before being sent to the printer. The “PRINT” button is set for visible “Screen Only’
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = “Quote”
stLinkCriteria = “=” & Me!
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.PrintOut
AND TO OPEN A REPORT FOR A SELECTED RECORD IN PRINT PREVIEW MODE:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = “Deal Sheet”
stLinkCriteria = “=” & Me!
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
AND TO OPEN A FORM FOR A SELECTED RECORD:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = “SHDDealDetails”
stLinkCriteria = “=” & Me!
DoCmd.OpenForm stDocName, , , stLinkCriteria
TO COMPLETE A CALCULATION AFTER UPDATING THE DATA IN A FIELD
Private Sub Weeks_AfterUpdate()
Weeks = ( / )
EndDate = ( + ( * 7))
Value = (( * ) + ( * * ) + ())
COPY THAT CALCULATED DATA TO A FIELD IN THE TABLE
Private Sub Update_Click()
‘update records to parent form
=
=
=
DoCmd.Requery
COPY DATA TO A FIELD IN THE PARENT FORM
Use on AfterUpdate or Change or on a button to copy data. Also to make it required remove the control box from the form and put in a close button with SAVE and any update / copy actions needed.
!! = ()
Like this on a close button:
!! = ()
DoCmd.Save
DoCmd.Close
MESSAGE BOXES
This can be used to annoy your users in lots of places. They usually require the user to click off it to close the box. A message can be part of an action or a pop up on a GotFocus
MsgBox “Update this record on the company data tab and change account status to active”, vbOKCancel, “UPDATE DATA”
ERROR MESSAGES
Add to the code to tell your user what they are doing wrong
Err_WebQuote_Click:
MsgBox “A Website data entry quote hasn’t been commenced for this customer. Enter basic information on this form and then open the data entry form to complete.”, vbInformation, “WEBSITE QUOTE”
Resume Exit_WebQuote_Click