Unofficial Access Stuff

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

Host your Web site with PureHost!

Thursday, March 22, 2018 – Permalink –

VBA Named Arguments

An easier read

Use named arguments for cleaner VBA code.

Most likely, you use positional arguments when working with VBA functions. For instance, to create a message box, you probably use a statement that adheres to the following syntax:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

When you work the MsgBox function this way, the order of the arguments can't be changed.

Therefore, if you want to skip an optional argument that's between two arguments you're defining, you need to include a blank argument, such as:

MsgBox "Hello World!", , "My Message Box"

Named arguments allow you to create more descriptive code and define arguments in any order you wish. To use named arguments, simply type the argument name, followed by :=, and then the argument value.

For instance, the previous statement can be rewritten as:

MsgBox Title:="My Message Box", _ Prompt:="Hello World!"

(To find out a function's named arguments, select the function in your code and press [F1].)

See all Topics access
Labels: VBA

<Doug Klippert@ 3:53 AM

Comments: Post a Comment

Tuesday, March 13, 2018 – 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 access
Labels: Customize, General, Macros, Reference, Shortcuts, Tips, Tutorials, VBA

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

Tuesday, February 20, 2018 – Permalink –

Convert Access Macros to VBA

Macros to Modules

Before Access 2000, the speculation was that Access would lose "Macros" and enter the exclusive world of VBA. It hasn't happened yet.

If you have macros in a database that you would like to convert to code, doing so is easy.

In Access 97: Right-click on the macro in the Database window and then choose Save As/Export from the shortcut menu. Then, select the Save As Visual Basic Module option button and click OK. You are then given the option of adding error handling functions and comments to the new module. Select the options you want and click Convert.

In Access 2000/2002+: Right-click on the macro in the Database window and then choose Save As from the shortcut menu. Enter the name of the module you want to create in the text box and choose Module from the As dropdown list. Next, click OK. You will be given the option of adding error handling functions and comments to the new module. Select the options you want and click Convert.

In 2007 go to Database Tools and look in the Macros group.

Pearson Informit:
Taking More Control of Access
By Gordon Padwick.

Access 2007 introduced a new type of macros called embedded macros. Embedded macros are macros that are stored on an event instead of as a separate object. Embedded macros support name fix-up and are used extensively through-out our templates. They are largely targeted to information workers that don’t write code but useful for developers that are trying to perform some simple actions.

See all Topics access
Labels: Customize, General, Macros, Reference, Shortcuts, Tips, Tutorials, VBA

<Doug Klippert@ 3:21 AM

Comments: Post a Comment

Tuesday, January 23, 2018 – Permalink –

Office VBA Tricks

Video + Free code

"Learn tips and use sample code for several Office applications. These tips can help you to be more productive and can also be a starting point for developing your own tools, utilities and techniques."

Update Word Document Statistics in the Title Bar

Create Outlook Rules Programmatically

Delete Repeated Text Throughout a Word Document

Run Macros Based on the Value of One or More Excel Spreadsheet Cells

Disable Related Controls on a PowerPoint Slide After a User Clicks an Input Control

Display Reminder Information When a User Opens an Office Document

Synchronize an Access Main Form to a Subform and Vice Versa

Log Worksheet Changes to an XML File

Merge Body Text from Multiple Outlook E-mail Messages to a Word Document

Use the Office Assistant as an Alternative to Displaying and Retrieving User Input

Ten Tips for Office VBA Developers

VBA Tips & Tricks

Getting Started with VBA in Office 2010

Download Office 2013 VBA Documentation

(VBA is VBA and is, in most cases, usable in all versions of Office)

See all Topics access
Labels: Reference, Tips, Tutorials, VBA

<Doug Klippert@ 3:01 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.

Create your table with an AutoNumber type field, but don't enter any records.

Create another table with only a single Long Integer Number type field.

This field must have same name as the AutoNumber field in the first table.

Enter one record in the second table that is a number one less than the required start of the AutoNumber for the first table.

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 access
Labels: Customize, General, Reference, Tips, Tutorials, VBA

<Doug Klippert@ 3:15 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 access
Labels: General, Reference, Shortcuts, Tips, Tutorials, VBA

<Doug Klippert@ 3:33 AM

Comments: Post a Comment

Wednesday, August 31, 2016 – Permalink –

Close Forms

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

Open the first form in Design view.

Double-click the Form Properties button.

Click on the Event tab.

Click in the On Deactivate text box.

Select Event Procedure.

Click the Build button.

At the prompt, enter: Me.TimerInterval =1.
(Try something like 30000 milliseconds)

Press [Alt][Q].

In the Form Properties window, click in the On Timer property text box.

Select Event Procedure.

Click the Build button.

At the prompt, enter: DoCmd.Close.

Press [Alt][Q].

Save the form.

Microsoft Support

See all Topics access
Labels: Forms, General, Reference, Shortcuts, Tips, Tutorials, VBA

<Doug Klippert@ 3:06 AM

Comments: Post a Comment

Saturday, August 27, 2016 – Permalink –

List Fields in Access Table

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 access
Labels: General, Reference, Shortcuts, Tables, Tips, Troubleshoot, VBA

<Doug Klippert@ 3:19 AM

Comments: Post a Comment

Sunday, April 17, 2016 – Permalink –

VBA Help

From Microsoft

Here is a Help reference covering the vagaries of VBA.

Office VBA Language Reference

See all Topics access
Labels: VBA

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

Sunday, September 27, 2015 – 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:

Allen Browne:
Field highlighting solutions

See all Topics
Labels: Customize, Entries, General, Reference, Tables, Tips, Tutorials, VBA

<Doug Klippert@ 3:42 AM

Comments: Post a Comment

Saturday, July 11, 2015 – 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]))

Download a sample from:
ACCESS Watch

See all Topics access
Labels: Functions, General, Macros, Reference, Shortcuts, Tips, Tutorials, VBA

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

Sunday, June 28, 2015 – 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 access
Labels: Functions, General, Reference, Tutorials, VBA

<Doug Klippert@ 3:57 AM

Comments: Post a Comment

Tuesday, April 28, 2015 – Permalink –

Number Entries

Beyond AutoNumber

Embedding information in a Primary key or ID, can lead to trouble in the future.
(If the first three numbers are to represent the warehouse address, what happens if new addresses have four numbers?)

Autonumbering can give a false sense of order. There is an initial tendency to try to keep all database records in some order. This violates the sense of a relational database.

The records can be sorted or filtered as needed.

Still some record numbering scheme may be desired.

Allen Browne's Access tips:
Numbering Entries in a Report or Form

"In relational database theory, the records in a table cannot have any physical order, so record numbers represent faulty thinking. In place of record numbers, Access uses the Primary Key of the table, or the Bookmark of a recordset. If you are accustomed from another database and find it difficult to conceive of life without record numbers, check out What, no record numbers?"

See all Topics access
Labels: Entries, General, Reference, Tables, Tips, Tutorials, VBA

<Doug Klippert@ 3:16 AM

Comments: Post a Comment