Microsoft Access – Code Snippets

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 = “[Product Name] LIKE ” & “‘” & “*” & [lookup] & “*” & “‘”
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 = “[Company_Name] LIKE ” & “‘” & “*” & Me![txtCriteria] & “*” & “‘”

FILTER FOR MORE THAN ONE CRITERIA USING NULL AND NOT NULL

Me.FilterOn = False
Me.Filter = “[EndDate]Is Null AND status = ‘win'”
Me.FilterOn = True

OR

Me.FilterOn = False
Me.Filter = “[EndDate]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 [AcctStatus] = ‘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 = “[Rep] ='” & Me.Combo30 & “‘”
Me.FilterOn = True

FILTER FOR TWO CRITERIA AND A  COMBO BOX SELECTION

Private Sub SalesCombo_AfterUpdate()
Me.FilterOn = False
Me.Filter = “[EmployeeID] = ‘” & 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 = “[reason]<>’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 = “([CustContactRecord-T].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 = “[CompanyName]=” & “‘” & Me![CompanyName] & “‘”
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

[LookUp].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 = “[CampHistID]=” & Me![CampHistID]
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 = “[DealNo]=” & Me![DealNo]
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 = “[DealNo]=” & Me![DealNo]
DoCmd.OpenForm stDocName, , , stLinkCriteria

TO COMPLETE A CALCULATION AFTER UPDATING THE DATA IN A FIELD

Private Sub Weeks_AfterUpdate()
Weeks = ([TotalDays] / [DaysPerWeek])
EndDate = ([StartDate] + ([Weeks] * 7))
Value = (([TotalDays] * [DailyBudget]) + ([MgtFee] * [TotalDays] * [DailyBudget]) + ([SetUpFee]))

COPY THAT CALCULATED DATA TO A FIELD IN THE TABLE

Private Sub Update_Click()
‘update records to parent form
[CustMgtFee] = [MgtFee]
[ExpiryDate] = [EndDate]
[CustDailyBudget] = [DailyBudget]
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.

[Forms]![WebDesign]![WebSiteCost] = ([TotalPrice])

Like this on a close button:

[Forms]![WebDesign]![WebSiteCost] = ([TotalPrice])
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

Tags: ,

No comments yet.

Leave a Reply