Automatic Input masks

Private Sub Country_AfterUpdate()
Dim strCountry As String
strCountry = Me.Country

    Select Case strCountry
        Case "Canada"
            Me.[PostalCode].InputMask = ">L0L\ 0L0;;_"
        Case "USA"
            Me.[PostalCode].InputMask = "00000-9999;;_"
        Case Else
        'If the country is not Canada or USA no input mask will be used
            Me.[PostalCode].InputMask = ""
    End Select
End Sub

comp.databases.ms-access forum

Working with postal codes in Access

As a rule, if you won't be performing numeric calculations on the data, entries should be stored as text. Social Security numbers, Phone numbers and postal codes should be stored as text.


You can use alphabetic characters in an input mask. For example, one of the sample input masks is >L0L\ 0L0 used to represent a Canadian postal code.

The ">" character in the input mask converts all the characters that follow to uppercase.

The "L" character requires an alpha entry; the "0" (zero) requires a numeric entry.

A "\"character causes the following character to be displayed as a literal character rather than a mask character.

A space appears between the three character pairs.
For example, V5P 2G1 is one valid postal code that the user could enter. The mask would prevent the user from entering two sequential alphabetic characters or numbers.

See:

Trinity University - San Antonio, Texas:
Input mask

Definition characters used to create an input mask
Some validation rules

You can manipulate postal codes in Access by changing the data type, input mask, or format of a postal code field.

Microsoft KB 207829:
ACC2000: How to Manipulate ZIP Codes in Microsoft Access.

Also see:
Postal Codes



See all Topics

access

Labels: Entries, Forms, Macros, VBA

A second chance

"In Microsoft Office Access 2007, by default, users are not prompted to confirm changes after modifying and saving records on a form. But often you might want to prompt users to confirm their changes before the record is saved.

You can use a BeforeUpdate event procedure to display a confirmation prompt and handle a user's response to either cancel or continue with the save.

This visual how-to topic illustrates how to display a custom dialog box to prompt users to cancel or continue with saving changes to a record.

Labels: Macros, Properties, Tutorials

Without navigation

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: Forms, Macros, Tips

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.


Sam's Publishing:
Taking More Control of Access
By Gordon Padwick.


Access 2007 introduces 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: Macros, VBA

Tag Property

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

Labels: Macros, Properties, VBA

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

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.



See all Topics

access

Labels: Customize, Macros

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. The maximum you can set is 65,535.
(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

access

Labels: Macros, Properties

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.

Ross, 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

access

Labels: Macros, VBA