Tips and code for Access VBA available on the web
The links on this page direct to the best available sources found on the web, including Microsoft Office developer documentation, Allen Browne and The Access Web. When possible solutions have been included as fragments in the fragments library included in the Code VBA knowledge and productivity add-in which can be downloaded here. In selecting the pages to link to I made a personal judgment on what I consider useful. The links have been grouped in themes which speak for themselves. Whenever a link title in itself is not sufficiently clear I've added a short explanation on what to expect.
Working with Access Forms using VBA
Opening and closing forms
- Open an Access Form - using the DoCmd.OpenForm arguments: view, datamode, filtername, where condition and openargs.
- Close an Access Form - save record before closing form, Ask the user for confirmation before closing the form, close all open forms, save changes to the Form object
- Keep something open - after closing a form, probably a kind of 'Switchboard' form.
Navigating Form Data
- Move to Record using DoCmd - GoTo First, Next, use of Offset. Also Using DoCmd.GoToRecord to navigate a subform and how to disable navigation buttons if at the first or last record in a form
- Filter a Form on a combobox or field in a Subform
- Change the Filter or Sort Order of a Form or Report - After a form or report is open, you can change the filter or sort order in response to users' actions by setting form and report properties.
- Return to the same record next time form is opened
Changing Form Data
- Duplicate the record in form and subform
- Assign default values from the last record
- Locking bound controls on a form and subforms to prevent changing data accidentally in Access
- Ask confirmation when the User Deletes a Record (Form_BeforeDelConfirm)
- Perform data validation checks when editing a record
Other
- Managing Multiple Instances of a Form
- Default Forms, Reports, and Databases
- Print the record in the form
- To set a property of a form or report - different syntax and which is faster
Events
Controls
Refer to controls
Textbox
- Unbound text box: limiting entry length
- Automatically place the cursor at the end of the text
- Prevent #Error when the Subform has no records
Combobox / drop down list
- Using a comboBox to find records
- Combos with tens of thousands of records
- Limit content of combo/list boxes
- Adding values to lookup tables
- Drop down when the control receives the focus
Subform
Section
Working with Access Reports
- Open Access Report - using the DoCmd.OpenReport arguments: view, filtername, where condition and openargs.
- Limiting a Report to a Date Range
- Print the record in the form
- Close report automatically if no data found
- Data functions DLookup, DSum and others
Fields
Managing data using DAO/ACE
