Visual Basic

 

EXCLUDING HIDDEN TABLES

In the previous tip, we showed you how to exclude system tables from a list of table names. Excluding the names of hidden tables takes an additional step. First, here's the code we're using to list all tables--including system and hidden--in the Immediate window:

Dim aob As AccessObject
Dim obj As Object
Set obj = CurrentData.AllTables
For Each aob In obj
Debug.Print aob.Name
Next

As we discussed previously, you can omit system tables from the list using this code:

Dim aob As AccessObject
Dim obj As Object
Set obj = CurrentData.AllTables
For Each aob In obj
If Left$(aob.Name, 4) <> "MSys" Then
Debug.Print aob.Name
End If
Next

Now, to omit hidden tables as well, use this code:

Dim aob As AccessObject
Dim obj As Object
Set obj = CurrentData.AllTables
For Each aob In obj
If Left$(aob.Name, 4) <> "MSys" Then
If Not (Application.GetHiddenAttribute(aob.Type, aob.Name)) Then
Debug.Print aob.Name
End If
End If
Next

This last bit of code will exclude both system and hidden tables from the list. Depending on your needs, you may need to rewrite the conditions.
by Susan Sales Harkins
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CANCEL UNLOAD
We've talked about using the Unload method to close all open forms before quitting an application. However, you can override that method using the form's QueryUnload event. This is the very last event triggered when you unload a form. If you change this event's Cancel argument to True, as shown below, VB won't unload the form. This is a good way to avoid losing unsaved data. Simply set this argument to True right before you enter data and change it to False once the data is saved.

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Cancel = True
End Sub
by Susan Sales Harkins
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

WORKING WITH ENUMERATIONS
An enumeration is a special type of constant that automatically assigns values to its members. Using an enumeration is an easy way to work with a set of related constants. For instance, VB has a built-in enumeration named vbDayOfWeek, which contains constants for each day of the week. To see how it works, open a module and type

vbDayOfWeek

followed by a period. VB will automatically display a list of
vbDayOfWeek's constants.

To create your own enumeration, declare an enumeration type using the Enum statement in the Declarations section of a standard or public class module using the syntax

Private Enum WorkDays

or

Public Enum WorkDays

Then list the constant names. VB will assign the value 0 to the first constant named in the list and increase the value by 1 for each
subsequent constant in the list. For instance, the enumeration

Private Enum WorkDays
Monday
Tuesday
Wednesday
Thursday
Friday
End Enum

would assign the values 0 through 4, respectively, to the items
listed.

To use the enumeration, declare a variable using the enumeration in
the form

Dim DayOff As WorkDays
DayOff = workdayconstant

where workdayconstant is Monday, Tuesday, Wednesday, Thursday or Friday.
by Susan Sales Harkins
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CONTROLLING THE STATUS BAR
Users familiar with the Windows interface know they can usually find a bit of information about what's currently going on in their
application by viewing the contents of the Status bar. You can use this to your advantage by displaying your own information in the Status bar in a number of ways. One such way is to use the StatusBar property in the form:

Application.StatusBar = "message you want to display in status bar"

Access doesn't support the StatusBar property, but Access users can use the control property StatusBarText.
by Susan Sales Harkins
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ENUM DATA TYPES
In the previous tip, we showed you how to create your own enumeration constant. Although it appears that you're creating a unique data type, you really aren't. Visual Basic treats all constant values in an enumeration as Long integers. If you assign a decimal value to an enumeration constant, Visual Basic will round that value to the nearest Long integer. In fact, if you try to enter the decimal value directly, VB returns an error.

by Susan Sales Harkins

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DISPLAYING THE STATUS BAR
In the previous tip, we showed you how to use the StatusBar property to display messages in the Status bar. Of course, if you're going to display a message there, you'll also need to control whether the Status bar is available. You do this using the DisplayStatusBar property. We recommend, when beginning an application or task, you capture that property's setting, using the code:

Dim bar As Variant
bar = Application.DisplayStatusBar

If the Status bar is visible, bar will equal True; when the Status bar isn't available, the property returns False. Adding this bit of code to the beginning of a task or procedure allows you to quickly return the Status bar to its original state--regardless of what you do during the task--by using the statement:

Application.DisplayStatusBar = bar

Access users should use the StartupShowStatusBar property, which is considerably more complex.

by Susan Sales Harkins

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ADDING AN ITEM TO A LIST BOX
To fill a list or combo box, you use the AddItem method. Did you know that you can also add an item to a specific position within the list?
You can if you include the method's index value in the form

List1.AddItem "One", 0

where 0 represents the first position in the list. For instance, the following procedure will display items "Two" and "Three" in a list box
named List1:

Private Sub Form_Load()
List1.AddItem "Two"
List1.AddItem "Three"
End Sub

This second procedure will add the item "One" to the beginning of that list:

Private Sub Form_Click()
List1.AddItem "One", 0
End Sub

(Just remember that the index values begin with 0, not 1.)

by Susan Sales Harkins

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CHART CONFUSION
Working with an Excel ChartObject object can be confusing if you're trying to select or activate the chart. It's hard to know which method to use. When selecting the ChartObject object, use the Select property in the following form, where sheetname identifies the sheet and x is the appropriate index value:

sheetname.ChartObject(x).Select

The Selection property belongs to the PlotArea object, not
ChartObject.

by Susan Sales Harkins

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

THE FASTEST COUNT
You probably know that you can count records in a recordset by using the RecordCount property. However, to do so, you must populate the entire recordset by executing the MoveLast method. If your recordset is large, this is inefficient and unnecessary, because the SQL Count function is faster. For instance, the code

strSQL = "SELECT Count(*) FROM tablename"
Set rst = db.OpenRecordset(strSQL)
Debug.Print rst(0)

is faster than

Set rst = db.OpenRecordset("tablename", dbOpenDynaset)
rst.MoveLast
Debug.Print rst.RecordCount

Granted, in a small database, you may not notice the difference. However, if you have thousands of records, you should definitely notice an improvement.
by Susan Sales Harkins
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EXCEL SPECIFIC INPUT BOX
You're probably familiar with VBA's InputBox statement, as it is available to all Office applications. The Excel library offers its own version of this statement, Application.InputBox. Use the Excel-specific version when inputting formulas and ranges.

This statement takes the form:

Application.InputBox(prompt, [title], [default], [left], [top], [helpfile], [helpcontextID], [type])

Only the first argument, Prompt, is required. The type argument specifies whether the input box expects a formula (0), number (0), range (8), or matrix of cells (64). For example, the following statement would display an input box that accepts only a valid range address:

Application.InputBox ("Please enter the range address",Type:=8)

by Susan Sales Harkins

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ADOBE PHOTOSHOP 5.5: AUTOMATIC IMAGE FITTING
The latest version of PhotoShop makes it simpler (in many cases) to resize images. If you're trying to resize an image to meet certain horizontal or vertical space requirements, PhotoShop will bi-cubically resample the entire image, preserving the correct proportions. To do this, select File, Automate, Fit Image; then enter the measurement you want to stay at or below for either the vertical or the horizontal, and click OK. The missing dimension will be calculated from the scale of the master document and the image resized accordingly.
by Susan Sales Harkins
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EASY SAVE
Lembit Soobik sent in this quick tip for saving changes when the user must press a Save or Cancel button to save the current entries. In such case, the following expression will force a quick save to an edited record:

Me.Dirty = False

Similarly, this next expression will undo any edits:

Me.Undo

Here's how it works: By setting the Dirty property to False, you force Access to save the current row. Just keep in mind that in the process, Access will also trigger Before Update and After Update events.

by Susan Sales Harkins

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

REPLACING CHARACTERS
Office 2000 users can benefit from access to the new VBA function, Replace. This function replaces characters in one string with another string. For instance, to replace the character "b" with the letter "c" in the string "abcd", you'd use the function:

Replace("abcd","b","c")

This will return "accd."
by Susan Sales Harkins
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NONEMPTY NULLS
In the previous tip, we talked about Null values as values that are unknown or don't apply. We used phone numbers to illustrate our point. If the phone number is unknown or if someone doesn't have a phone, you'll leave the phone field blank for that person.

However, if you know the person has no phone, you might want to consider an alternative to Null: When there is no phone (versus no phone number), consider using an empty string ("") or an actual string value, such as "no phone" or "N/A", so you can readily tell when a field is actually missing data.
by Susan Sales Harkins
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DELETING INSTEAD OF REPLACING
In the previous tip, we showed you how to replace characters in a string using VBA's Replace function. You can also delete characters by specifying an empty string "" for the third argument in the form

Replace("string", "delete","")

where "string" is the string or expression from which you're deleting characters, and "delete" is the character you want to delete. For example, the expression

Replace("abcd","b","")

returns the string "acd".

by Susan Sales Harkins
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~