Saturday, May 15, 2004

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
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.

No comments: