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



  Sunday, November 19, 2017 – Permalink –

Runtime and PDF

Add-in's complete


Great news, you can now redistribute and use the PDF and XPS add-in with your Runtime solution.
The Runtime's code has not been changed. Your existing runtime solutions can now officially be distributed with the PDF and XPS add-in by chaining the .msi for PDF and XPS into your install process for your app (after the Runtime).

You may copy and distribute the object code form of the add-in listed below together and for use only with the Microsoft Office Access Runtime software, subject to the license terms accompanying the Microsoft Office Access 2007 Runtime software download:
2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS (located at PDF add-in download)

Runtime for Access


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:40 AM

Comments: Post a Comment


  Monday, November 13, 2017 – Permalink –

Prevent Users From Copying Field Text

Copy or select


As you know, you can set a form field's Locked property to Yes to prevent users from changing the underlying data. However, users are still able to select and copy data from the field, and you may not always want this to be the case. The solution is to also set the field's Enabled property to No.

Ordinarily, setting the Enabled property this way causes the field and its associated label to be difficult to read. However, when you set the Enabled property to No and the Locked property to Yes, the fields and labels look exactly as they do when they're enabled for normal entry. The difference is that users will be unable to select or copy any of the displayed data.


See all Topics

Labels: , , ,


<Doug Klippert@ 3:45 AM

Comments: Post a Comment


  Friday, November 03, 2017 – Permalink –

Total Footer

Sum() it up



If you would like to show a total in the footer or each page of a report, you may have a problem. Access does not allow the SUM() function in the footer.

The way around this is to put a SUM() function in an unbound text box in another part of the report.

Choose Properties and set the visible property of the control to No.

In the footer, create another control using the "calculation" text box as the ControlSource.

The Sum() function, as well as the other aggregate (totals) functions can reference only a field and not a control.

From the Microsoft Knowledge base:

How to Sum a Calculation in a Report

How to Display and Total Subtotals from Subreports



See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:12 AM

Comments: Post a Comment


  Thursday, October 26, 2017 – Permalink –

Insert Line Breaks With Code

Label Captions


If you've ever needed to insert line breaks in a message box prompt, you most likely built a string that incorporated a line feed or carriage return character. Unfortunately, label objects aren't as forgiving when it comes to using these characters.

If you're setting a label's Caption property with code, you'll find that the special control characters are interpreted as squares, since they're otherwise un-displayable.
To successfully insert a line break in a label caption, you need to include both a line feed character and a carriage return character, entered consecutively.

To do so, you can use the Chr() function, such as:

Me.Label1.Caption = "Line 1" & _
Chr(13) & Chr(10) & "Line 2"

However, you can also simplify your code using an built-in constant:
Me.Label1.Caption = "Line 1" & vbCrLf & "Line 2"



See all Topics

Labels: , , ,


<Doug Klippert@ 3:06 AM

Comments: Post a Comment


  Wednesday, October 25, 2017 – Permalink –

Zoom Box

Better view


Access does not provide much room to enter long expressions in queries, forms, or reports.
You can drag the column wider, but there is a neater, quicker method.

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

New Folders


BTW: If you enter Field names in the Zoom box without square brackets, if the fields are recognized, Access will add the brackets.


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:38 AM

Comments: Post a Comment


  Thursday, October 19, 2017 – Permalink –

Place Controls Where You Want

Works with other apps as well


The Snap To Grid feature is an invaluable tool for aligning controls when you're designing forms and reports. However, when you fine-tune the placement of some controls, you'll probably want to move some of them to positions that aren't exactly aligned with the design grid.

You can temporarily disable the Snap To Grid feature by holding down the [Ctrl] key. Then, you can use your mouse or the cursor arrows to place the controls exactly where you want them.


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:09 AM

Comments: Post a Comment


  Saturday, October 14, 2017 – Permalink –

Hardcopy Relationships

Document database


When you're documenting your database applications, you may want to include the same visual diagram of your table relationships that's available through the Relationships window.
In Access 2000 thru 2003, this is easy. Simply display the Relationships window as usual and then choose File>Print Relationships from the menu bar. Doing so displays a report preview that you can then print or save.

In 2007-13, to just print out a report, find Database tools on the Ribbon and click on Database Documenter.

Relationships are at the bottom of the All Object Types tab




See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:17 AM

Comments: Post a Comment


  Friday, October 06, 2017 – Permalink –

Startup Switches for Access

Your choice


"This article shows you how to customize the way that Microsoft Office Access 2007+ starts by adding switches and parameters to the startup command. For example, you can have Office Access 2007+ open a specific file or run a specific macro when it starts."


Office.Microsoft.com

Also:

Support.Microsoft.com

and

VB123.com


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:47 AM

Comments: Post a Comment


  Sunday, October 01, 2017 – Permalink –

Use a Table Alias

to change names in a query


When you need to change the table name referenced in an existing Access query, it can be a pain. This is especially true if the query contains a large number of fields.

You typically might use an alias when you need to relate a table to itself or tables have long or unwieldy names.

If you make a practice of always using aliases in your queries, you can easily change which table is used by changing the one occurrence of the original table name in the query's FROM clause.
  1. To set a table alias in the query's Design view,
  2. Right-click on the table field list and choose Properties.
  3. Then, enter the alias name you want to use in the Alias text box.




See all Topics

Labels: , , , ,


<Doug Klippert@ 3:10 AM

Comments: Post a Comment


  Monday, September 25, 2017 – Permalink –

Access Tutorials

Learning Sites



Microsoft has created a web site for Office. Part of the site is devoted to tutorials touching on:

Word, Access, Publisher , Outlook, PowerPoint, InfoPath , Excel, Visio, OneNote, Expression, and Project.

The Access lessons are located here:

2013 Access Tutorials

Here's another site:

FunctionX Access tutorials



See all Topics

Labels: , , ,


<Doug Klippert@ 3:26 AM

Comments: Post a Comment


  Friday, September 22, 2017 – Permalink –

Flag Access Controls

Tag Property


The TAG property allows you to associate up to 2,084 characters of text with any form, report, section, or control. This is especially helpful when you want to single out a specific subset of controls.

For instance, say that you want to hide certain controls on a form when a user clicks a button.
You can flag which controls will be hidden by entering the word "Hide" (or any other consistent word) in each control's Tag property. Then, attach the following code to the command button's Click event procedure:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "Hide" Then
ctl.Visible = False
End If
Next




See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:16 AM

Comments: Post a Comment


  Sunday, September 17, 2017 – Permalink –

Auto Number

Don't be smart



There should not be any "intelligence" in an AutoNumber field. It is meant as an index field and not anything else.

If the need should arise to reset the field, if your table does NOT contain any records, simply compacting the database again will set the Autonumber field back to 1.

Another way would be to delete the AutoNumber field and re-insert it in the table.

Here's a long way to start at a specific number.
  1. Create your table with an AutoNumber type field, but don't enter any records.
  2. Create another table with only a single Long Integer Number type field.
  3. This field must have same name as the AutoNumber field in the first table.
  4. Enter one record in the second table that is a number one less than the required start of the AutoNumber for the first table.
  5. Now create an append query to append the record in the second table to the first table and run the query.
You can now delete the second table and begin entering your data into the first table.
Also: Access AutoNumber Reset
"This is some sample code that shows how to programmatically reset all AutoNumber fields in an Access Database to a correct value (whether it be 0 or the max value + 1). In addition, it contains code for Compacting and Repairing an MS Access Database. This is perfect for people who are working with a complicated Access Database and have experienced AutoNumber bugs!
And: Creating an AutoNumber field from code  


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:15 AM

Comments: Post a Comment


  Friday, September 15, 2017 – Permalink –

Create Hyperlinks in Access Tables

Simply


When you populate a hyperlink field in a table, you probably cut and paste the URL from your browser into the Insert Hyperlink dialog box.
There's an even easier way to do this using Internet Explorer.

  1. First, open the Access table you're updating and Internet Explorer.

  2. Select the hyperlink field you want to create the link in and choose Insert >Hyperlink from the menu bar.

  3. Press Alt+Tab or use your mouse to select the browser window.

  4. Browse to the page you want to link to and then switch back to Access.
You'll find that the URL is automatically inserted in the dialog box.


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:00 AM

Comments: Post a Comment


  Sunday, September 10, 2017 – Permalink –

Split Access Database

Separate tables



You don't need to keep all of your data in one file. You can split your MDB file into data and application files.

"Even if all your data is in Access itself, consider using linked tables. Store all the data tables in one MDB file - the data file - and the remaining objects (queries, forms, reports, macros, and modules) in a second MDB - the application file.
In multi-user situations, each user receives a local copy of the application file, linked to the tables in the single remote data file."

  • Maintenance: To update the program, just replace the application file.
    Since the data is in a separate file, no data is overwritten.

  • Network Traffic: Loading the entire application (forms, controls, code, etc)across the network increases traffic making your interface slower.
In some cases you will link additional files:
  • Static look-up data such as postal codes might be kept in its own file.
  • Linked temporary tables might avoid the need to compact the application file.
From Allen Brown's tips for Access users
Access has a tool to do the splitting for you, go to: Tools>Database Utilities Database Splitter
In Access 2007+:
  1. On the Database Tools tab, in the Move Data group, click Access Database.
  2. In the Database Splitter dialog box, click Split Database.
  3. Type a name for the back-end database, and then click Split.
Also:
Knowledgebase:
How to manually split a Microsoft Access database

MSDN:
About sharing an Access database on a network  


See all Topics

Labels: , , ,


<Doug Klippert@ 3:47 AM

Comments: Post a Comment


  Thursday, September 07, 2017 – Permalink –

Runtime for Access

Free Download !


Access is not included in the basic Office 2007+ suites. If you want to distribute your database projects to others in the office, you'll need this download.

The Access 2003 Developer Extensions cost about $500. The 2007-10 downloads are free!


"Microsoft Office Access 2007 provides a rich platform for developing database management solutions with easy-to-use customization tools.

If no end-user customization is required (including report modifications), you can choose to distribute those Access 2007 solutions so that they run without requiring a full installation of Access 2007.

To do so, you must package and distribute your application with the Access Runtime.

The Access Runtime is similar to previous runtimes in that all design-related UI is either removed or disabled.

You do not need to buy any special SKU in order to redistribute the Access Runtime. You can freely redistribute it or point users to this download."


Access 2007 Runtime

Access 2010 Runtime

Access 2013 Runtime


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:36 AM

Comments: Post a Comment


  Saturday, September 02, 2017 – Permalink –

Indent Code

Realign a bunch


Indenting blocks of VBA code, such as statements within loops or If...Then statements, makes reading a procedure much easier.

You probably indent a code statement using the [Tab] key, and outdent by using [Shift][Tab].

However, you may not be aware that the [Tab] and [Shift][Tab] techniques also work when multiple code lines are selected.

The Visual Basic Editor also provides Indent and Outdent buttons on the Edit toolbar that allow you to easily reposition blocks of code.


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Saturday, August 26, 2017 – Permalink –

Access Field Highlighting

More code


This technique can also be applied to controls like option groups.

Instead of using OnGotFocus and OnLostFocus events you must use the OnEnter and OnExit events.

In addition, the control group's BackStyle property must be set to Normal to take advantage of the Windows color scheme:

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 = vbHighlight
ctrl.ForeColor = vbHighlightText
ElseIf Stat = "LostFocus" Then
ctrl.BackColor = vbWindowBackground
ctrl.ForeColor = vbWindowText
End If
End Function

Take advantage of global constants. Just add the following two statements to a module:

Global Const Orange = 39423
Global Const LightBlue = 16776960

Then, set the OnGotFocus and OnLostFocus events for the controls in the following format:
Private Sub controlName_GotFocus()
controlname.BackColor = Orange
End Sub
Private Sub controlName_LostFocus()
controlname.BackColor = LightBlue
End Sub

Highlight data on forms by using conditional formatting


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:17 AM

Comments: Post a Comment


  Friday, August 18, 2017 – Permalink –

Zeros - Before and After

Nothing's a problem



"When you import data into Microsoft Access, trailing zeros may be lost. This will happen when you import data that is formatted to show these zeros, but where the zeros are not actually part of the data.
For example, in a Microsoft Excel workbook, you can format the number 1234 so that it will be displayed as 1234.000. When you import this workbook into a Microsoft Access table, the number will be displayed as 1234.
This article shows you how to preserve trailing zeros when you import data into Microsoft Access."


How to Preserve Trailing Zeros When Importing Data
Also:

Word — Decimal Point or Trailing Zeros Missing When You Merge Microsoft Access Database

Excel — Using a Custom Number Format to Display Leading Zeros


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:32 AM

Comments: Post a Comment


  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