Examples to part out

Database of Access 2000 sample forms

The sample forms in this database demonstrate a variety of form types and techniques, including how to manipulate data, use controls, and create undo and redo operations.

• Bring a subtotal from a subform to a main form
  
• Create a running sum
  
• Create a stopwatch form
  
• Display line numbers on subform records
  
• Fill current record with data from previous record automatically
  
• Hide the combo box drop-down arrow
  
• Simulate drag-and-drop capabilities

Database of Access 2000 sample reports

The sample reports in this database demonstrate a number of techniques, including how to shade every other row or every nth row in a report, how to create a table of contents or an index for a report, and how to create a top 10 report.

Labels: Access

Everything's connected

"Cascading combo boxes are used to help aid the user in determining a choice when entering data into a form.

They help to breakdown the options available into selectable sub-groups such a State & City, Product Type & Product, Department & Employees.

The user can select, for example, Customer Services in a combo box and the second combo box's list will reflect this selection and only list those employees within Customer Services.

Switch to Finance and all the Finance employees become selectable, etc."

[Edited entry from 12/29/2004]




See all Topics

Labels: Access

Multiple selections

"How to open a report based on a multiple selection in a list box. For example, you may have a list of makes of car and a report that shows various details for each make of car. You want to allow the users to select a range of cars and pull up the report according to that selection."

Microsoft KB:
How to Use a Multi-Select List Box to Filter a Form

Customize a list box, combo box, or drop-down list box

[Edited entry from 12/6/2004]




See all Topics

Labels: Access

Automatic Input masks

Private Sub Country_AfterUpdate()
Dim strCountry As String
strCountry = Me.Country

    Select Case strCountry
        Case "Canada"
            Me.[PostalCode].InputMask = ">L0L\ 0L0;;_"
        Case "USA"
            Me.[PostalCode].InputMask = "00000-9999;;_"
        Case Else
        'If the country is not Canada or USA no input mask will be used
            Me.[PostalCode].InputMask = ""
    End Select
End Sub

comp.databases.ms-access forum

Working with postal codes in Access

As a rule, if you won't be performing numeric calculations on the data, entries should be stored as text. Social Security numbers, Phone numbers and postal codes should be stored as text.


You can use alphabetic characters in an input mask. For example, one of the sample input masks is >L0L\ 0L0 used to represent a Canadian postal code.

The ">" character in the input mask converts all the characters that follow to uppercase.

The "L" character requires an alpha entry; the "0" (zero) requires a numeric entry.

A "\"character causes the following character to be displayed as a literal character rather than a mask character.

A space appears between the three character pairs.
For example, V5P 2G1 is one valid postal code that the user could enter. The mask would prevent the user from entering two sequential alphabetic characters or numbers.

See:

Trinity University - San Antonio, Texas:
Input mask

Definition characters used to create an input mask
Some validation rules

You can manipulate postal codes in Access by changing the data type, input mask, or format of a postal code field.

Microsoft KB 207829:
ACC2000: How to Manipulate ZIP Codes in Microsoft Access.

Also see:
Postal Codes

[Edited entry from 11/12/2004]




See all Topics

Labels: Access

Drag drop trick

There is another way to do this.

Drag the table or query object's icon from the Database window/Navigation pane directly to the top half of the query design grid.

You can also use this technique in Access's Relationships window

[Edited entry from 12/13/2004]




See all Topics

Labels: Access

Who's counting?

2004 has 53 weeks. January 1 is the only day in the first week of 2005. Week 2 starts on Sunday 1/2/2005.

Chip Pearson is the Date and Time guy:
Week Numbers In Excel

"Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec."

The first week of 2005 should start on January 3. The first and second would be part of week 53 of 2004.

Wikipedia:
Week Dates

If your week starts on a different day, you can use the Analysis ToolPac function:
=WEEKNUM(A1, 2) for a week that starts on Monday, =WEEKNUM(A1) if it starts on Sunday.

Also this from ExcelTip.com:
Weeknumbers using VBA in Microsoft Excel

"The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer."

In Access:
DatePart Function

If your work week is always Saturday through Friday then

datepart("ww",[DateField],7,1)

will return 1 for 1/1/2005 through 1/7/2005, 2 for January 8-14/2005, etc.
Otherwise use 1 for Sunday through 7 for Saturday.

The last number sets these parameters:

1, Start with week in which January 1 occurs (default).
2, Start with the first week that has at least four days in the new year.
3, Start with first full week of the year.

[Edited entry from 12/2/2004]



See all Topics

Labels: Access, Excel

A second chance

"In Microsoft Office Access 2007, by default, users are not prompted to confirm changes after modifying and saving records on a form. But often you might want to prompt users to confirm their changes before the record is saved.

You can use a BeforeUpdate event procedure to display a confirmation prompt and handle a user's response to either cancel or continue with the save.

This visual how-to topic illustrates how to display a custom dialog box to prompt users to cancel or continue with saving changes to a record.

Labels: Access

Sent from Access

A recent issue has information about sending reminders to Outlook from Access.

"If you have a table that contains a date field, and you want to make sure that something happens on that date, one way is to create an Outlook task with a reminder that will pop up on the specified date; you can even use the Outlook reminder to create an email message that will be sent on the specified date.

This article will show how to create an Outlook task from Access VBA code, and send an email message when the task's reminder fires."

The file is located on Helen Feddema's site.
Access Archon
Scroll down to #126

The zip file contains the WAW article, in Word format, plus the supporting file.

Helen Feddema has been working with Word since v. 1.1, Access since the beta of v. 1.0, and Outlook since the beta of v. 8.0 (that's where Outlook started its version numbering).

Labels: Access, Outlook

Choose your own location

Word

you can change the default location by going to Tools>Options. On the "File Locations" tab you can modify the storage location.

Excel

Tools>Options. On the "General" tab change the default location.

PowerPoint

uses Tools>Options and the "Save" tab.

Access

Tools>Options and the "General" tab for Databases and Projects

Publisher

Tools>Options "General".

Outlook

will make you take an underground tour into the Registry to change the location to save e-mail attachments.

FrontPage/Expression Web

appears to require the same sort of spelunking.

Labels: Access, Excel, Outlook, PowerPoint, Word

Good combo

"While working with forms, a split form can be a very useful view because you simultaneously get two views of the form that are connected to the same data source.
This demo shows you how to create a split form view where you can use the datasheet part of the form to quickly locate a record and the form portion to view or modify the record.

You will also learn how to enhance and customize a split form view to suit your needs."

Labels: Access

Access tidbits

Labels: Access

More dimensions than Star trek

When a company accumulates a great deal of information, it becomes un-wieldy to work with just basic Excel or Access databases.

There is a database concept called on OLAP cube (On-Line Analytical Processing).

This multidimensional collection of data can be thought of as a 3-D pivot table viewed from flat land.

MSDN:
Just What Are Cubes Anyway?
(A Painless Introduction to OLAP Technology)

OLAPReort.com:
What is OLAP


Wikipedia:
OLAP

Wang.se (Wang Sweden) a Swedish software company:

Create an OLAP Cube

[Edited entry from 12/9/2004]


See all Topics

Labels: Access, Excel

Speed up table creation with default field settings

When you add fields to a table, Access assumes you want to use a 50/255-character Text field by default. However, you may typically use a smaller field size or you may personally use Number fields more often than Text ones.

You can avoid having to change the size and data types for new fields by setting defaults that are appropriate to your own design habits. To do so:

1. Choose Tools> Options from the menu bar and switch to the Tables/Queries sheet.
   
   
2. Select the data type you use most from the Default Field Type dropdown list.
   
   
3. Set the Text and Number sizes you usually want to use in the Default Field Sizes panel and
   
   
4. Click OK.

Labels: Access

Go back

"When you use Windows Explorer or the desktop to create a new 2007 Microsoft Office file, a new Office file is created in an XML file format (.dox or .xlsx). For example, this behavior occurs when you right-click the desktop, you point to New, and then you click Microsoft Office Word Document. By default, files that you create in the 2007 Office system are in XML file formats.

This article is about how to create legacy Office files, such as .doc files, .xls files, .ppt files, or .mdb files in the 2007 Office system. You can create legacy Office files without opening any Office applications. To do this, you must modify some settings. The modified settings will apply to all the users who log on to the computer."

Labels: Access, Excel, PowerPoint, Word

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])

MsgBox "Hello World!", , "My Message Box"

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

Labels: Access, Excel, PowerPoint, Word

Without navigation

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.

[Edited entry from 11/27/2004]




See all Topics

Labels: Access

Drag 'em on over

Labels: Access

Table sorts

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)

Florida Gulf Coast University:
Sorting and Filtering

eHow.com:
How to Filter Records in a Microsoft Access Table

Microsoft Kb:
How to filter records in an Access database

[Edited entry from 11/20/2004]




See all Topics

Labels: Access

Question and collection

"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 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.

Labels: Access, Outlook

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.


Sam's Publishing:
Taking More Control of Access
By Gordon Padwick.


Access 2007 introduces 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.


[Edited entry from 11/6/2004]




See all Topics

Labels: Access

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: Access

Fewer steps

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.

Labels: Access

Avoid the dark spots

A "sandbox" allows the kids to play in a confined area. They can't get into as much trouble as when they are allowed to run around the park.


Frequently asked questions about Access security warnings

"To help enhance the security of your data, you have the option of running Access 2003 in sandbox mode. In sandbox mode, Access evaluates only those expressions in field properties and controls that are safe. An expression is considered safe if it does not use functions or properties that could be exploited by malicious users to access drives, files, or other resources for which they do not have authorization. For example, functions such as Kill and Shell could be used to damage data and files on a computer, so they are not considered safe.

Sandbox mode is implemented by using the Microsoft Jet Expression Service to evaluate expressions. Microsoft Jet 4.0 Service Pack 8 (SP8) or later allows Access to be fully functional, yet still have Jet block unsafe expressions by enabling sandbox mode. However, the Jet expression service is not part of Access. You need to download and install Microsoft Jet 4.0 Service Pack 8 (SP8) or later before you can enable sandbox mode. "

You will see this box if your computer does not have Microsoft Jet 4.0 Service Pack 8 (SP8) or later installed, and/or sandbox mode has not yet been enabled on your computer.

Installing the latest Jet service pack does not automatically turn on sandbox mode and block unsafe expressions. You need to click Yes in the message box to complete the process of enabling sandbox mode.

If you enable sandbox mode without installing Jet 4.0 SP 8 or later, certain features will not work. For example, wizards will not start, and instead, you will see the message "This feature isn't installed, or has been disabled".

About Microsoft Jet Expression Service sandbox mode

You may also see this warning.

Access cannot verify that the file is from a trusted source and that it has not been tampered with since you received it or last worked on it.

You can avoid this bother by digitally signing your file or reducing security to the Low level. This effectively puts Access back on the playground.



Microsoft warns:

"Unless yours is a stand-alone computer (not part of a network), and you don't ever connect to the Internet, and no one ever has physical access to your computer, or your databases and other files are protected with strong passwords, you should not even consider setting the macro security level to Low."

There is another way to do this. Via registery key.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines]
"SandBoxMode"=dword:00000003

2007 has fewer warning messages. Earlier versions of Access forced you to deal with a variety of alert messages - macro security and sandbox mode, just to name two. By default, if you open an Office Access 2007 database outside of a trusted location, you see a single tool called the Message Bar.

VB123.com:

How To Avoid An Access 2003 Sandbox Storm
By Garry Robinson

AccessAdvisor.com:
Microsoft:

How to configure Jet 4.0 to prevent unsafe functions from running in Access 2003

[Edited entry from 10/27/2004]



See all Topics

Labels: Access

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: Access

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: Access

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

Labels: Access, Excel, Outlook, PowerPoint, Word

Easy additions

Labels: Access

Things you need to know

Labels: Access

Brand your app

Labels: Access

Show something

 Like [Enter Name] & "*"

[Enter Name]

Labels: Access

Office Wide

"Automation (formerly known as OLE Automation) is a feature of the Component Object Model (COM), an industry-standard technology that applications use to expose their objects, methods, and properties to development tools, macro languages, and other applications.

For example, a spreadsheet application might expose a worksheet, chart, cell, or range of cells--each as a different type of object. A word processor might expose objects such as an application, document, paragraph, bookmark, or sentence.

When an application supports Automation, the objects that the application exposes can be accessed through Visual Basic. You can use Visual Basic to manipulate the objects by invoking methods or by getting and setting properties of the objects."

Here's an example:

Inserting Data into a Microsoft Word Document

With Automation code, you can open a Microsoft Word document and move to a bookmark location in the document. The following example opens a Microsoft Word document and inserts text after a bookmark.

This example assumes that you have Microsoft Word on your computer, that you have an existing document called C:\My Documents\WordTest.doc, and that the document contains a pre-defined bookmark named City.

Sub FindBMark()

   Dim wordApp As Word.Application
   Dim wordDoc As Word.Document
   Dim wordRange As Word.Range

   Set wordApp = CreateObject("Word.Application")
   Set wordDoc = wordApp.Documents.Open("C:\My Documents\Wordtest.doc")

   wordApp.Visible = True

   ' Go to the bookmark named "City".
   Set wordRange = wordDoc.Goto(What:=wdGoToBookmark, Name:="City")
   wordRange.InsertAfter "Los Angeles"

   ' Print the document.
   wordDoc.PrintOut Background:=False

   ' Save the modified document.
   wordDoc.Save

   ' Quit Word without saving changes to the document.
   wordApp.Quit SaveChanges:=wdDoNotSaveChanges

   Set wordApp = Nothing

End Sub

Microsoft Support provides an entire Help file to assist you. It includes theory and examples.

The file is called XPAutomation.chm.

Download it and then double click on the file to run it. You could also set up a shortcut on the desk top, if it will be used frequently.


Microsoft Knowledge Base Article: 302460

This was aimed at Office 2002 but it can be used with later versions:

• Microsoft Access
  
• Microsoft Excel
  
• Microsoft Outlook
  
• Microsoft PowerPoint
  
• Microsoft Word

Labels: Access, Excel, Outlook, PowerPoint, Word

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."

Labels: Access

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."

Labels: Access

Reformat reports

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 2000 with automation

Teachers on the web: Aussie SchoolHouse:
Merging Access Data with Word Documents

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

[Edited entry from 10/3/2004]




See all Topics

Labels: Access

Old command locations

Microsoft has issued great "Getting Started" flash programs that help you find 2003 commands in 2007.

Now they have one for Access:
Interactive command reference guide

While it does not provide the Getting Started tab like is available in Word, Excel, and PowerPoint, it is helpful.

Getting Started with '07

There is also one for Outlook:
Getting started in Outlook '07


See all Topics

Labels: Access

Just a keystroke away

[Ctrl][Alt][Spacebar]