TIP: Enforce UpperCase Input Mask

VBA Ascii values 97-122 represent the lowercase letters a-z and 65-90 the uppercase letters A-Z: each uppercase letter's value is 32 less than the corresponding lowercase value.

In a control's KeyPress event procedure add the following code:

Private Sub txtMyName_KeyPress(KeyAscii As Integer)

If KeyAscii >= 97 And KeyAscii <= 122 Then
KeyAscii = KeyAscii - 32
End If

End Sub


Double Quotes Demystified

In string variables and when constructing SQL strings in VBA, using double-quotes can be confusing and frustrating. Try these approaches:

1. Create a global constant to represent double quotes, which makes code easier to read:

Public Const pubconQuotes = """"

Example: strSQL = "Instr(MyField," & pubconQuotes & " " & pubconQuotes & ")<>0"

2. Example: strSQL = "Instr(MyField," & chr(34) & " " & chr(34) & ")<>0"


TIP: Alternate line shading in MS Access Reports

1. Declare a constant in the Declarations of the Report's module:

Const vbLightGrey = 12632256

2. Add the following code to the Detail section OnFormat Event:

If Me.CurrentRecord Mod 2 = 0 Then
Me.Section(acDetail).BackColor = vbLightGrey
Else
Me.Section(acDetail).BackColor = vbWhite
End If

The Report will display a white background for odd records and a light gray background color for even records. The BackStyle of record TextBoxes should be set to Transparent.