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

Labels: Combo, Tips

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



See all Topics

access

Labels: Forms, General, List, Tips

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




See all Topics

access

Labels: Queries, Tables, Tips

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.



See all Topics

access

Labels: Functions, Tips

Access tidbits

Labels: Tips

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.



See all Topics

access

Labels: Forms, Macros, Tips

Labels: General, Tables, Tips

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: Shortcuts, Tips

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

See all Topics

access

Labels: General, Tips

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

access

Labels: Customize, Forms, Tips

Things you need to know

Labels: General, Tips

Brand your app

Labels: Customize, Tips

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

access

Labels: General, Tips

Customize

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 200x 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

See all Topics

access

Labels: Fonts, Tips

Copy or select

Labels: Tips

Better view

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

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

access

Labels: Entries, General, Tips

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

Labels: Customize, General, Tips

Learning Sites

2007 Access Tutorials

(You will see something different if Office 2003 or 2007 is not installed)

Here are some other sites:

FunctionX Access tutorials

TutorialUSA.com

Access Tutorials, Basic

Bay City Public Schools

Tech Tutorials.net

Zicklin School of Business - Baruch College



See all Topics

access

Labels: General, Tips

Eavesdrop on the experts

Labels: Tips

Simply

When you populate a hyperlink field in a table, you probably cut and paste the URL from your browser into the Insert Hyperlink dialog box.

There's an even easier way to do this using Internet Explorer.

1. First, open the Access table you're updating and Internet Explorer.
   
   
2. Select the hyperlink field you want to create the link in and choose Insert >Hyperlink from the menu bar.
   
   
3. Press Alt+Tab or use your mouse to select the browser window.
   
   
4. Browse to the page you want to link to and then switch back to Access.

Labels: Tips