Enter your email address:

Delivered by FeedBurner

Use your pdf converter to make your pdf files easy! You can now buy software that makes converting pdf to doc possible! Did you know you can even convert pdf to word?
Home Page








Subscribe here
Add to 

My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!

eXTReMe Tracker
  Web http://www.klippert.com

  Thursday, March 22, 2018 – Permalink –

VBA Named Arguments

An easier read

Use named arguments for cleaner VBA code.

Most likely, you use positional arguments when working with VBA functions. For instance, to create a message box, you probably use a statement that adheres to the following syntax:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

When you work the MsgBox function this way, the order of the arguments can't be changed.

Therefore, if you want to skip an optional argument that's between two arguments you're defining, you need to include a blank argument, such as:
MsgBox "Hello World!", , "My Message Box"

Named arguments allow you to create more descriptive code and define arguments in any order you wish. To use named arguments, simply type the argument name, followed by :=, and then the argument value.

For instance, the previous statement can be rewritten as:

MsgBox Title:="My Message Box", _
Prompt:="Hello World!"

(To find out a function's named arguments, select the function in your code and press [F1].)

See all Topics


<Doug Klippert@ 3:53 AM

Comments: Post a Comment

  Tuesday, March 13, 2018 – Permalink –

Display the Current Record Number

Without navigation

You may want to remove the navigation buttons from an Access form but still display the current record number. Not the ID or serial number, but the record number that would appear in the navigation box.

To provide this feature, you can use VBA to place the form's CurrentRecord value in an unbound text box, and then update the value during the Current event.

To utilize this property, add an unbound text box to your form in Design view. Then, on the Event tab of the form's Property list, click the ellipsis or Build button. Choose Code Builder.

Add the following code in the Visual Basic Editor:

Private Sub Form_Current()
MyTextBox = Me.CurrentRecord
End Sub

(where MyTextBox is the name of the control that displays the record number.)

Now, when you navigate from record to record, the MyTextBox control will update automatically to reflect the current number.

See all Topics

Labels: , , , , , , ,

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

  Saturday, March 10, 2018 – Permalink –

Quick Subforms and Subreorts

Drag 'em on over

When you need to create a subform or subreport, you probably use the Subform/Subreport tool from the Toolbox to draw where you want to add the control.

You can also create subform and subreports using drag and drop.

Simply open the main form or report in Design view, then drag the appropriate form or report from the Database window to where you want the control created.

Note that you'll still need to set Link Child Fields and Link Master Fields properties on the new control.

See all Topics

Labels: , , , , , , ,

<Doug Klippert@ 3:32 AM

Comments: Post a Comment

  Tuesday, March 06, 2018 – Permalink –

Filter Multiple Fields

Table sorts

In a table's Datasheet view, you can filter multiple selections in a few simple ways.

In the first method, you select one of the fields you want to filter by and click the Filter By Selection button on the toolbar. Access filters the records by that selection. Next, select the second field you want to filter by and click the button again. Access filters the records even further by this second selection. Continue to follow these steps until you have filtered by the desired number of selections.

You can also use Filter by Form. Click the icon on the toolbar. In the displayed form, enter the criteria. You can use And/Or statements, such as "Boston" Or "San Francisco".
(Access will enter the quote marks for you.)

For the third option, you use the Advanced Filter>Sort feature. To do so, select Records>Filter>Advanced Filter>Sort from the menu bar. Access displays a grid similar to the Query By Example grid. Now, drag down all the fields you want to filter by from the field list. Then, in the Criteria cells enter the values you want to filter for. When you have finished, click the Apply Filter button to see the results.

Fourth, you could use Filter by input. Right click any entry in a field you want filtered. Enter the value in the Filter For box and hit Enter. You could then choose another field and sort again by another criteria.
Creating a Query would let you save your filter, but these methods can be used to quickly display the desired information in a table.
(In 2007+ these options are on the Home tab in the Sort & Filter group)

How to Filter Records in a Microsoft Access Table

Microsoft Kb:
How to filter records in an Access database

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:19 AM

Comments: Post a Comment

  Saturday, March 03, 2018 – Permalink –

Email Access Form

Question and collection

Access 2007-10 has a wizard that will walk you through the process of sending an information gathering form through Outlook. The wizard is on the External Data tab in the Collect Data group.

"You begin with the Collect Data Through E-mail Messages Wizard, which guides you through the steps of creating a form.

The form is sent through Microsoft Office Outlook 2007-10 to your recipients, with your request for new or updated information.

When the recipients reply to your message, Access automatically enters their data into your database.

Collect data by using e-mail

See all Topics

Labels: , , , , , , ,

<Doug Klippert@ 3:41 AM

Comments: Post a Comment

  Tuesday, February 20, 2018 – Permalink –

Convert Access Macros to VBA

Macros to Modules

Before Access 2000, the speculation was that Access would lose "Macros" and enter the exclusive world of VBA. It hasn't happened yet.

If you have macros in a database that you would like to convert to code, doing so is easy.

In Access 97: Right-click on the macro in the Database window and then choose Save As/Export from the shortcut menu. Then, select the Save As Visual Basic Module option button and click OK. You are then given the option of adding error handling functions and comments to the new module. Select the options you want and click Convert.

In Access 2000/2002+: Right-click on the macro in the Database window and then choose Save As from the shortcut menu. Enter the name of the module you want to create in the text box and choose Module from the As dropdown list. Next, click OK. You will be given the option of adding error handling functions and comments to the new module. Select the options you want and click Convert.

In 2007 go to Database Tools and look in the Macros group.

Pearson Informit:
Taking More Control of Access
By Gordon Padwick.

Access 2007 introduced a new type of macros called embedded macros. Embedded macros are macros that are stored on an event instead of as a separate object. Embedded macros support name fix-up and are used extensively through-out our templates. They are largely targeted to information workers that don’t write code but useful for developers that are trying to perform some simple actions.

See all Topics

Labels: , , , , , , ,

<Doug Klippert@ 3:21 AM

Comments: Post a Comment

  Sunday, February 18, 2018 – Permalink –

What the ####

Truncated Numbers

Access has a new option that will show octothorps when the column is too narrow to display the entire value. When this option is not enabled, you see only part of the values in a column rather than ####.

You'll find the selection under Access Options when you click the Office button.
Go to Current Database and make your choice.

See all Topics

Labels: , , , , , ,

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

  Sunday, February 11, 2018 – Permalink –

Copy Access Data to New Records

Fewer steps

The Paste Append feature is often overlooked in Access.

This feature lets you quickly create new records that copy existing information from other records.

To see one way to use the feature, open a table in Datasheet view.
  1. While holding down the [Shift] key, select adjacent fields with data you want to copy. You can also select fields from adjacent records.
  2. When you've finished, press Ctrl+C to copy the data.
  3. Then, choose Edit>Paste Append (Paste>Paste Append in 2007+)
  4. Click Yes when Access asks for confirmation.
You'll now have an appropriate number of new records in the table that contains the information you copied.

See all Topics

Labels: , , , , ,

<Doug Klippert@ 3:21 AM

Comments: Post a Comment

  Friday, February 09, 2018 – Permalink –

Security Questions


This article provides an overview of the security features offered by Access 2009-2013, and explains how to use the tools that Access provides for helping to secure a database. This article also links to more detailed content about various security features.
  • What's new in Access security
  • Use an Access database in a trusted location
  • Package, sign, and distribute an Access 2010 database
  • Enable disabled content when you open a database
  • Use a database password to encrypt an Access database
  • How security works with databases from earlier versions of Access opened in Access 2010
  • Run unsafe expressions (disable sandbox mode)

Introduction to Security

See all Topics

Labels: , , ,

<Doug Klippert@ 3:11 AM

Comments: Post a Comment

  Thursday, February 01, 2018 – Permalink –

Resize Form

It's fitting

When you switch between Design and Form views, the size of the form is dictated by the size of the Design view window, not the size of the form sections.

You often need to expand the window to be able to see the rulers and scroll bars in addition to all of the sections. This means you're left with wasted space when viewing the form in Form view, assuming that you forget to shrink the window back down.

A solution to this annoyance is to use the Size To Fit Form feature.

Simply view the form in Form view and choose Window>Size To Fit Form from the menu bar. If your view of the form is maximized, the menu option will be unavailable and you'll need to click the Restore Window button on the form window to enable the choice.

Once Access has resized the form, you can save its current dimensions by clicking the Save button.

With Access 2007+ go to the Office button, choose Access Options and click Overlapping Windows. The Size To Fit Form icon will appear on the Home tab.

See all Topics

Labels: , , , , , ,

<Doug Klippert@ 3:01 AM

Comments: Post a Comment

  Thursday, January 25, 2018 – Permalink –

Quickly Query Table Names

Change by code

If you've ever developed a dozen or more complex queries, then had to change one of the table names, you know how frustrating it can be to all but rebuild the queries in the Query Design view grid by changing the table name in each cell.

One quick alternative is to choose View >SQL View while the query is open and then cut and paste all the SQL code into Word.

Next, do a Find and Replace, changing all the instances of the old table name to the new table name.

Finally, copy and paste the SQL statements back into the SQL view of your Access queries.

When you go back to the QBE, the new table will replace the old one.

See all Topics

Labels: , , , , , ,

<Doug Klippert@ 3:06 AM

Comments: Post a Comment

  Tuesday, January 23, 2018 – Permalink –

Office VBA Tricks

Video + Free code

"Learn tips and use sample code for several Office applications. These tips can help you to be more productive and can also be a starting point for developing your own tools, utilities and techniques."
  • Update Word Document Statistics in the Title Bar
  • Create Outlook Rules Programmatically
  • Delete Repeated Text Throughout a Word Document
  • Run Macros Based on the Value of One or More Excel Spreadsheet Cells
  • Disable Related Controls on a PowerPoint Slide After a User Clicks an Input Control
  • Display Reminder Information When a User Opens an Office Document
  • Synchronize an Access Main Form to a Subform and Vice Versa
  • Log Worksheet Changes to an XML File
  • Merge Body Text from Multiple Outlook E-mail Messages to a Word Document
  • Use the Office Assistant as an Alternative to Displaying and Retrieving User Input
Ten Tips for Office VBA Developers

VBA Tips & Tricks

Getting Started with VBA in Office 2010

Download Office 2013 VBA Documentation

(VBA is VBA and is, in most cases, usable in all versions of Office)

See all Topics

Labels: , , ,

<Doug Klippert@ 3:01 AM

Comments: Post a Comment

  Saturday, January 20, 2018 – Permalink –

Add Objects to the Query Grid

Easy additions

If you need to add a table or query to a query you're building in Design view, you most likely click the Show Table button, drag the appropriate objects from the resulting dialog box, and then close the dialog box.

However, there's a much easier way to do this.

Simply drag the table or query object's icon directly to the gray background of the query design grid. This same technique also works with the Access Relationships window.

See all Topics

Labels: , , , , ,

<Doug Klippert@ 3:16 AM

Comments: Post a Comment

  Sunday, December 24, 2017 – Permalink –

Null Parameter

Show something

If a user doesn't specify a parameter value, you can use a wildcard with the parameter in the format
Like [Enter Name] & "*"

The problem with this is that the query will return records that partially match the criteria.

For instance, if users searching for records based on last name enter a parameter value of "Smith" they'll also get the records for Smithers, Smithfield and Smithson.

Another problem is that the parameter query will ignore any records where the field being searched contains a Null value when you try to return the entire recordset with a blank parameter.

To fix this, set up a query to limit responses to explicit parameter entries, but still allow users to return all records by leaving the parameter blank.

If you're searching for LastName, open the query design grid and add LastName to it.

In the Criteria row for the field, enter the parameter prompt
[Enter Name]

Then, in the next blank column of the design grid, enter the same parameter (everything between and including the square brackets) in the Field text box.

Finally, in the Or row, enter the criteria Is Null .

If you're using any additional criteria for other fields, make sure to copy that criteria to the Or line as well.

See all Topics

Labels: , , , , ,

<Doug Klippert@ 3:56 AM

Comments: Post a Comment

  Monday, December 11, 2017 – Permalink –

Change Access Ribon

Oh, Fooey (F U I)

"One of the most exciting new developer features that Microsoft Office Access 2007 provides is the ability to customize the Office Fluent User Interface (UI) in your application.
The Office Fluent UI provides a new user model for exposing commands, and application navigation that is more discoverable and easier for users of the application.

You create XML to change the Ribbon, a component of the new Microsoft Office Fluent user interface (UI). You can create customization files in any text editor.

All applications that include the Office Fluent Ribbon use the same extensibility model, so you can reuse the same Office Fluent UI extensibility XML with a minimum of adjustments.
For example, you can reuse the custom XML you create for Access 2007+ in Microsoft Office Excel 2007+, Microsoft Office Word 2007+, Microsoft Office PowerPoint 2007+, or Microsoft Office Outlook 2007+."

Customizing Ribbon in Access 2007

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:24 AM

Comments: Post a Comment

  Thursday, December 07, 2017 – Permalink –

Access Developer Extensions

No cost tool

"The Access Developer Extensions include the following components:

  • Save As Template
    Enables you to create database templates (ACCDTs) that can be featured in the Access 2007 Getting Started screen.

  • Package Solution Wizard
    A wizard that creates a Windows Installer Package (MSI) to install your database and any supporting files and optionally includes the Access 2007 Runtime, or prompts the user to download the Access 2007 Runtime.

  • Source Code Control
    Integration with Microsoft Visual SourceSafe or other source code control systems to allow check-in/check-out of queries, forms, reports, macros, modules, and data. You can also see the differences that have been made to your checked out objects."
 Developer Extensions

See all Topics

Labels: , , ,

<Doug Klippert@ 3:31 AM

Comments: Post a Comment

  Tuesday, December 05, 2017 – Permalink –

Send Access to Word

Reformat reports

Not everyone has Access installed on his or her machine.
Access is not the most versatile instrument for complex formatting.

If you wish to share your findings, Access does have the ability to re-format Reports into Word documents.

Open the Report in Access and go to Tools>Office Links.
One of the choices is to "Publish It with Microsoft Word."
Here's the command in 2007+:

When the data is sent to Word, you will be asked to confirm that you wish to convert the file to "Rich Text Format (RTF)" Click OK.

(RTF is a "universal" format. The Report can be re-saved as a Word "DOC" or "DOCX" file.)

Microsoft KB:
How to send the current record to Word with automation

Use a table or query as a mail-merge data source (2007+)

See all Topics

Labels: , , , , ,

<Doug Klippert@ 3:07 AM

Comments: Post a Comment

  Friday, December 01, 2017 – Permalink –

Update Data to Default

Just a keystroke away

You probably know that you can set up a default value for Access to enter into a field when a new record is created. This can be done in the Design view for a table or form by setting the Default Value property.

Unfortunately, you sometimes may set a default value after you've already entered records into the database. When you do so, the existing records aren't automatically updated to equal the new default.

However, if you're editing a record and you want to update the field to the current default, you can do so with a keystroke shortcut. To do so, simply select the appropriate field and press


See all Topics

Labels: , , , ,

<Doug Klippert@ 3:54 AM

Comments: Post a Comment

  Saturday, November 25, 2017 – Permalink –

Print Raw Data

From reports and forms

Sometimes, you may not want to go to the effort of creating a report; you just need a quick hardcopy of data.

In such cases, you can simply print the Form view of your data. However, doing so also prints the background and shading associated with the form.

If you just need a quick data reference, you probably don't want to waste the resources and time to print such a detailed view. Fortunately, Access has a feature that lets you quickly print just the data from a form or report.

To do so, view the data you want to print in the form or report.

Then, choose:

Office button>Print>Print Preview (File >Page Setup)

select the Print Data Only check box

and click OK.

Doing so hides any graphics, lines, control borders, and label controls so that the print out simply contains data.

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:02 AM

Comments: Post a Comment

  Thursday, November 23, 2017 – Permalink –

Zoom Box Fonts


Change the appearance of text in Access' Zoom box

Access' Zoom box, allows you to display the contents of a text box in a dialog box for easier editing and viewing. Although viewing long expressions is more convenient in the Zoom box, it is still sometimes difficult to follow what is displayed due to the font Access uses by default.

Access 20xx allows you to change the font that the Zoom box uses. To display the Zoom box, select the text box you want to expand and press Shift+F2. Then, just click the Font button, set the options you want, and click OK. The settings you select are used whenever you display the Zoom box during your current instance of Access. However, the next time you start Access the Zoom box font settings will revert to their defaults

Zoom Box Font

See all Topics

Labels: , , , , ,

<Doug Klippert@ 3:36 AM

Comments: Post a Comment

  Sunday, November 19, 2017 – Permalink –

Runtime and PDF

Add-in's complete

Great news, you can now redistribute and use the PDF and XPS add-in with your Runtime solution.
The Runtime's code has not been changed. Your existing runtime solutions can now officially be distributed with the PDF and XPS add-in by chaining the .msi for PDF and XPS into your install process for your app (after the Runtime).

You may copy and distribute the object code form of the add-in listed below together and for use only with the Microsoft Office Access Runtime software, subject to the license terms accompanying the Microsoft Office Access 2007 Runtime software download:
2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS (located at PDF add-in download)

Runtime for Access

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:40 AM

Comments: Post a Comment

  Monday, November 13, 2017 – Permalink –

Prevent Users From Copying Field Text

Copy or select

As you know, you can set a form field's Locked property to Yes to prevent users from changing the underlying data. However, users are still able to select and copy data from the field, and you may not always want this to be the case. The solution is to also set the field's Enabled property to No.

Ordinarily, setting the Enabled property this way causes the field and its associated label to be difficult to read. However, when you set the Enabled property to No and the Locked property to Yes, the fields and labels look exactly as they do when they're enabled for normal entry. The difference is that users will be unable to select or copy any of the displayed data.

See all Topics

Labels: , , ,

<Doug Klippert@ 3:45 AM

Comments: Post a Comment

  Friday, November 03, 2017 – Permalink –

Total Footer

Sum() it up

If you would like to show a total in the footer or each page of a report, you may have a problem. Access does not allow the SUM() function in the footer.

The way around this is to put a SUM() function in an unbound text box in another part of the report.

Choose Properties and set the visible property of the control to No.

In the footer, create another control using the "calculation" text box as the ControlSource.

The Sum() function, as well as the other aggregate (totals) functions can reference only a field and not a control.

From the Microsoft Knowledge base:

How to Sum a Calculation in a Report

How to Display and Total Subtotals from Subreports

See all Topics

Labels: , , , , ,

<Doug Klippert@ 3:12 AM

Comments: Post a Comment

  Thursday, October 26, 2017 – Permalink –

Insert Line Breaks With Code

Label Captions

If you've ever needed to insert line breaks in a message box prompt, you most likely built a string that incorporated a line feed or carriage return character. Unfortunately, label objects aren't as forgiving when it comes to using these characters.

If you're setting a label's Caption property with code, you'll find that the special control characters are interpreted as squares, since they're otherwise un-displayable.
To successfully insert a line break in a label caption, you need to include both a line feed character and a carriage return character, entered consecutively.

To do so, you can use the Chr() function, such as:

Me.Label1.Caption = "Line 1" & _
Chr(13) & Chr(10) & "Line 2"

However, you can also simplify your code using an built-in constant:
Me.Label1.Caption = "Line 1" & vbCrLf & "Line 2"

See all Topics

Labels: , , ,

<Doug Klippert@ 3:06 AM

Comments: Post a Comment

  Wednesday, October 25, 2017 – Permalink –

Zoom Box

Better view

Access does not provide much room to enter long expressions in queries, forms, or reports.
You can drag the column wider, but there is a neater, quicker method.

With the insertion point in the field, hit: SHIFT+F2.
A Zoom box opens. Enter the formula and hit OK.

New Folders

BTW: If you enter Field names in the Zoom box without square brackets, if the fields are recognized, Access will add the brackets.

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:38 AM

Comments: Post a Comment

  Thursday, October 19, 2017 – Permalink –

Place Controls Where You Want

Works with other apps as well

The Snap To Grid feature is an invaluable tool for aligning controls when you're designing forms and reports. However, when you fine-tune the placement of some controls, you'll probably want to move some of them to positions that aren't exactly aligned with the design grid.

You can temporarily disable the Snap To Grid feature by holding down the [Ctrl] key. Then, you can use your mouse or the cursor arrows to place the controls exactly where you want them.

See all Topics

Labels: , , , , ,

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

  Saturday, October 14, 2017 – Permalink –

Hardcopy Relationships

Document database

When you're documenting your database applications, you may want to include the same visual diagram of your table relationships that's available through the Relationships window.
In Access 2000 thru 2003, this is easy. Simply display the Relationships window as usual and then choose File>Print Relationships from the menu bar. Doing so displays a report preview that you can then print or save.

In 2007-13, to just print out a report, find Database tools on the Ribbon and click on Database Documenter.

Relationships are at the bottom of the All Object Types tab

See all Topics

Labels: , , , , , ,

<Doug Klippert@ 3:17 AM

Comments: Post a Comment

  Friday, October 06, 2017 – Permalink –

Startup Switches for Access

Your choice

"This article shows you how to customize the way that Microsoft Office Access 2007+ starts by adding switches and parameters to the startup command. For example, you can have Office Access 2007+ open a specific file or run a specific macro when it starts."






See all Topics

Labels: , , , , ,

<Doug Klippert@ 3:47 AM

Comments: Post a Comment

  Sunday, October 01, 2017 – Permalink –

Use a Table Alias

to change names in a query

When you need to change the table name referenced in an existing Access query, it can be a pain. This is especially true if the query contains a large number of fields.

You typically might use an alias when you need to relate a table to itself or tables have long or unwieldy names.

If you make a practice of always using aliases in your queries, you can easily change which table is used by changing the one occurrence of the original table name in the query's FROM clause.
  1. To set a table alias in the query's Design view,
  2. Right-click on the table field list and choose Properties.
  3. Then, enter the alias name you want to use in the Alias text box.

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:10 AM

Comments: Post a Comment

  Monday, September 25, 2017 – Permalink –

Access Tutorials

Learning Sites

Microsoft has created a web site for Office. Part of the site is devoted to tutorials touching on:

Word, Access, Publisher , Outlook, PowerPoint, InfoPath , Excel, Visio, OneNote, Expression, and Project.

The Access lessons are located here:

2013 Access Tutorials

Here's another site:

FunctionX Access tutorials

See all Topics

Labels: , , ,

<Doug Klippert@ 3:26 AM

Comments: Post a Comment

  Friday, September 22, 2017 – Permalink –

Flag Access Controls

Tag Property

The TAG property allows you to associate up to 2,084 characters of text with any form, report, section, or control. This is especially helpful when you want to single out a specific subset of controls.

For instance, say that you want to hide certain controls on a form when a user clicks a button.
You can flag which controls will be hidden by entering the word "Hide" (or any other consistent word) in each control's Tag property. Then, attach the following code to the command button's Click event procedure:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "Hide" Then
ctl.Visible = False
End If

See all Topics

Labels: , , , , ,

<Doug Klippert@ 3:16 AM

Comments: Post a Comment