Gotta start somewhere

Labels: Tips, Tutorials

Ahoy

"We have heard a few of you have missed the ALT D shortcut to open an object in design. As you know, ALT in ribbon apps now allows you to access ribbon shortcuts. Next time you want to open an object in design view, try Control Enter."

Labels: Shortcuts, Tips

Finger it out

After completing this course you will be able to:
Accomplish tasks by using sequential shortcut keys, known as Key Tips, shown on the Ribbon.
Navigate around the Ribbon using the TAB key and arrow keys.
Accomplish tasks by using key combinations — keys you press at the same time - exactly as you've done in previous versions of Office.

Labels: Shortcuts, Tips

Auto Shutdown

1. Open the first form in Design view.
   
2. Double-click the Form Properties button.
   
3. Click on the Event tab.
   
4. Click in the On Deactivate text box.
   
5. Select Event Procedure.
   
6. Click the Build button.
   
7. At the prompt, enter: Me.TimerInterval =1.
   (Try something like 30000 milliseconds)
   
8. Press [Alt][Q].
   
9. In the Form Properties window, click in the On Timer property text box.
   
10. Select Event Procedure.
    
11. Click the Build button.
    
12. At the prompt, enter: DoCmd.Close.
    
13. Press [Alt][Q].
    
14. Save the form.

Labels: Forms, Tips

Bit o' code

Sub ListFields()
Dim dbs As DATABASE
Dim dbfield As Field
Dim tdf As TableDef

Set dbs = CurrentDb
Set tdf = dbs.TableDefs!NAMEOFYOURTABLE

Debug.Print ""
Debug.Print "Name of table: "; tdf.Name
Debug.Print ""

For Each dbfield In tdf.Fields
Debug.Print dbfield.Name
Next dbfield
End Sub

Labels: Tables, Tips, VBA

Belépés

• tbl- Table
• qry- Query
• frm- Form
• rpt- Report

Labels: Tips

Distribute Text Evenly Within Controls

General (Default)

The text aligns to the left; numbers and dates align to the right.

Left

The text, numbers, and dates align to the left.

Center

The text, numbers, and dates are centered.

Right

The text, numbers, and dates align to the right.

Distribute

The text, numbers, and dates are evenly distributed.

Labels: Forms, Properties, Tips

A multidiscipline subject

Labels: Tips

Click to Word, PowerPoint, Excel

1. Open the target document and the Access table that contains a hyperlink field.
   
   
2. Select some of the text at the beginning where you want the hyperlink to jump.
   
   
3. Hold down the Ctrl key, drag the selection to the Access hyperlink field you want to set up.
   
   
4. When you release the mouse button, the previously selected text is used as the hyperlink text and the link becomes active.

Labels: Shortcuts, Tables, Tips

Once is enough

• Set up a query as usual using the design grid.
  
  
• Choose View>Properties from the menu bar to display the Query Properties dialog box.
  
  
• Change the Unique Values property to Yes.

Labels: Customize, Queries, Tips

Access to Excel

Labels: Tips

Report Trick

1. Open the report in Design view.
   
   
2. Select the control that displays repetitive information.
   
   
3. Display the control's Property sheet.
   
   
4. Set the Hide Duplicates property equal to No.
   
   
5. Finally, Save and preview your report.

Labels: Customize, Forms, Tips

What's the Outlook?

"Using Microsoft Access and Outlook together can reduce manual processing of Ordering emails very substantially. I know this because sometimes it would take up to 15 minutes to undertake all the little steps of saving customer details into tables and newsletter lists. Also without software, it was very difficult to explain to other staff members what to do when an e-mail arrived. Now we can process the orders in a couple of minutes when Outlook email arrives in the correct folder."

Labels: E-mail, Tips, Tutorials

Save shoe leather

Labels: Forms, Tips

Only for the brave

Labels: Customize, General, Tips

You have a choice

1. Create a query to use as the RecordSource of your report.
   
2. In query design view, in the Criteria row under your date field, enter:
   
   Between [StartDate] And [EndDate]
   

• Flexible: user does not have to limit report to from and to dates.
  
• Better interface: allows defaults and other mechanisms for choosing dates.
  
• Validation: can verify the date entries.
  

Labels: Forms, Queries, Tips, Tutorials

It's your choice

Labels: Customize, Forms, Tips

Disable AutoExpand

Labels: Customize, Forms, Tips

Access style

• General Protection from Unauthorized Users
  
• Protect your Logon Identity
  
• How to Change your Password in Access
  
• User Logon, Security and Data Integrity
  
• The Access Workgroup Security File
  
• Object Permissions and User Logon
  
• Access Sessions and the Workgroup File
  
• Joining to another Workgroup Security File from within Access
  
• Opening a Database from the Command Line
  
• Opening a Database from a Shortcut
  
• Backup
  
• Compact and Repair

Labels: Tips, Troubleshoot

Make up your own year

BeginFiscalYr = DateSerial(Year(Date), 6, 1)

EndFiscalYr = DateSerial(Year(Date) + 1, 6, 1) - 1

Labels: Functions, Tips

Just add Data

Labels: Forms, Tips

Office 2003 redo

1. 1. Start any Office 2003 program.
   
2. On Help menu, click Detect and Repair.
   
   
   
   
3. Click to select the Discard my customized settings and restore default settings check box, and then click Start.
   
4. Quit the application, and then click Ignore.
   
5. Click OK when you receive the following message:
   Reset of setting to default succeed.

Labels: Tips, Troubleshoot

I'd Like to Make It Clear

• squinted your eyes trying to see the numbers in a PowerPoint presentation?
  
  
• scratched your head at a charity leaflet with an indecipherable pie chart titled 'Where your donation goes' ... and set it aside?
  
  
• missed discussion at a meeting because you were busy trying to figure out the figures?
  
  
• put aside a graph or table, thinking "I'm not good with numbers."?

• what to include: when unsure what numbers are important, people frequently display them all, overpowering the reader with irrelevance.
  
  
• which format to use: the choice between text and table, table and chart, bar and pie.
  
  
• how to use the technology effectively: computer software generates graphs easily, but the results hide your point behind incomprehensible chartjunk.
  
  
• how to explain the information: selecting the right words for titles, columns and captions.

Labels: General, Tips

Good for all levels of user

• Casual Users - basic tips and explanations;
  
• Serious Users - ideas for forms, combos, reports, and code;
  
• Flaws in Access - unfixed bugs that will bite you;
  
• Traps to avoid - problems that may not be obvious;
  
• Specific Applications - a couple of examples where people get stuck;
  
• Tips for xBase developers - for people who used the old dBase;
  
• Functions you can use - code to copy;
  
• Upgrading - issues when changing versions;
  
• Other sites - links to other sites with free Access tips.
  

Labels: General, Tips

A DB two step

Labels: Tips

Don't let Outlook stop you

Outlook 2007, 2003, 2002 and Outlook 2000 SP3 allow the user to use a registry key to open up access to blocked attachments. (Always make a backup before editing the registry.) To use this key:

1. Run Regedit, and go to this key:
   
   

   HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Outlook\Security 

   
   (change 10.0 to 9.0 for Outlook 2000 SP3,11.0 for Outlook 2003, 12.0 for Outlook 2007)
2. Under that key, add a new string value named Level1Remove.
   
   
3. For the value for Level1Remove, enter a semicolon-delimited list of file extensions. For example, entering this:
   
   

   .mdb;.url

   
   would unblock Microsoft Access files and Internet shortcuts. Note that the use of a leading dot was not previously required, however, new security patches may require it. If you are using "mdb;url" format and extensions are blocked, add a dot to each extension.
   Note also that there is not a space between extensions.

 

If you are using this registry entry, a glance at Help>About Microsoft Outlook will show Security Mode: User Controlled above the license information.
After applying this registry fix or using one of the above tools, the user still has to save the attached file to a system drive before opening it. In effect, the fix rolls the attachment behavior back to Outlook 2000 SR-1, with its included Attachment Security Fix.
An end-user cannot bypass this "save to disk" behavior and open the file directly from the mail message, though an Exchange administrator can.

Labels: E-mail, Tips

Link in a snap

1. Choose File>Open from the menu bar
   
   
2. Select the appropriate data format from the Files Of Type dropdown list
   
   
3. Open the file as you would any Access database

Labels: General, Tables, Tips

Clever timesavers

CascadingComboBoxes.mdb

This sample illustrates how to restrict the value of one combo box based on the value of another

EmailingSpecificReports.mdb

This illustrates how to email the same report with different data to a variety of users

RelinkOnOpen.mdb

This sample illustrates how re-link, automatically, tables if the database has been moved

UIDesign.mdb

Shows a variety of samples of good and bad User Interface design techniques

Labels: General, Samples, Tips

Desktop convenience

Labels: Shortcuts, Tips

Table fields on the fly

Labels: Tables, Tips

Multi-Field Keys

1. Hold the Ctrl key and click each field that you want to make up the primary key.
   
2. Choose Edit>Primary Key from the menu bar, or click the Primary Key button on the toolbar

Labels: Tables, Tips

No need to reinvent

1. Choose File>Get External Data>Import from the menu bar
   (External Data tab in 2007, then select Access in the Import group)
   
2. Select the database that contains the tables you want to copy and click the Import button.
   
3. Select the appropriate tables on the Tables sheet
   
4. click the Options button, select the Definition Only option and click OK

1. Select the appropriate table in the Database window.
   
2. In Access 2000 and above, choose File>Export.
   (If you're using Access 2007, choose More in the Export group)
   
3. Click OK.
   
4. Now, regardless of version, select the target database and press Enter

Labels: Tables, Tips

Bean Counting Models

"My intention is to provide a wide range of 'Kick Start' Models that anyone can use as a starting-point, and could extend cleanly and logically, with appropriate reference to the Business Rules.

It is not my intention to provide Models that can be used off-the-shelf to meet the requirements of a large commercial organization.

After all, that is one of the things I do for a living !!!

None of the Models is the complete and final solution in its area, but any of them can be added to easily and quickly to meet a specific requirement. The logic in each Model is intended to be correct and to contain the minimum Entities for the area being modeled."

Labels: Relationships, Tips, Tutorials

Lost functionality

MORE INFORMATION
Because of legal issues, Microsoft has disabled the functionality in Access 2003 and in Access 2002 that let users change the data in linked tables that point to a range in an Excel workbook. However, when you make changes directly in the Excel workbook, the changes appear in the linked table in Access.

"A jury in U.S. federal court found that Microsoft infringed on a Guatemalan inventor's 1994 patent on technology linking the company's Access and Excel programs, and ordered the world's largest software maker to pay $8.9 million in damages.

Labels: Tables, Tips

Use the keyboard

Labels: General, Tips

Set new standards

1. Create your template form by specifying all the properties you want to maintain from form to form.
   
2. Then, save the form using any name.
   
3. Next, select Tools>Options from the menu bar and select the Forms/Reports tab.
   
4. Enter your template's name in the Form Template box to replace the Access default (Normal) and click OK.

Labels: Customize, Forms, Reports, Tips

Create a sorting function

"Chris Weber develops a solution that allows users to sort the data in their forms (or subforms) that you can add to your application easily.

• The sorting interface should be generic to any form in any database.
  
  
• It should also work with subforms.
  
  
• It needs to be implemented as expediently as possible to fulfill the bid, and be easily maintained in the future.
  
  
• It should be intuitive or reminiscent of other interfaces in Access and Windows.

Labels: Forms, Macros, Tips, Tutorials, VBA

Simple exchange

1. Open the target Office document
   
   
2. Arrange both applications on the screen
   (Right-click an empty part of the Task bar and choose Tile Windows Vertically)
   
   
3. Switch to Access and select the fields or records that you want copied
   
   
4. When you've finished selecting the data, move the mouse pointer near the border of the selection until it turns into an arrow
   
   
5. Finally, drag and drop the data to your target document

Labels: General, Tables, Tips

Identify a linked Access table's source

1. Open the table in Design view
   
   
2. Clicking Yes when Access informs you that some properties can not be modified.
   
   
3. Right-click on the window's title bar
   
   
4. Select Properties from the shortcut menu.
   
   
5. The location will appear in the Description text box in the Table Properties dialog box.

(Use Shift+F2 to open the Zoom box, if necessary)



ComputerBooksOnline

See all Topics

access

Labels: Tables, Tips

If one is missing, use the other

1. Select the Queries, and then click "Create query in Design view".
   
2. Choose the table you want to sort, click Add.
   
3. Click Close.
   
4. Drag down all the fields you want to display in your form, including the two separate fields you want to alphabetize.
   
5. Insert a new column on the left side of the QBE grid.
   
6. In the Field cell, enter the expression
   NZ([CompanyName],[LastName])
   
7. Select Ascending for the Sort option.

When you run the query, if CompanyName is null (empty — no entry), the NZ() function uses the contents in LastName instead.

Here's another way to do it:

See all Topics

access

Labels: Forms, General, Tips

$ lost

If you don't see DDE, check Show all.

Also see:

Answer Box:
Numbers don't merge right in Word

and:

Cindy Meister:
Mail Merge FAQ

Here are some other Mail Merge resources:
Mail Merge Links



See all Topics

access

Labels: Tips

Change background

To do so, create a new Module and add the following code:

Function Highlight(Stat As String) As Integer
Dim ctrl As Control
On Error Resume Next
Set ctrl = Screen.ActiveControl
If Stat = "GotFocus" Then
     ctrl.BackColor = 65535
ElseIf Stat = "LostFocus" Then
     ctrl.BackColor = 16777215
End If
End Function

Click the Code button and insert =Highlight("GotFocus") in each of the Form's textbox control's GotFocus event procedure.

Likewise, add =Highlight("LostFocus)") to each textbox's LostFocus event procedure.

When you've finished, save the changes, close the VBE, and switch to Form view.



When you tab to a field, it's shaded yellow. When you tab away from the field, its background is restored to white.

Also:

ComputerBooksOnline:
Field highlighting solutions



See all Topics

access

Labels: Customize, Forms, Tips, VBA

Ask your peers

• The Use of LIKE
  
  
• Calculated Field
  
  
• Conditional formatting
  
  
• Hide/Show Toolbars
  
  
• Change Default Printer without using print dialog
  
  
• Problems with duplicate matches in my queries
  
  
• Import Excel into Access Table
  
  
• Sending Email Via Lotus Notes
  
  
• Automated E-mail For Lotus Notes and Microsoft Outlook
  
  
• Print Report of the Form onscreen
  
  
• Table/Field Structure
  
  
• Hiding Access Background and taskbar

Labels: General, Tips

Change directions

Access Options.Advanced for 2007:





See all Topics

access

Labels: Customize, Tips

Empty entries

"An example might be fax numbers in a customer database. If you store a Null, it means you don't know whether the customer has a fax number.

If you store a zero-length string, you know the customer has no fax number.
Access gives you the flexibility to deal with both types of 'empty' values."

Nulls and Zero-Length Strings

From John L. Viescas at Viescas.com

Also:
Make Null Zero



See all Topics

access

Labels: Customize, Forms, Properties, Tips

More data than the pie will hold

"Chris Weber provides you with a generic method to control the data for pie charts that are actually readable."

Labels: General, Tips, Tutorials

Change data display

Character

Description

0

Digit (0 through 9 entry required; plus [+] and minus [-] signs not allowed).

9

Digit or space (entry not required; plus and minus signs not allowed).

#

Digit or space (entry not required; blank positions converted to spaces, plus and minus signs allowed).

L

Letter (A through Z, entry required).

?

Letter (A through Z, entry not required).

A

Letter or digit (entry required).

a

Letter or digit (entry not required).

&

Any character or a space (entry required).

C

Any character or a space (entry not required).

, : ; - /

Decimal placeholder and thousands, date, and time separators.
(The actual character used depends on the regional settings specified in Microsoft Windows Control Panel.)

<

Causes all characters that follow to be converted to lowercase.

>

Causes all characters that follow to be converted to uppercase.

!

Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.

Causes the character that follows to be displayed as a literal character. Used to display any of the characters listed in this table as literal characters.
(For example, \A is displayed as just A.)

"Literal"

You can also enclose any literal string in double quotation marks.

Password

Setting the InputMask property to the word Password creates a password entry text box. Any character typed in the text box is stored as the character but is displayed as an asterisk (*).

"The value you entered isn't appropriate for the input mask '!\(999") "000\-0000;;_' specified for this field"