Book

Suggestions


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

Bloglines

1906
CelebrateStadium
2006


OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal












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



  Saturday, February 27, 2010 – Permalink –

Database Genisis

Gotta start somewhere


Microsoft has a tutorial on how to create a database.

Office.Microsoft.com




See all Topics

Labels: ,


<Doug Klippert@ 3:41 AM

Comments: Post a Comment


  Friday, February 19, 2010 – Permalink –

Navigate Navigation Pane

Ahoy


Here's the Microsoft tutorial:

Meet the Navigation Pane

Also:
"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."
Navigation pane tip






See all Topics

Labels: ,


<Doug Klippert@ 3:05 AM

Comments: Post a Comment


  Sunday, January 03, 2010 – Permalink –

Keyboard and Key Tips

Finger it out



2007 apps look different because of the ribbon, but the keyboard can still be used to speed up tasks.
Microsoft has an online course that may help

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.
Office.Microsoft.com/Training




See all Topics

Labels: ,


<Doug Klippert@ 3:55 AM

Comments: Post a Comment


  Tuesday, December 29, 2009 – Permalink –

Close Forms

Auto Shutdown

Here's how to close a form after it’s used:

  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.

TechRepublic


See all Topics

Labels: ,


<Doug Klippert@ 3:41 AM

Comments: Post a Comment


  Thursday, December 24, 2009 – Permalink –

List Fields in Access Tables

Bit o' code


When viewing a table that has many fields in Design view, you have to scroll up and down to review the field names.

This can be tiresome when you're referring to them constantly, and particularly when you're working with several tables.

The following code produces a field listing for a given table. This can then be copied to Notepad and printed for easy reference.

Enter the code into a module, substituting your table's name where appropriate.

Open the Debug/Immediate window, type ListFields,

Press Enter to produce the listing.
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




See all Topics

Labels: , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Friday, December 11, 2009 – Permalink –

Hungarian Notation

Belépés



This tip is useful in a number of applications.

When you name an object, include a prefix that identifies the type of object.

When naming a table for Customers, use "tblCustomers" .
You could also have a form for customers. It would be "frmCustomers" .


It's called Hungarian notation because with the prefix, it does not look like an English word.

Dr. Charles Simonyi developed the convention at Microsoft, and he is from Hungary.

He wrote an article on Hungarian notation for MSDN, the Microsoft Developer's Network.

Hungarian notation


Here are some prefixes:
  • tbl- Table
  • qry- Query
  • frm- Form
  • rpt- Report




See all Topics

Labels:


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Thursday, November 19, 2009 – Permalink –

Control Access Text Alignment

Distribute Text Evenly Within Controls


When you add form or report controls, the Text Align property defaults to General setting: characters align to the left while numbers and dates align to the right.

When you set up controls to act as headings or titles, you can achieve interesting visual results by changing the Text Align setting to Distribute (This is called Justify in Word).

This setting distributes characters within the control evenly to span its entire width.

If you apply this setting to a textbox control, the alignment switches to Left alignment when you click inside the control to allow for easy data entry.
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.




See all Topics

Labels: , ,


<Doug Klippert@ 3:31 AM

Comments: Post a Comment


  Sunday, November 15, 2009 – Permalink –

Color News

A multidiscipline subject


Here is a study about how color effects a reader's choice of concentration.

It was intended for newspaper publishers, but the same knowledge can be used in Web design, PowerPoint, or any other reporting application. Word and Excel will also benefit.

Color, Contrast, and Dimension in News Design

ColorProject

The Poynter Institute is a school for journalists, future journalists, and teachers of journalists.
Poynter.org




See all Topics

Labels:


<Doug Klippert@ 3:06 AM

Comments: Post a Comment


  Friday, November 13, 2009 – Permalink –

Link to Office Documents from Access

Click to Word, PowerPoint, Excel


You can create hyperlinks in Access that jump to other Office documents. The process of specifying the document and the bookmark you want to jump to can be cumbersome.

There's an easy way to specify where in a Word, Excel or PowerPoint document that a hyperlink should jump to, without even having to open the Insert Hyperlink dialog box.

  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.




See all Topics

Labels: , ,


<Doug Klippert@ 3:44 AM

Comments: Post a Comment


  Saturday, October 31, 2009 – Permalink –

Hiding Duplicates in Query Results

Once is enough


It's easy to hide duplicate entries when you run a query, even though Access doesn't go out of its way to call attention to this ability.
  • 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.


Access displays unique records based on each field returned by the query.



See all Topics

Labels: , ,


<Doug Klippert@ 3:12 AM

Comments: Post a Comment


  Sunday, October 25, 2009 – Permalink –

Export Formatted Sheets

Access to Excel


Access provides an easy way to export data to Excel through the Office Links feature.

To use this feature, simply select a relevant database object and choose Tools>Office Links> Analyze It With Excel.

The worksheet Excel creates includes some minor formatting applied to the field headings that appear in row 1. Some formatting in your original Access database affects the worksheet cell formatting as well. For example, if you're exporting from a datasheet, gridline and font attributes are carried over to Excel. If you use the Office Links feature to export data behind a form, text box shading and font properties are applied.

The final result in Excel may not exactly match your Access data; however, you'll probably find that less work is required to get your Excel version of the data into an easily readable state.

In Office 2007-10 it's External Data>Excel






See all Topics

Labels:


<Doug Klippert@ 3:08 AM

Comments: Post a Comment


  Tuesday, October 20, 2009 – Permalink –

Display Data Once

Report Trick


Sometimes, you may have data that needlessly clutters a report.

For instance, suppose your report is listing the fields strCompany, strFirstName, and strLastName.

If there are multiple names listed for each company, and the report is sorted by company name, repeating the company information is unnecessary.

You may want to set a group header based on the company name, but there's an easier way to hide the redundant data.
  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.


If the data in the modified control is the same as the data from the previous record, the control is hidden.



See all Topics

Labels: , ,


<Doug Klippert@ 3:35 AM

Comments: Post a Comment


  Wednesday, October 14, 2009 – Permalink –

Access to E-mail

What's the Outlook?


Garry Robinson from GR-FX Pty Limited of Australia and Scott McManus from Skandus, have a tutorial about:

Processing E-Mail Orders using Outlook and Access.

They have include sample database downloads and the code needed to make the engine work.

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




See all Topics

Labels: , ,


<Doug Klippert@ 3:04 AM

Comments: Post a Comment


  Saturday, September 26, 2009 – Permalink –

Reduce Trips to the Toolbox

Save shoe leather


When you add controls to forms and reports you'll often need to add several of the same type, such as when creating a group of option buttons or a series of unbound text boxes. In such cases, repeatedly moving between the Toolbox and the object you're designing can quickly become tedious.

Fortunately, you can make the process easier. When you select the control you want to add from the Toolbox, double-click the control button (like double-clicking the Format Painter). Doing so lets you add as many controls of that type as you need. When you finish, click the button again to disable the control tool.




See all Topics

Labels: ,


<Doug Klippert@ 3:09 AM

Comments: Post a Comment


  Saturday, September 19, 2009 – Permalink –

Disable Confirmation

Only for the brave


Access tries to prevent user errors that would have calamitous effects on data or an application by throwing up confirmation boxes before potentially dangerous actions. This provides users with an out before committing irreversible changes to the database.

Although you probably want these confirmation dialog boxes in place for end-users, you may find that they slow your work down too much. You may, also, click through the dialog boxes so quickly that they're essentially ineffective.

If you have a programmer's version of hubris, you can prevent Access from displaying confirmation dialog boxes.

To do so, choose Tools>Options from the menu bar and click on the Edit/Find tab. Then, clear the appropriate check boxes in the Confirm panel that correspond to the dialog boxes you want to suppress.

Finally, click OK.
In 2007, click the Office logo and then Access Options



Good luck, you've been warned.




See all Topics

Labels: , ,


<Doug Klippert@ 3:00 AM

Comments: Post a Comment


  Friday, August 28, 2009 – Permalink –

Parameter v. Form

You have a choice



There are a couple of ways to limit the data that is displayed in a Query or in a report.
One is a Parameter Query
  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]
The other is to create a specific form. The unbound form has the following advantages:
  • 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.
Allen Browne has supplied a clear explanation along with some typical code.

Also see FontStuff.com:
Using Parameter Queries




See all Topics

Labels: , , ,


<Doug Klippert@ 3:02 AM

Comments: Post a Comment


  Sunday, August 16, 2009 – Permalink –

Change Keyboard Navigation

It's your choice


You, probably. take for granted how your direction arrow and Enter keys behave in Access.

When you press Enter the focus moves to the next field and the contents of the field are selected. Likewise, when you press the direction arrow keys, the focus moves to the next field in the appropriate direction. You aren't locked into these behaviors.

If you'd like, you can set up the arrow keys to move from one character to the next in the current field, rather than moving focus to the next field.

You can also configure the Enter key to move to the next record when it's pressed or configure it to do nothing at all.

When you do move focus to another field, you have the option to place the insertion point at the beginning or end of the field, rather than selecting the field's entire contents.

To modify these settings, choose Tools>Options from the menu bar and click on the Keyboard tab. Then, simply make the selections you want and click OK.



In 2007 it's under Access Options>Advanced when you click the Logo in the upper left corner.






See all Topics

Labels: , ,


<Doug Klippert@ 6:55 AM

Comments: Post a Comment


  Saturday, August 08, 2009 – Permalink –

Reduce Entry Mistakes

Disable AutoExpand


When you type an entry in a combobox control Access will typically attempt to complete the entry based on the control's lookup list.

This is controlled by the AutoExpand property, which is set to Yes (-1) by default.

Although such behavior is helpful, it can cause problems if your value list contains several items that are close in spelling, since it's easy for users to accidentally let Access choose the wrong item.

You can avoid errors by setting the control's AutoExpand property to No (0) in Design view or using VBA to set the property equal to 0.

Once you've made the change users are forced to type the entire entry or select an item using the combobox control's dropdown list.

(Works the same in Access 2007)






See all Topics

Labels: , ,


<Doug Klippert@ 3:06 AM

Comments: Post a Comment


  Saturday, August 01, 2009 – Permalink –

Data Security

Access style


With all the news about database spying, here's some information about protecting your own Access db.
Don't tell the New York Times.

This is Margaret Blauvelt's TOC from Mendoclick.com:
  • 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
Mendoclick.com
Access Security for End Users

Support.microsoft.com:
Frequently asked questions about Access security warnings

For Access 2007:
Data Security in Microsoft Access 2007




See all Topics

Labels: ,


<Doug Klippert@ 3:11 AM

Comments: Post a Comment


  Sunday, July 26, 2009 – Permalink –

Fiscal Year in Access

Make up your own year


You can show a custom Fiscal Year starting June, 1 and ending May 31.


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


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


Also:
Calculating a future or past date in Access




See all Topics

Labels: ,


<Doug Klippert@ 3:29 AM

Comments: Post a Comment


  Saturday, July 18, 2009 – Permalink –

Auto Form

Just add Data


Highlight a Table in the Objects Table view.

Go to the main menu bar and click on Insert.

Towards the bottom of the menu, you'll find an entry for
"AutoForm"

Click on it and Access will create a data entry form based on the selected table.

When you close the AutoForm, you will be asked if you want to save changes.
Click Yes.

(In 2007 go to Create>Form)

The default name will be the name of the table. If you are using Hungarian notation, change the name from

"tblCustomers" to "frmCustomers"

and click OK.


Also see:
Hungarian Notation




See all Topics

Labels: ,


<Doug Klippert@ 3:48 AM

Comments: Post a Comment


  Wednesday, July 15, 2009 – Permalink –

Restore Defaults

Office 2003 redo


To reset the original settings in Office 2003, follow these steps.
Make sure that you back up your files before you follow these steps.
  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.

Microsoft Office Diagnostics in 2007 replaces Diagnose and Repair:

Howtogeek.com




See all Topics

Labels: ,


<Doug Klippert@ 3:30 AM

Comments: Post a Comment


  Sunday, July 12, 2009 – Permalink –

Plain Numbers

I'd Like to Make It Clear


Plain Figures is a method of transforming statistical and financial data into figures, tables and graphs that people readily understand.

Have you ever:
  • 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."?

Then you know how important the clear display of numerical information can be. Common problems People have trouble using numerical information for many reasons. Most commonly, authors don't know:
  • 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.

Plain Figures is a partnership between Sally Bigwood, located in Wakefield, Yorkshire, UK, and Melissa Spore, who divides her time between Toronto and Saskatoon, Canada. Sally and Melissa are sisters and both have dual citizenship in the United States. PlainFigures.com See all Topics

Labels: ,


<Doug Klippert@ 3:01 AM

Comments: Post a Comment


  Saturday, July 11, 2009 – Permalink –

A Hoard of Tips

Good for all levels of user


Allen Browne's tips for Access users

Here is a list of the sections on this site; something for everyone.
  • 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.

See all Topics

Labels: ,


<Doug Klippert@ 3:19 AM

Comments: Post a Comment


  Thursday, June 25, 2009 – Permalink –

Compact Before Synchronizing

A DB two step


If you're working with a replicated database, always compact it twice before you synchronize.

The first time you compact, Access marks objects that need to be deleted, but it doesn't actually remove them.

The flagged objects are removed the second time you compact the database. Although there's no harm in additional compacting, there's no added benefit.

This is particularly important to do when working with the Design Master. Each time you make design changes to the Design Master, a copy of the original object is kept in the database.

If you edit and save a Report or Form 10 times, your database actually has 9 older versions of the form in it. The same is true if you change the schema of a Table or Query If you synchronize before compacting, all of the versions are sequentially sent to the other replicas until everything is in synch, and you can't reclaim the space taken up by the older versions.

However, if you compact the database first, only the most recent form version is sent to the replica.




See all Topics

Labels:


<Doug Klippert@ 3:29 AM

Comments: Post a Comment


  Tuesday, June 23, 2009 – Permalink –

Mail Your MDB-ACDB

Don't let Outlook stop you


There was a lot of frustration with Outlook 200x. If an Access MDB file is received, Outlook expects the worst and blocks the attachment.

The trick has been to ZIP the file or change the extension to something like .DAT.

Here's a registry trick that restores Outlook to the good old days.
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.
Slipstick.com:

Opening .exe Attachments


Also see:
Shortcuts for Sending Access Objects via Email


See all Topics

Labels: ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Wednesday, June 10, 2009 – Permalink –

Automaticaly Setup a Database

Link in a snap


When you're creating a new database that will link to data that isn't in an Access format, you can speed up the setup process.

Rather than creating a new database and then using the File>Get External Data>Link Tables,

  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
Access will automatically create an MDB file with the same name as the data source you selected and will set up links to the data.




See all Topics

Labels: , ,


<Doug Klippert@ 3:55 AM

Comments: Post a Comment


  Thursday, June 04, 2009 – Permalink –

Database Examples

Clever timesavers


Roger Carlson, RogerCarlson.com, has collected a group of Access databases that demonstrate different functions.

The Library includes tutorials and a good list of files that can be downloaded.

See the Table of Contents
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

And many more




See all Topics

Labels: , ,


<Doug Klippert@ 3:34 AM

Comments: Post a Comment


  Saturday, May 23, 2009 – Permalink –

Compact-Repair Shortcut

Desktop convenience


You can make compacting and repairing databases easier by providing a desktop shortcut.

Right-click on the Windows desktop and select New>Shortcut from the shortcut menu. Then, set up a Command Line entry in the form:

"Path to Access.exe" "Path to Database.mdb" /compact

For example, to create a shortcut to compact Northwind, you might use:

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" /compact

Click Next and continue through the shortcut setup wizard, naming the shortcut appropriately.

In Access 2000+, the database is both compacted and repaired when the /compact switch is applied.

Access 97 executes these processes separately, so Access 97 shortcuts should use a Command Line in the form:

"Path to Access" "Path to Database" /compact /repair

Also note that you can compact to a different location by specifying a target database name after the /compact switch.

If you omit a target file name following the /compact option, the file is compacted to the original name and folder. To compact to a different name, specify a target file.

If you don't include a path in target database or target Access project, the target file is created in your My Documents folder by default.

(Even though a shortcut will open the database without it, in order for the command line flag to work, you MUST include the path to the executable - Access.exe)




See all Topics

Labels: ,


<Doug Klippert@ 3:31 AM

Comments: Post a Comment


  Sunday, May 17, 2009 – Permalink –

New Fields from Datasheet View

Table fields on the fly



We all, of course, pre-plan our table layouts. However, if you need to create a table field while working in Datasheet view, you don't have to switch to Design view.

There's a quick way to create a field from Datasheet view.

Right-click on the column heading of the column you want to the right of your new field.
Choose Insert Column from the shortcut menu.

Access creates a field with a name such as "Field1".

You can then immediately start entering data in. You can also use the shortcut menu to rename or delete the newly created field.

Later you can go to Design view to set the field's properties.




See all Topics

Labels: ,


<Doug Klippert@ 3:19 AM

Comments: Post a Comment


  Sunday, April 26, 2009 – Permalink –

Composite Keys

Multi-Field Keys


To quickly create a multi-field primary key, while in the table's Design View:
  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
Access creates the composite primary key for you.

University of Texas at Austin:
Primary and Foreign Keys




See all Topics

Labels: ,


<Doug Klippert@ 3:05 AM

Comments: Post a Comment


  Saturday, April 18, 2009 – Permalink –

Duplicate Table Structures

No need to reinvent


When setting up a database, you may find that there is a table structure in another database that would be appropriate. You could Import the table, but then you have to clear out the useless records.

Here's an easier way to do it:
  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
.

To export a table definition from your current database to another:
  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
When the Export dialog box appears, select the Definition Only option button and click OK. See all




See all Topics

Labels: ,


<Doug Klippert@ 3:42 AM

Comments: Post a Comment


  Friday, April 10, 2009 – Permalink –

Business Rules!!! ... Rules

Bean Counting Models


Data modeling can be an exhausting, time consuming process. Here are some samples that may establish a starting point.


The website has close to 100 database models for experimentation, practice and examples.

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


Database Answers

Barry Williams - Founder and Principal Consultant.


If you're attempting a "Big Year" list of blogs, you can add Viewpoint of a Database Analyst: A Database Design Principles Blog




See all Topics

Labels: , ,


<Doug Klippert@ 3:49 AM

Comments: Post a Comment


  Monday, March 30, 2009 – Permalink –

Linked Table Problems

Lost functionality


Have you found that you cannot update linked Excel tables in Access 2003?


If you have installed Microsoft Office 2003 Service Pack 2 (SP2):


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.


Support.Microsft.com:
You cannot change data in linked tables that are to an Excel workbook

You might try reinstalling and then only installing SP1.


Also:

VB123.com:
Guacamole dipped - Access to Excel linked table gotcha

PCWorld.com:
Patent ruling costs Microsoft $8.9 million


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



See all Topics

Labels: ,


<Doug Klippert@ 3:17 AM

Comments: Post a Comment


  Wednesday, March 25, 2009 – Permalink –

Toggle Object Views

Use the keyboard


When you are putting a database together you often want to switch between views of Access objects to see the changes.

For instance, you'll switch the view to examine a Table in Design view and then back to data view. It is can be faster to switch between views using keyboard shortcuts, rather than the View menu.

You can cycle through the views of an open object using the

Ctrl + .

or

Ctrl + ,

shortcut keys.
These shortcuts can be used with tables, queries, forms, reports, and data access pages.




See all Topics

Labels: ,


<Doug Klippert@ 3:31 AM

Comments: Post a Comment


  Monday, March 16, 2009 – Permalink –

New Default Access Form Template

Set new standards



You can change Access's default form.

To set up a custom template,
  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.
The next time you create a form, Access will base it on your form template rather than the typical Normal template.

In Access 2007, click the Office button in the upper left corner and the click Access Options.
The design options are located under Object Designers: Forms/Reports.
The same thing can be done with Reports.

 

 (The settings are saved in the Access workgroup information file, not in a particular data base)

Also: MeadinKent.co.uk: Making a report template

Here's a calendar from MeadinKent: Calendar page




See all Topics

Labels: , , ,


<Doug Klippert@ 3:13 AM

Comments: Post a Comment


  Thursday, January 29, 2009 – Permalink –

Sort Forms

Create a sorting function

Here is one way to provide your users with a means to reorder fields in forms.

"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.
Let Your Users Sort it Out



Scroll down to the bottom of the page. There is a file that can be downloaded with the code.




See all Topics

Labels: , , , ,


<Doug Klippert@ 3:04 AM

Comments: Post a Comment


  Monday, December 29, 2008 – Permalink –

Drag Data

Simple exchange


To transfer data from an Access query or table in another Office program, such as Word, there's no need to manually export the data.
  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
You can also select a whole table, go to Edit>Copy. Switch to Word or Excel and Paste.
It works in the other direction too. Select some Excel data. Switch to Access. While viewing the Tables Objects, Paste the Excel data. It will form a new table.



See all Topics

Labels: , ,


<Doug Klippert@ 3:57 AM

Comments: Post a Comment


  Thursday, December 18, 2008 – Permalink –

Source of Tables

Identify a linked Access table's source



When a database contains linked tables, you may need to find the location of the source file.
One way is to:
  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

Labels: ,


<Doug Klippert@ 3:38 AM

Comments: Post a Comment


  Thursday, December 11, 2008 – Permalink –

Alphabetize by One Field or the Other

If one is missing, use the other


Let's say you have a database that has the company name and a contacts name.

In some cases the CompanyName field is empty. If that happens, you want to continue the alpha sort using the contact's LastName.

To do this, you need to create an extra query field to provide the sort, using the NZ() function to replace the contents of one field for null values in another.

(Nz(variant, [valueifnull])

  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

Labels: , ,


<Doug Klippert@ 3:47 AM

Comments: Post a Comment


  Friday, December 05, 2008 – Permalink –

Merge Formatting

$ lost


The data in Access or Excel has been formatted. You have leading zeros, percents, currency is formatted and so on.

Word 200+, however loses the formatting when a mail merge is attempted.

Here's a fix.

Word has three potential data access methods, the "old fashioned" ODBC or DDE and the newer OLE DB.

ODBC and OLE DB can, quickly, extract data from a source application without opening the program. The application does not even have to be installed.

The downside is that these methods do not transfer the formatting in the data file. Individual MERGEFIELDs need to be formatted in Word.

DDE can be used with Excel and Access. It communicates with the source and carries the formatting into the target document. This is how it worked before Word 2002.

To have a choice go to:
Tools>Options>General "Confirm Conversions at Open"

When you connect to the Data Source, a dialog box will give you the opportunity to choose the type of connection to use.


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

Labels:


<Doug Klippert@ 7:37 AM

Comments: Post a Comment


  Tuesday, December 02, 2008 – Permalink –

Text Box Highlights

Change background


It can be difficult to tell which text box on a form you're currently working with.

One solution is to highlight the current position, with a different background.

Access 2000+ allows you to do this with conditional formatting, but you can also get a similar result using code.

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


Save and close the Module, then open the appropriate Form in Design view.

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

Labels: , , ,


<Doug Klippert@ 6:55 AM

Comments: Post a Comment


  Wednesday, November 26, 2008 – Permalink –

Access Forum

Ask your peers


Here is a forum populated by Access users and developers around the world.

Just a few of the recent inquiries:


  • 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



Access World Forums



See all Topics

Labels: ,


<Doug Klippert@ 6:57 AM

Comments: Post a Comment


  Sunday, November 16, 2008 – Permalink –

Keyboard Navigation

Change directions


You can change how your direction arrow and Enter keys behave in Access.

By default, when you press Enter the focus moves to the next field and the contents of the field are selected. When you press the direction arrow keys, the focus moves to the next field in the appropriate direction.

If you would like, you can set up the arrow keys to move from one character to the next in the current field, rather than moving focus to the next field.

You can configure the Enter key to move to the next record when it's pressed or configure it to do nothing at all.

When you do move focus to another field, you have the option to place the insertion point at the beginning or end of the field, rather than selecting the field's entire contents.

To modify these settings, choose Tools> Options from the menu bar and click on the Keyboard tab. Make the selections you want and click OK.




Access Options.Advanced for 2007:





See all Topics

Labels: ,


<Doug Klippert@ 3:15 AM

Comments: Post a Comment


  Friday, November 07, 2008 – Permalink –

Null, Nothing, Nada

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

Labels: , , ,


<Doug Klippert@ 4:10 AM

Comments: Post a Comment


  Tuesday, November 04, 2008 – Permalink –

Too Many Slices

More data than the pie will hold


A pie chart displays the per cent of the whole is represented by the component elements. Four salesmen, four slices of pie.

The problem arises when there are 10 or so components that vary in size. The labels begin to overlap and the chart is difficult to read:



One suggestion that Chris Weber offers is to rearrange the order of the slices:



The article uses MS Graph in Access, but the techniques are applicable in all the other applications that can use graphs.

SmartAccess:
Easy as Pie. . .

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

(A downloadable example file is also provided



See all Topics

Labels: , ,


<Doug Klippert@ 3:41 AM

Comments: Post a Comment


  Wednesday, October 22, 2008 – Permalink –

Input Characters

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 (*).

If you don't like the error message that appears by default i.e.:
"The value you entered isn't appropriate for the input mask '!\(999") "000\-0000;;_' specified for this field"

See:
How to Replace the Default Input Mask Error Message

Also see:
Using an input mask to restrict data

Hidden Passwords





See all Topics

Labels: ,


<Doug Klippert@ 3:05 AM

Comments: Post a Comment


  Tuesday, September 30, 2008 – Permalink –

Access Data - Excel Time Sheets

Distribute to everyone


Many times an office will provide Excel for all users, but not want or need to also install Access on every desk.

Helen Feddema has laid out a method to use the data in an Access database to create Excel workbooks. These workbooks can then be e-mailed to employees to be used to record time spent on projects.

The code provided is above the entry level user, but understandable.

There is a downloadable file that includes the instructions and samples of the Access and Excel files.

Go to Access Archon Columns from Woody's Office Watch.


The pertinent file is down near the bottom of the page. Look for article 127 and download accarch127.zip.



See all Topics

Labels: ,


<Doug Klippert@ 3:08 AM

Comments: Post a Comment


  Monday, September 22, 2008 – Permalink –

Highlight Form Data

Show your position


Conditional formatting, in Access, has one more alternative than Excel. Besides "Field Value Is" and "Expression Is", there is "Field Has Focus". You can call attention to the location of the insertion point.

  1. In Design View, in the Detail section, click the Control box.
  2. On the Format menu, click Conditional Formatting.
  3. Under Condition 1, chooseField Has Focus in the first box.
  4. Click the arrow next to the Font/Fore Color button, click the yellow box, and then click OK.



  5. On the File menu, click Save.
  6. On the View menu, click Datasheet View to view the results.

Also see:

Using the Value in a Control as Formatting Criteria

and

Using an Expression as Formatting Criteria

Microsoft Office Online:
Highlighting data for impact on a form in Access



See all Topics

Labels: ,


<Doug Klippert@ 3:50 AM

Comments: Post a Comment


  Saturday, September 13, 2008 – Permalink –

Make Null Zero

It's nothing


When it is desirable to return a zero (or another value) rather than an empty field, Access (Visual Basic) has a function Nz():

Nz(variant, [valueifnull])


The Nz function has the following arguments.

variant
A variable of data type Variant.
Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.
valueifnull


This example demonstrates how you can simplify an IIF function

Instead of:


varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")


You could use:

varResult = IIf(Nz(varFreight) > 50, "High", "Low")


Helen Feddema offers a suggestion about forcing a zero when Nz() doesn't work

When you want to display zeroes in text boxes (or datasheet columns) when there is no value in a field, the standard method is to surround the value with the Nz() function, to convert a Null value to a zero. However, this doesn't always work, especially in Access 2003, which is much more data type-sensitive than previous versions. In these cases, you can force a zero to appear instead of a blank by using two functions: first Nz() and then the appropriate numeric data type conversion function, such as CLng or CDbl. Here is a sample expression that will yield a zero when appropriate:

NoAvailable: CLng(Nz([QuantityAvailable]))

ACCESS Watch Vol 7 No. 5


See all Topics

Labels: , ,


<Doug Klippert@ 3:32 AM

Comments: Post a Comment


  Thursday, August 28, 2008 – Permalink –

Calculate Age

A few solutions



Here are some methods that have been posted to the newsgroups:

Assuming that the birth date field is called [BDate] and is of type date, you can use the following calculation:

Age:DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd")

Alternately you can use this function to calculate age:

Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < age =" Year(DateToday)" age =" Year(DateToday)">


From:
The Access Web (MVPs.org)

Also see:

Support.Microsoft.com:
Two Functions to Calculate Age in Months and Years


Office Tips:
Martin Green
Working out Someone's Age





See all Topics

Labels: ,


<Doug Klippert@ 3:03 AM

Comments: Post a Comment


  Sunday, August 17, 2008 – Permalink –

Pictures in Forms

It can still be done


Photo Editor was an Office component prior to Office 2003.

In Access/Office 2003/2007 MS Photo Editor was replaced, and Office no longer includes an OLE server for images.

To work around this issue, reinstall Photo Editor.

To do this, follow these steps:

  1. Insert the Microsoft Office XP CD-ROM into your computer's CD-ROM or DVD-ROM drive.
  2. If the Office XP Setup program does not start automatically, follow these steps:

    1. Click Start, and then click Run.
    2. In the Open box, type drive:\Setup.exe, where drive is the letter of your CD-ROM or DVD-ROM drive.

  3. Start to step through the Setup process, and then select Customize for the type of Setup to perform.
  4. When you reach the Selecting Features step, set each node of the Setup tree to Not Available.
  5. Under Features to install, expand Office Tools.
  6. Click Microsoft Photo Editor, and then click Run from My Computer.
  7. Click Install Now.
  8. Click OK when Office XP Setup has completed successfully.

Microsoft Support Article: 817095

Also: Ammara.com: Pictures in Access Forms & Reports OLE Object Photo & Image Problems & Solutions

Access MVPS.org: Handle/Display images in forms/database

"While it's possible to actually store the images within the database itself as OLE objects, normally it's not the preferred way. When you embed an OLE object in a table, you're storing a lot of overhead as well. This overhead is the information about the object (such as its parent application) that Access needs to store in order to render the object properly."
Displaying photos in a form See all Topics

Labels: ,


<Doug Klippert@ 3:51 AM

Comments: Post a Comment


  Friday, July 11, 2008 – Permalink –

Checkmark vs. Checkbox

Just the check


Use of a Boolean Yes/No checkbox is pretty common.

Here is a way to place just a checkmark next to an entry.
Not Channel 9 material, but good to know.

Display ü

  1. Use the Report Wizard to create a report that is based on the Northwind Products table. Use the ProductName field and the Discontinued field.

  2. In Design view, add a TextBox control to the Detail section.

  3. Right-click TextBox, and then click Properties.

  4. In the TextBox properties dialog box, click the Format tab. For Border Style, click Transparent. For Font Name, click Wingdings.
    Note You can adjust the size of the check mark by changing Font Size.

  5. Click the Data tab.

  6. For Control Source, type =IIF([Discontinued]=-1,"Alt+0252")
    Note "ALT+0252" is the check mark character. To enter this character, hold down ALT, and then type 0252 by using the keypad. The check mark character appears as "ü" in the IIF statement.


    Note If you want to display an "X" instead of a check mark,

    1. In the TextBox properties dialog box, click the Format tab. For Border Style, click Transparent. For Font Name, click Arial.

    2. Click the Data tab.

    3. For Control Source, type =IIF([Discontinued]=-1,"X")

  7. Close Design view.

  8. Click Preview.


Microsoft Knowledgebase:
How to print a check box without borders on a pre-printed form in Access



See all Topics

Labels: , ,


<Doug Klippert@ 3:13 AM

Comments: Post a Comment


  Monday, July 07, 2008 – Permalink –

Hidden Tables

Don't peek


If you do not want the ordinary user to see a table in your database, you can hide it in the database windows so that other users cannot select it.

Preface the table's name with the four letters 'Usys'. For example, if your table's name is ProprietaryCosts , change it to Usys ProprietaryCosts. This makes your table into a system object which Access does not display.

To see this table later, in Tools>Options, on the View tab in the Show section, put a check mark by System objects.

If we agree among ourselves not to tell the uninitiated, no one need be the wiser.


Microsoft Office Online:
How to Hide Objects in the Database Window

Here's how it works in 2007:
Hide tables as system tables in Access 2007



See all Topics

Labels: , ,


<Doug Klippert@ 3:30 AM

Comments: Post a Comment


  Friday, June 27, 2008 – Permalink –

Avoid AutoComplete Errors

Don't start


When you type an entry in a ComboBox control Access will attempt to complete the entry based on the control's lookup list. This is controlled by the AutoExpand property, which is set to "Yes" by default.

If your value list contains several items that are close in spelling, it is easy for users to let Access choose the wrong item by accident.

You can avoid errors by setting the control's AutoExpand property to "No" in Design view.

Once the change has been made, users will be forced to type the entire entry or select an item using the ComboBox control's dropdown list.



See all Topics

Labels: ,


<Doug Klippert@ 5:44 AM

Comments: Post a Comment


  Saturday, June 07, 2008 – Permalink –

Good OLAP

More data


From Builders.com.com:

An introduction to the benefits of online analytical processing (OLAP)

"Every day we create reams of data in customer relationship management applications, order entry applications, and warehouse management systems. We're drowning in a sea of data. However, even with all that data we don't have a large amount of information. We have the ones and zeros of the transactions, but we don't have the answers we need to simple questions like:

  • "Why was March better than February?"
  • "Where is the sales force having the most success?"
  • "In what conditions does the sales team struggle with making sales?"


The article also has links to:

  • Oracle 9i makes data warehousing easy to implement
  • Seven highly effective steps to a smooth data warehouse implementation
  • Business intelligence is just a few steps away for SAP R/3 users
  • Resources for designing, planning, and implementing a data warehouse strategy
  • Making the operational case for data warehousing
  • TechRepublic Tutorial: Data warehousing defined


Also see:
OLAP Cube

Data Warehousing and OLAP
A Research-Oriented Bibliography


FAQ Excel 2007 – OLAP

Microsoft:

OLAP



See all Topics

Labels: , ,


<Doug Klippert@ 6:43 AM

Comments: Post a Comment


  Monday, April 21, 2008 – Permalink –

Cascading Combo Boxes

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



A cascading combo box solution:

Roger J. Carlson

CascadingComboBoxes.mdb (beginner)
CascadingComboInSubform.mdb (intermediate)



See all Topics

Labels: ,


<Doug Klippert@ 6:16 AM

Comments: Post a Comment


  Tuesday, April 15, 2008 – Permalink –

List Box Filter

Multiple selections



Filter a Report based on List Box

Fabalou.com:
"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

Labels: , , ,


<Doug Klippert@ 5:30 AM

Comments: Post a Comment


  Sunday, April 06, 2008 – Permalink –

Add a Table

Drag drop trick



While working with a query in Design view, you may find that you need to add a table or query. The "book" way to do it is to click the Show Table toolbar button, drag the appropriate objects from the list, and then close the dialog box.

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

Labels: , ,


<Doug Klippert@ 6:50 AM

Comments: Post a Comment


  Saturday, March 29, 2008 – Permalink –

Week Numbers

Who's counting?


For most purposes, weeks are numbered with Sunday considered the first day of the week. This works most of the time, but it can be a little confusing certain years.


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

Labels: ,


<Doug Klippert@ 7:23 AM

Comments: Post a Comment


  Friday, March 07, 2008 – Permalink –

Tips Handout

Access tidbits



Woody's Lounge at Wopr.com is a good location to find information on most anything computer like.

Here is a downloadable handout with tricks to use with Access.

Tips Handout



See all Topics

Labels:


<Doug Klippert@ 7:00 AM

Comments: Post a Comment


  Saturday, February 09, 2008 – 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@ 7:09 AM

Comments: Post a Comment


  Saturday, February 02, 2008 – Permalink –

Filter Multiple Selections

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)


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



See all Topics

Labels: , ,


<Doug Klippert@ 7:21 AM

Comments: Post a Comment


  Monday, January 07, 2008 – 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@ 8:04 AM

Comments: Post a Comment


  Saturday, January 05, 2008 – Permalink –

Sandbox Warning

Avoid the dark spots



Access 2003 added another security feature - slash - PIA. Jet 4.0 has had a protective mode in the past, but Access 2003 takes it seriously.


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



Access 2007 sandbox

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.


New in 2007

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

Labels: ,


<Doug Klippert@ 4:23 AM

Comments: Post a Comment


  Thursday, December 27, 2007 – 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@ 7:21 AM

Comments: Post a Comment


  Thursday, December 06, 2007 – Permalink –

Info Docs

Things you need to know


WOPR.com has a great lounge with members who can answer most any question you can come up with.


Steve Hayward prepared a couple of Access papers that contain vital information about databases.

Golden Rules

Normalization Doc



See all Topics

Labels: ,


<Doug Klippert@ 6:59 AM

Comments: Post a Comment


  Saturday, December 01, 2007 – Permalink –

Splash Screen

Brand your app



You can replace the Access splash screen with your own logo.

Access displays a quick splash screen when you launch it. You can replace that splash screen with a graphic of your own.

Save a bitmap graphic in the same folder as the database.

Give the graphic the same name as your database and make sure it has a BMP extension.
(MyDatabase.BMP)

Now when you launch Access by double-clicking on the database icon or its shortcut, you will see your logo instead of Access's default screen.

You can also make the graphic 1x1 pixels, so it won't be seen.

Advisor.com:
Create Personalized Splash Screens for Access

MS Knowledge Base:
How to Create a Custom Startup "Splash" Screen



See all Topics

Labels: ,


<Doug Klippert@ 6:33 AM

Comments: Post a Comment


  Tuesday, September 25, 2007 – 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@ 8:06 AM

Comments: Post a Comment