Tutorial Archive

How to use the Pen Tool

2012-04-04T05:15:00.000-07:00

Pen Tool

2012-04-04T05:06:00.001-07:00

Access Birthdays

2012-03-07T08:07:00.000-08:00

Access: Form Formatting Notes to Sticky Paper

2012-03-07T07:54:00.000-08:00

Quark express: Item tool

2012-03-06T10:50:00.000-08:00

MS Access: Add an independent label on a form in Access 2003/XP/2000/97

2012-02-25T00:05:00.000-08:00

You've probably seen labels get created on a form whenever you create a Text Box, Combo Box, etc. But you can also create an independent label that is not attached to any other object.
To do this, open your form in Design View. Select the Label button in the Toolbar. Then click on the form where you'd lke the label appear and enter the text that you'd like to display in the label.

In this example, we've created a label that displays the text "Product Information".

MS Access: Set up a text box to display the difference between two dates (display value of 1 if dates are the same) in Access 2003/XP/2000/97

2012-02-25T00:04:00.000-08:00

You can create a Text Box on your Form or Report. Then set the Control Source property of the Text Box to the following formula:

=IIf([From_Date]=[To_Date],1,DateDiff("d",[From_Date],[To_Date]))

MS Access: Set the value of a textbox based on the value of another textbox in Access 2003/XP/2000/97

2012-02-25T00:03:00.002-08:00

To set the value of Textbox2 based on the value entered in Textbox1, you need to place your VBA code on the "After Update" event of Textbox1.

To do this, open your form in Design View.

Under the View menu, select Properties. Highlight the textbox called Textbox1. Click on the property called "After Update". A button with 3 dots to the right should appear. Click on this button.

When the Choose Builder window appears, highlight Code Builder. Click on the OK button.

Then place the following code:

Private Sub Textbox1_AfterUpdate()

If Textbox1.Value = "1" Then
Textbox2.Value = "10"
End If

End Sub

Now, when a value of 1 is entered in Textbox1, Textbox2 will automatically be populated with a value of 10.

MS Access: Open a Form from another Form (disabling the parent form until the child form is closed) in Access 2003/XP/2000/97

2012-02-25T00:03:00.001-08:00

In Access, you can open a form in different modes. In this situation, you want to open the form as a dialog. A dialog disables the parent form (ie: original form) until the new form is closed.

The command that you want to use to open a form as a dialog is as follows:

DoCmd.OpenForm "Calendar", , , , , acDialog

In this example, we are opening a form called Calendar as a dialog.

The acDialog option on the OpenForm command specifies that the parent form (ie: the original form that opened the Calendar form) will not be accessible until the Calendar form is closed.

MS Access: Set the focus on the first field in a form whenever a record is viewed in Access 2003/XP/2000/97

2012-02-25T00:02:00.000-08:00

To make sure that the focus is on the first field whenever a new record is added...or for that matter, whenever you view a record in the form, try the following VBA code on the form's "On Current" event:

First_Field.SetFocus

For example, if your first field was called CompanyName, you would use the following VBA code:

Private Sub Form_Current()

CompanyName.SetFocus

End Sub

MS Access: Replace system error message with a custom message in Access 2003/XP/2000/97

2012-02-25T00:01:00.000-08:00

Yes, you can override the system error messages with your own messages. In your case, you want to place code on the Form's On Error event.

For example, you could place the following code on the Form's On Error event to trap the primary key violation:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3146 Then
MsgBox ("You have violated the primary key.")
Response = 0
End If

End Sub

Now, whenever a user tries to enter a record that violates the primary key, the following error message will appear:

MS Access: Change the tab order on a form with VBA code in Access 2003/XP/2000/97

2012-02-25T00:00:00.000-08:00

We've created a sample Access database that you can download that demonstrates how to change the tab order on a form.

Let's take a look at the example. Below, we have a form where the user can change the tab order by selecting a radio button.

If the user selects the "Order tabs down and then across" radio button, the tab order will be as follows: Supplier ID, Company Name, Contact Name, Contact Title, Phone, Fax, and Home Page.

If the user selects the "Order tabs across and then down" radio button, the tab order will be as follows: Supplier ID, Phone, Company Name, Fax, Contact Name, Home Page, and Contact Title.

This tab reordering is done by the following code:

Private Sub frameTabOrder_Click()

'Order tabs down and then across was chosen
If frameTabOrder.Value = 1 Then
[SupplierID].TabIndex = 0
[CompanyName].TabIndex = 1
[ContactName].TabIndex = 2
[ContactTitle].TabIndex = 3
[Phone].TabIndex = 4
[Fax].TabIndex = 5
[HomePage].TabIndex = 6

'Order tabs across and then down was chosen
Else
[SupplierID].TabIndex = 0
[Phone].TabIndex = 1
[CompanyName].TabIndex = 2;
[Fax].TabIndex = 3
[ContactName].TabIndex = 4
[HomePage].TabIndex = 5
[ContactTitle].TabIndex = 6

End If

End Sub

MS Access: Load a form into memory, but do not display it in Access 2003/XP/2000/97

2012-02-24T23:59:00.000-08:00

To load a form into memory, but not have it be visible, you can try the following VBA code:

DoCmd.OpenForm "Categories Form"
Forms![Categories Form].Visible = False

This code will load a form called Categories Form. Then it will make the form invisible, so that the user can not see it.

You can then close your form with the following VBA code:

DoCmd.Close acForm, "Categories Form"

MS Access: Sort a form's records based on the value of a calculated field in Access 2003/XP/2000/97

2012-02-24T23:58:00.000-08:00

Please note that this example will only work with an Access 2000 database or higher. You will need to start with a new Access 2000 file, not one that has been converted from a previous Access version.

To sort the records in the form based on a calculated field, you need to add the calculated field to the result set in the Record Source property. We've provided a download to demonstrate how this can be done.

We've created a form called frmAccounts that lists all records in the Accounts table. We've then created a textbox called TotalUnits that displays the number of records in the Account_units table (for the account displayed on frmAccount).

Open the form in Design view and select Properties under the View menu. Select the property called Record Source. A button with three dots should appear to the right of this property. Click on this button.

When the Query Builder appears, select all of the fields from the Accounts table and then enter the function for the calculated field. In this example, we've entered the following:

CalcUnits([AcctNo])

Next, select how you've like to sort the calculated field. We've chosen to sort in Descending order.

You can close down the Query Builder. When prompted with the message box, "Do you want to save the changes made to the SQL statement and update the property?", click on the Yes button.

Now when you open the form, you'll see the form records sorted in descending order based on the "Total Units" field.

MS Access: Display on a form, the number of related records from another table in Access 2003/XP/2000/97

2012-02-24T23:57:00.000-08:00

o display the number of records in the Account_units table for the current account displayed, you will need to create a custom function in a Module. Then reference this function on your form. We've provided a download to demonstrate how this can be done.

If you view the Properties for the TotalUnits textbox, you can see that the Control Source property is set to:

=CalcUnits([AcctNo])

This textbox is linked to a custom function that we've created called CalcUnits. You can find this function defined in the Module called modGeneral in our sample database.

This function accepts the AcctNo as the parameter. It then queries the Account_units table and returns the number of records that exist for this AcctNo.

MS Access: Disable the Esc key in a form in Access 2003/XP/2000/97

2012-02-24T23:56:00.000-08:00

To disable the Esc key in an Access form, open your form in design view. Set the form's property called "Key Preview" to Yes.

Then select the property called "On Key Down" and click on the button with the three dots to the right.

When the Choose Builder window appears, highlight Code Builder and click on the OK button.

Next, paste in the following code:

If KeyCode = vbKeyEscape Then
KeyCode = 0
End If

Your code should look as follows:

Now, the Esc key should be disabled on your form.

MS Access: Set the background color of a button in Access 2003/XP/2000/97

2012-02-24T23:55:00.000-08:00

Unfortunately, you can't change the back color of a command button. As an alternative, you could create a label and have it behave as a button.

To do this, create a label. Right-click on the label and view its properties.

Set the Back Color property of the label to the color that you want. Then set the Special Effect property to Raised.

Then code it's Mouse Down event to:

[LabelName].SpecialEffect = 2

Code the Mouse Up event:

[LabelName].SpecialEffect = 1

Code the OnClick event as if it was a command button.

Center the label text horizontally using the text alignment tool button or setting the Text Align property to Center.

If you want to align text vertically, Press Shift + Enter then type in the label text.

It will look and act like a command button.

MS Access: Prevent the mouse wheel from scrolling through records in a form in Access 2003/XP/2000/97

2012-02-24T23:51:00.001-08:00

You'll have to download the following MouseWheel.dll file and copy it to each computer that you wish to prevent scrolling.

We recommend that you copy the MouseWheel.dll file to the following directory:

c:\Program Files\Microsoft Office\Office\

But you can copy this file to any directory that you wish. However, you should consistently copy this file to the same directory location on each computer.

Register the dll file

Next, you'll need to register the file. This is particularly important if you are running Access 97. However, if you are running Access 2000 or higher, it is still a good idea to register the file.

To do this, open a Command Prompt. Type the following command and press enter:

cd /program files/microsoft office/office

This will change your prompt to point to the directory where you've saved the MouseWheel.dll file. Now, we want to register the file. Type the following command and press enter:

regsvr32.exe mousewheel.dll

The following window should appear indicating that the MouseWheel.dll file was successfully registered.

Updating References in Access

Next, open your Access database and press Alt-F11. This will take you to the Visual Basic Editor.

Under the Tools menu, select References.

When the References window appears, click on the Browse button.

Find the MouseWheel.dll file that you saved to the computer. In this example, the MouseWheel.dll file can be found under c:\Program Files\Microsoft Office\Office\.

Click on the Open button.

Now when you return to the References window, the MouseWheel.dll file should appear with a checkmark to the left. Click on the OK button.

Add code to your form

Open your Form in design view and go to the Visual Basic editor for the form. You can do this by clicking on the button to the right of the On Open event for the Form.

When the Choose Builder window appears, highlight Code Builder and click on the OK button.

When the Microsoft Visual Basic window appears, paste in the following code:

Option Compare Database
Option Explicit

Private WithEvents clsMouseWheel As MouseWheel.CMouseWheel

Private Sub Form_Load()
Set clsMouseWheel = New MouseWheel.CMouseWheel
Set clsMouseWheel.Form = Me
clsMouseWheel.SubClassHookForm
End Sub

Private Sub Form_Close()
clsMouseWheel.SubClassUnHookForm
Set clsMouseWheel.Form = Nothing
Set clsMouseWheel = Nothing
End Sub

Private Sub clsMouseWheel_MouseWheel(Cancel As Integer)
Cancel = True
End Sub

The code should look as follows:

You can now close down the Microsoft Visual Basic editor. (save any changes, if prompted)

Save changes to your form. Now when you view your form and move the mouse wheel, the records should no longer scroll.

MS Access: Update a text box to a value when a new record is created in Access 2003/XP/2000/97

2012-02-24T23:50:00.000-08:00

On the Form object, there should be a "Before Insert" event. You should be able to place code there to set the value of the text box.

Let's take a look at an example. Often times, it is useful to know when a record has been created in your Access database. So we've created a Customers table that contains a field called Create_Date. We will use this field to store the exact date and time of when the record was created.

This is an example of a form that we've created. There is code on the "Before Insert" event of this form to update the field called Create_Date. When you are viewing this form in "form mode", you can not see the field called Create_Date.

However, if you view this form in Design View, you will notice that there is, in fact, a field called Create_Date. This field has been set to "not visible" so that the user can not interfere in the "Before Insert" event code that will run.

When a new record is inserted into the Customers table, the Create_Date field will be updated with the current system date/time.

The VBA code is quite simple:

Private Sub Form_BeforeInsert(Cancel As Integer)

'Set the Create_Date field in the Customers table to the current system
'date when a new record is created
Create_Date = Now()

End Sub

MS Access: Redraw a form in Access 2003/XP/2000/97

2012-02-24T23:46:00.000-08:00

Sometimes in Access, the system will not properly redraw the form after it has done extensive processing. If this occurs, you try calling the repaint method for the form object.

For example:

Form2.Repaint

In this example, there is a form called Form2. The Repaint method forces the form to redraw itself.

MS Access: Refresh data in a form in Access 2003/XP/2000/97

2012-02-24T23:45:00.000-08:00

To refresh the data in a form, you could create a button on the form and attach the following code to the On_Click event:

Private Sub Command1_Click()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub

In this example, we've created a button called Command1. When the button is clicked, the form will requery the data displayed in the form. If the data has changed, these changes will now be displayed in the form.

MS Access: Duplicate a record in Access 2003/XP/2000/97

2012-02-24T23:43:00.000-08:00

You could create a button on the form and attach the following code to the On_Click event:

Private Sub Command1_Click()

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

End Sub

In this example, we've created a button called Command1. We've placed the above code on the button's click event.

MS Access: Use a control in an SQL statement in Access 2003/XP/2000/97

2012-02-24T23:42:00.002-08:00

The first example that we'll take a look at involves using a control whose value contains a number. The following code uses a textbox control called txtSupplier_ID in an SQL statement in VBA code.

Dim LSQL as string

LSQL = "select * from Suppliers where Supplier_ID = " & txtSupplier_ID

The result set will return all records from the Suppliers table where the Supplier_ID equals the value that is found in the textbox control called txtSupplier_ID.

Our next example will look at a control whose value contains a string. The following code uses a control called txtSupplier_City in an SQL statement in VBA code.

Dim LSQL as string

LSQL = "select * from Suppliers where Supplier_City =' " & txtSupplier_City & "'"

You will notice in this example that you need to place a single quote in front of the control (txtSupplier_City) as well as one after. This is because string values must always be surrounded by single quotes in an SQL statement

This result set will return all records from the Suppliers table where the Supplier_City equals the value that is found in the textbox called txtSupplier_City.

MS Access: Prompt for changes to be saved in Access 2003/XP/2000/97

2012-02-24T23:42:00.001-08:00

Since Access automatically save changes directly to the database, the only way to stop this from happening is to intervene on the Form_BeforeUpdate event. At this point, you can prompt the user to save or cancel changes.

For example, you could place the following code on the BeforeUpdate event for the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim LResponse as integer Dim LMsg as string

LMsg = "Do you wish to save changes?"
LResponse = msgbox(LMsg, vbYesNo, "Save changes")

If LResponse = vbNo then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End if

End Sub

In this example, before the changes are saved, the user is prompted with a message box. This message box asks the user if he/she wishes to save the changes. If the user responds "yes", then the changes are saved. If the user responds "No", then the changes are undone and the record is not updated.

MS Access: Check if a form is loaded in Access 2003/XP/2000/97

2012-02-24T23:41:00.001-08:00

It is often useful in Access programming to know whether a form is already loaded. To do this, you can use the function IsLoaded.

For example:

If CurrentProject.AllForms("Suppliers").IsLoaded = True Then

.

End If

This code checks to see if the form called Suppliers has been loaded in the current Access session.

Here is an example of how we've used IsLoaded.

Function GetCity() as string

If CurrentProject.AllForms("Suppliers").IsLoaded = True Then
GetCity = Form_Suppliers.txtSupplier_City
Else
GetCity = ""
End If

End Function

This function is used to retrieve the value of the txtSupplier_City in the Suppliers form.

If the Suppliers form is loaded, it will return the value in the textbox called txtSupplier_City. If the form is not loaded, it will return a blank value. This helps us avoid errors if we try to access the txtSupplier_City value when the Suppliers form is not loaded.

You can then use this function in a Query or a Report. or for that matter, anywhere within your Access database.