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, August 12, 2017 – Permalink –

Ripple the Ribbon

Change the look


"Learn how you can create a custom Office Fluent Ribbon for an Access 2007-13 database by using only Office Fluent extensibility markup XML and macros.

Discover how to create a command space without writing any code and also learn about more advanced scenarios that require code."

Customizing the Office Fluent User Interface



Customize the Ribbon


See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:57 AM

Comments: Post a Comment


  Sunday, August 06, 2017 – Permalink –

Highlight the Current Control

Code vs. property


Many users have trouble knowing which text box on a form they're currently working with. One way to make it clear for users is to highlight the current one, for example, with a yellow background.
Access 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 form you want to apply the highlighting to in Design view.
Click the Code button and insert

Highlight("GotFocus")

in each text box control's GotFocus event procedure. Likewise, add

Highlight("LostFocus")

to each text box'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.


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:13 AM

Comments: Post a Comment


  Saturday, July 29, 2017 – Permalink –

Hiding Columns

In Datasheet view


When you're working in Datasheet View, you can easily hide columns containing data that you don't need to immediately work with.

To do so, select any field in the column and choose Format >Hide Columns from the menu bar.

As an alternative, right- click on the column's field name and select Hide Columns from the shortcut menu.

To redisplay hidden columns, select Format>Unhide Columns from the menu bar.
Then, select the check boxes next to the field names of any columns you want displayed and click OK.

You can select the Unhide Columns command even if no columns are hidden, allowing you to easily hide multiple columns by clearing the appropriate check boxes.


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:23 AM

Comments: Post a Comment


  Tuesday, July 18, 2017 – Permalink –

5 of 10 Commandments - Access

Writ on tables


"And it came to pass that the cries and lamentations of the Access newbies were heard on high by the gods of the Database, and their hearts were moved to pity for their followers. And they opened their mouths and spake, saying: "Nevermore shall the young and innocent wander witless on their journeys!
  1. Thou shalt design normalized tables and understand thy fields and relationships before thou dost begin.
  2. Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One.
  3. Thou shalt choose a naming convention and abide by its wisdom and never allow spaces in thy names.
  4. Thou shalt write comments in your procedures and explain each variable.
  5. Thou shalt understand error handling and use it faithfully in all thy procedures.
  6. . . .

Thus spake the gods of the Database, and blessed be their names!"

The full list can be found on:
The Access Web


See all Topics

Labels: , , ,


<Doug Klippert@ 3:12 AM

Comments: Post a Comment


  Thursday, July 13, 2017 – Permalink –

Numbers to Words

Cardinal numbers



You can create a User Defined Function in Access to covert numbers to words.
The function can be used in a calculated field or control in a form or report.

From the Microsoft Knowledgebase collection:
How to Convert a Numeric Value into English Words



Also:
The Access Web (MVPS)
Convert Currency ($500) into words (Five Hundred Dollars)

TECH on the Net.com
Convert currency into words
(The Access code also works in Excel)

To create Cardinal numbers in Excel see:
Excel - Numbers to Words
(The Excel code also works in Access)

Word appears to be the only Office app with a built in cardinal number function.

For Word see:
Word - Numbers to Words


See all Topics

Labels: , , ,


<Doug Klippert@ 3:37 AM

Comments: Post a Comment


  Friday, July 07, 2017 – Permalink –

Time Interval

Run code at timed intervals


You may occasionally want to run a procedure associated with a form at set intervals. To do so, add the code to the form's Timer event procedure. Then, set the form's TimerInterval property to the number of milliseconds that should elapse between each time the code is run. (in Access 2007+, the TimerInterval property setting is a Long Integer value between 0 and 2,147,483,647.)

Keep in mind that you shouldn't use a very small TimerInterval, otherwise your application will likely suffer a performance hit. To prevent the Timer event from firing, set the TimerInterval to 0.

Also see:
HOW TO: Create a Stopwatch Form in Access


See all Topics

Labels: , , ,


<Doug Klippert@ 3:10 AM

Comments: Post a Comment


  Tuesday, June 27, 2017 – Permalink –

Getting 2010-13 Reference Guides

Where'd they hide that thing?


Wondering where your favorite Word 2003 commands are located in the new Word 2010-13 interface? Or just want to explore the rich, new design with a little guidance?

... rest the mouse pointer over a Word 2003 menu or button to learn its new location in Word 2010-13. To see an animation of the location of the command or button in Word 2010-13, just click it.


Command reference guides for:

Office 2010

Office 2013




See all Topics

Labels: ,


<Doug Klippert@ 3:51 AM

Comments: Post a Comment


  Sunday, June 25, 2017 – Permalink –

Comment Code

Edit toolbar



You'll many times want to change blocks of code to comments in VBA modules; temporarily convert a block of VBA code to comments so that it's ignored during a trial run. Inserting an apostrophe before each line of code is a bother. Office 2000+ simplifies this task by letting you convert a block of code to comments with a click of a button.

Open any module in the Visual Basic Editor (VBE), and then choose View>Toolbars and choose Edit from the menu bar to display the Edit toolbar.

Select the lines of code that you want to turn into comments. Then, click the Comment Block button on the Edit toolbar (it's the sixth button in from the RIGHT end of the toolbar).
Each line of the selected code is now preceded with an apostrophe.



To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.
This, of course, works in any application that uses the VBE.

It's been suggested that two or three apostrophes (sometimes called inverted commas) be placed around existing comments. When the Comment Block is used, the original comments will not be removed.


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:44 AM

Comments: Post a Comment


  Tuesday, June 20, 2017 – Permalink –

Use Access or Excel

When to use one or the other


Use Access when you:
  • Require a relational database (multiple tables) to store your data.

  • Might need to add more tables, in the future, to an originally flat or nonrelational data set.

  • Keep a very large amount of data (thousands of entries).

  • Keep data that is mostly text.

  • Rely on multiple external databases to derive and analyze the data that you need.

  • Need to maintain constant connectivity to a large external database, such as one built by using Microsoft SQL Server.

  • Want to run complex queries.

  • Need many people working in the database and you want robust options that expose that data for updating.
Use Excel when you:
  • Require a flat or nonrelational view of your data (that is, you do not need a relational database with multiple tables).

  • This is especially true if that data is mostly numeric - for example, if you want to maintain a financial budget for a given year.

  • Want to run primarily calculations and statistical comparisons on your data - for example, if you want to show a cost/benefit analysis in your company's budget.

Use Access or Excel to manage your data


See all Topics

Labels: , ,


<Doug Klippert@ 3:24 AM

Comments: Post a Comment


  Friday, June 16, 2017 – Permalink –

How Access Grew

History



A history of Microsoft Access, including key features and milestones from the release of Access 1.0 to Access 2010.

20 years of Access

Also:



Old Access Versions



In Access 1.0, if you found the "Easter egg" list of developers, at the very end there was a pond with two birds floating around.

A large foot then comes down and crushes the pair of ducks. (Paradox was Microsoft's rival at the time.)


See all Topics

Labels: ,


<Doug Klippert@ 3:05 AM

Comments: Post a Comment


  Tuesday, June 13, 2017 – Permalink –

SQL Statements Automatically

Hidden code


If you're having trouble figuring out a complex SQL statement for use in code, you may be able to simplify the process by first setting it up the query you want in the query design grid.

Once you've got it configured correctly, choose View >SQL view to reveal the underlying SQL statement, which you can copy and paste into your code.

You may have to make minor modifications, but this technique often eliminates much of the hassle of manually constructing SQL statements.

You can also tweak the underlying SQL code to adjust your Query. This code can also be copied and reused in other Queries after a little customizing.


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:53 AM

Comments: Post a Comment


  Tuesday, June 06, 2017 – Permalink –

View Multiple Pages of an Access Report

More than one at a time


When you preview an Access report, you may want to see several pages at one time to examine the layout of the report.
You may know that you can do so by choosing View >Pages from the menu bar. However, this technique limits how you view the pages -- you can only view 1, 2, 4, 8, or 12 at a time.

For more control over how the pages are displayed, right-click on the report preview itself, and choose Multiple Pages from the shortcut menu.

The default layout grid is 2 x 3 pages. However, if you click and drag with your mouse, you can select a range of up to 4 x 5 pages.


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:51 AM

Comments: Post a Comment


  Sunday, June 04, 2017 – Permalink –

Automatically Renumber Records

Been looking for this


What happens to a list when one record is deleted?

If you need to correct the numbering, see these instructions.

Automatically renumber the records in an Access table when one is deleted

from Martin Green - FontStuff.com


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Wednesday, May 31, 2017 – Permalink –

Send E-Mail with Access

Automate the drill


You can use Access as a data source and use Word to merge to Outlook. Here, however, is a way to do it from Access itself.
"You can use the SendObject method to send a MAPI mail message programmatically in Microsoft Access. However, the SendObject method does not give you access to complete mail functionality, such as the ability to attach an external file or set message importance.

The example that follows uses Automation to create and send a mail message that you can use to take advantage of many features in Microsoft Outlook that are not available with the SendObject method.

There are six main steps to sending a Microsoft Outlook mail message by using Automation, as follows:

  1. Initialize the Outlook session.
  2. Create a new message.
  3. Add the recipients (To, CC, and BCC) and resolve their names.
  4. Set valid properties, such as the Subject, Body, and Importance.
  5. Add attachments (if any).
  6. Display/Send the message.

Use Automation to send a Microsoft Outlook message using Access

Access to E-Mail


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:24 AM

Comments: Post a Comment


  Thursday, May 25, 2017 – Permalink –

New Line in Memo

Labels and Text boxes


When you're using a form to enter text in a memo field, pressing [Enter] within the text inserts a line break.

However, when you're working with a memo in a table's Datasheet view, pressing [Enter] moves the focus to the next field.

You can force line breaks when you're entering text by pressing [Ctrl][Enter]. This technique also works with text fields and can be applied when you're entering text in labels or text boxes on a form.

To permanently configure a text box so that pressing [Enter] inserts a new line, set its EnterKeyBehavior property equal to New Line In Field.


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:30 AM

Comments: Post a Comment


  Tuesday, May 16, 2017 – Permalink –

Sort Listboxes

We must have order




"A nice way to enhance your application is to give your users the ability to sort the contents of listboxes.

If you have a listbox with many records, this feature could be much appreciated. It's easy to manipulate the listbox RowSource property to accomplish this.

A command button could be used to add an "Order By" clause to the SQL statement, and once the RowSource property is updated, the list is automatically sorted."


An example showing exactly how to do this is available for download as a Zip file here:
Sortable Link Boxes

from Peter's Software


See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:19 AM

Comments: Post a Comment


  Saturday, May 13, 2017 – Permalink –

Update the FUI Ribbon

Let the add-ins begin


It is said that the Office 2007 Graphical User Interface Ribbon cannot be as easily changed or modified like it has been in previous versions.

This may be partially true, but not all is lost.

Here is some information from the equine's mouth:


Learn how to customize the Ribbon user interface (UI) in the 2007+ Microsoft Office release. Also learn how new features in Microsoft Visual Studio 2005 Tools for the 2007+ Microsoft Office System support RAD development of Ribbon customization. (40 printed pages)

Customizing the Office Ribbon


Monsieurs MS also have a downloadable spreadsheets with the Control IDs. There are files for 2003 as well.

Lists of Control IDs


2007 Office System Add-In: Icons Gallery

2013 Icons Gallery



See all Topics

Labels: , , , ,


<Doug Klippert@ 3:17 AM

Comments: Post a Comment


  Thursday, May 11, 2017 – Permalink –

Properties Report

Record of Records



If you would like a report that includes all of the properties, relationships and permissions of the objects in your database, go to Tools>Analyze and choose Documenter...

Access will create a VERY detailed report.

Access Documentation


Ezy Documenter



See all Topics

Labels: , , , ,


<Doug Klippert@ 3:12 AM

Comments: Post a Comment


  Thursday, May 04, 2017 – Permalink –

Define Relationships by Keyboard

It's not a drag


If you've played with Access, you know that you can create relationships by dragging fields from one table to another.

There is another way to do it using just the keyboard.

I don't know why you would do it this way, but let's assume you lent your mouse to your brother-in-law for the week-end. (?)


  1. Close any open tables.

  2. Use F11 to switch to the Database window.

  3. Press ALT+T to select the Tools menu, and then press R to open the Relationships window.

  4. If the Show Table dialog box does not appear, press ALT+R to select the Relationships menu, and then press T to open the Show Table dialog box.

  5. In the Show Table dialog box, select the first table that you want to relate, and then press ENTER to add it to the Relationships window.

  6. Repeat step 5 for any other tables you want to relate, and then press ALT+C to close the Show Table dialog box.

  7. Press ALT+R to select the Relationships menu, and then press R to open the Edit Relationships dialog box.

  8. Press ALT+N to open the Create New dialog box.

  9. In the Left Table Name box, select the name of the table that contains the primary key.

  10. In the Right Table Name box, select the name of the table that contains the foreign key.

  11. In the Left Column Name box, select the primary key field, and in the Right Column Name box, select the foreign key field.

  12. Press ENTER.

  13. In the Edit Relationships dialog box, use the arrow keys to make sure that the two columns contain the field names you want.

  14. Press ALT+C to create the relationship.
Support.Microsoft.com:
Define relationships by using the keyboard  



See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:20 AM

Comments: Post a Comment


  Wednesday, May 03, 2017 – Permalink –

Font Properties Plus

Everything you need to know


To embed a font in a document or slide show so it can be displayed on any other machine, the font must support that action. The standard Windows properties statement does not show all the needed information.

The bottom of this illustration shows the standard information shown when you right-click a font file, and choose properties. The two views at the top are what appear when the Microsoft Font properties extension is installed.


Font Properties

If you right click on a font file in Windows its basic properties are displayed. The Font properties extension adds several new property tabs to this properties dialog box. These include information relating to font origination and copyright, the type sizes to which hinting and smoothing are applied, and the code pages supported by extended character sets.

It also will tell you if the font can be embedded and/or edited in a document.



Protected
The font may not be embedded, copied, or modified. If you use a protected font in a document and if the document is opened on a computer that does not have the font installed on it, a font substitution occurs. Word substitutes the closest font available on the computer for the missing protected font.
Print/Preview
The font is embedded and temporarily loaded on the target computer. Documents that contain print/preview fonts must be opened read-only, and no edits are stored in the document. Embedding a font of this nature has the least impact on file size increase.
Editable
The font behaves just like the print/preview fonts, except that you may also apply the font to other text in the same document.
Installable
The font is installed on the target computer permanently when you open the document. This allows you to use the new fonts as if you installed the fonts directly into Windows yourself. This type of embedded font has the greatest impact on file size because the entire font or fonts are included with the document.


Versionand Features tabs
The Version tab includes version and date information. The Features tab describes the font in terms of number of glyphs, number of kerning pairs, the possible existence of a euro symbol and the presence of embedded bitmaps within the font.

Linkstab
If a font doesn't include a Web site URL, but does include a 'vendor ID code' a link will be provided to Microsoft's font vendor database.

Font properties extension, version 2.3
(32-bit only)


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:51 AM

Comments: Post a Comment


  Wednesday, April 26, 2017 – Permalink –

Search the Way You Want

It's your choice


Using Find in Access can be frustrating when you forget to change the search options.

You can change the defaults to something closer to your personal search preferences.

To do so, select Tools>Options from the menu bar (the Office Logo in 2007).

Then, click on the Edit>Find tab (under Advanced in 2007) and select the option button you want from Default Find>Replace Behavior.


  • Fast Search is the default, and causes Access to search the current field for an exact match to your criteria.

  • General Search, searches all fields and matches any part of the field.

  • Start Of Field Search, searches the current field for matches between your search string and the beginning characters in the field.

When you've made your selection, click OK.


See all Topics

Labels: , , ,


<Doug Klippert@ 3:42 AM

Comments: Post a Comment


  Wednesday, April 19, 2017 – Permalink –

View Related Fields

The whole thing


When designing a query, you'll often find the need to temporarily view fields beyond the ones you want shown in the final result.

To do so, you probably drag the relevant fields to the design grid and then delete them when you're done. However, there's an easier way to view the data that you typically want excluded in the query.

To do so:
  1. Open the query in Design view.
  2. Display the query's Properties sheet
  3. Set the Output All Fields property setting to Yes
  4. Run the query
Data from all of the underlying tables' fields will be displayed in addition to the fields you specified in the QBE grid. To change the query so that it only displays the fields explicitly selected in the QBE grid, simply
reset the Output All Fields property to No.


 


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:04 AM

Comments: Post a Comment


  Wednesday, April 12, 2017 – Permalink –

Progress bar on Forms

Show movement


You can display computer time by using the Microsoft Progress Bar Control. In earlier versions of Access, it can be found by using the "More Controls icon in the Toolbox. In Access 2007 it's under "Insert Active X Control".



Here are the instructions from FunctionX.com:
Progress Clock


See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:07 AM

Comments: Post a Comment


  Sunday, April 09, 2017 – Permalink –

Template Videos

See how it's done

Access 2013-16 template videos available.

"Access  ships with Web database templates, each of which does a great job of showing off the new features. We've created videos about how to use each template, as well as a video showing you how to modify a template by adding a field."

Support.Office.com


See all Topics

Labels: , , ,


<Doug Klippert@ 3:00 AM

Comments: Post a Comment


  Saturday, April 01, 2017 – Permalink –

SS Zip Codes

Excel to Access

Social Security Numbers and ZIP Codes can present special challenges when importing them into Access from Excel. This tip explains how you can prepare the data for the smoothest transition possible.
ExcelRibbon.Tips.net


See all Topics

Labels: , , ,


<Doug Klippert@ 3:42 AM

Comments: Post a Comment


  Saturday, March 25, 2017 – Permalink –

Animated Forms

Add action

"Publishing your Access forms on the Web? Add some fun with animated images! For example, say you want to publish a form that allows your users to enter a keyword that will search your software catalog for winter specials; to illustrate this, you’ve embedded an image of two boys hitting the a puck with their hockey sticks."

TechRepublic.com


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:10 AM

Comments: Post a Comment


  Saturday, March 18, 2017 – Permalink –

PPT from Access

Automation

"Create a PowerPoint slide presentation from scratch using Access data. In addition, display and control a slide show from within an Access form. Walk through the solution and explore ways to extend the sample for your own applications."
MSDN.Microsoft.com


See all Topics

Labels: , ,


<Doug Klippert@ 3:22 AM

Comments: Post a Comment


  Saturday, March 11, 2017 – Permalink –

Access Forum II

Answers and samples


"The premier Microsoft Access help forum with Visual Basic, SQL Server, Office and more! Members are able to post questions and help others with problem solutions. UtterAccess has been seen in over 160 countries, recording over 825,000,000 hits to date. Microsoft Access Tables, Queries, Forms, Reports, Pages, Macros, Modules...you'll find all the free samples and answers here!"
Utter Access


See all Topics

Labels: , , ,


<Doug Klippert@ 3:12 AM

Comments: Post a Comment


  Friday, March 03, 2017 – Permalink –

Getting Started with Access

2010, but still good

A new look and new features designed to help you get your work done more easily than ever. You'll see differences right away, starting with the Getting Started with Microsoft Office Access.

Office.Microsoft.com


See all Topics

Labels: , , ,


<Doug Klippert@ 3:16 AM

Comments: Post a Comment


  Saturday, February 25, 2017 – Permalink –

Count Unique

Distinct entries

A common query is needed to determine the number of different items in a data base. Here is the code:

YouTube


See all Topics

Labels: , , ,


<Doug Klippert@ 3:01 AM

Comments: Post a Comment


  Friday, February 17, 2017 – Permalink –

Grouping in Sections

Enhance grouping in your Access reports


Discover a quick way to put these groups in sections and then enhance these sections by providing summary details about the group.

TechRepublic.com


See all Topics

Labels: , , ,


<Doug Klippert@ 3:09 AM

Comments: Post a Comment