Visual Basic Tips #33


----------------------------------------------

TipWorld - http://www.tipworld.com
The Internet's #1 Source for Computer Tips, News, and Gossip

Proudly presents:
Visual Basic

----------------------------------------------


*1. JUST FOR FUN                 
        
If you're considering a new diet or exercise regime, good for you.
If you're not, you might want to check your Body Mass Index and 
then reevaluate. To check your Body Mass Index in VB, you can use
the following procedure: 

Function BMI(pounds As Integer, feet As Integer, inches As 
Integer) As Integer 
BMI = (pounds * 0.45) / (((feet * 12) + inches) * 0.0254) ^ 2 
End Function 

You can attach the procedure to a text box control or run it in 
the Immediate window: 

?BMI(pounds, feet, inches) 

where pounds, feet, and inches represent your information as 
integers. For example, if you're 5'5" and you weigh 130 pounds, 
you'd use the statement 

?BMI(130,5,5) 

which will return a BMI value of 21. If the result is 25 or under,
your BMI is acceptable. If it's over 25, you need to reduce your 
body fat through diet and/or exercise. 

The first part of our expression--pounds * 0.45--converts your 
weight into kilograms. The second component--(((feet * 12) + 
inches) * 0.0254)--converts your height into meters. Your height 
in meters is then squared. In a nutshell, your weight is divided 
by your height squared to return your BMI. 

If you're wondering why our expression doesn't return decimal 
values, stay tuned for our next tip.


*2. QUICKLY ROUNDING DECIMAL VALUES                 
        
In our last tip, we showed you how to return your Body Mass Index.
Although the expression we used should, in most cases, return 
decimal values, our procedure doesn't. 

If you're rounding decimal values to an integer, you don't really 
need special expressions for rounding. Simply assign the value of 
your procedure as an Integer data type. If you recall, the 
procedure used in our last tip was named 

Function BMI(pounds As Integer, feet As Integer, inches As 
Integer) As Integer 

By assigning the entire procedure to an Integer value, we limit 
our function to returning only Integer values. Therefore, any 
rounding is automatic. If the result of the expression is 21.45, 
the function will return 21; if the result is 22.55, the function
will return 23. 

The function doesn't care how many decimal values the expression 
returns. While this isn't a typical programming solution, it is 
a quick solution in the right situation.


*3. MOVING A TOOLBAR                 
        
If you don't like where VB positions a toolbar by default, move 
it. To do so, simply click the move handle (the raised gray line 
at the left or top of a toolbar) and drag the toolbar to the 
desired location. You can use the move handle to slide the 
toolbar to the left or right of a docked position, or to undock 
the toolbar and create a floating toolbar. Once the toolbar is 
floating, you can click the title bar and drag the toolbar to a 
new position. 

If you're unable to move a toolbar, choose Options from the 
Tools menu and click the Docking tab. Simply select the toolbar 
(by name) if you want a particular toolbar to be dockable. Or 
deselect it if you don't want the toolbar to be dockable.


*4. AUTOMATING THE KNOWLEDGE BASE                 
        
You're probably aware of Microsoft's Knowledge Base--a huge 
online database of technical articles. Now, you can receive 
these articles automatically by simply sending an e-mail. 
Here's how it works. First, to receive an index of articles, 
send e-mail to 

Mshelp@microsoft.com 

and enter the word 

Index 

in the Subject line. Once you've received and reviewed your 
index, send another e-mail to 

mshelp@microsoft.com 

with an article's ID number in the Subject line. Microsoft will 
e-mail an electronic copy of the article to you. To request more 
than one article at a time, separate ID numbers with a comma.


*5. DOCKING TOOLBARS THE EASY WAY                  
        
VB toolbars are easy to modify and position. For instance, you 
probably know that there are two positions--docked and floating.
A docked toolbar is attached to the top, bottom, or one of the 
sides of the Access application window. Although you can move a 
docked toolbar, you must undock that toolbar first. A floating 
toolbar resides inside the Container window and moves easily. 

You can alternate between a docked and floating toolbar by 
simply double-clicking the toolbar. If the toolbar is floating,
double-click its title bar to dock it. To undock the toolbar, 
double-click the raised handles at the toolbar's left edge. 

You can turn off the docking feature by choosing Options from 
the Tools menu, clicking the Docking tab, and selecting or 
deselecting the toolbar.


*6. VIEWING THE OBJECT HIERARCHY                 
        
The Object Browser is a great tool for learning about properties 
and objects. But the browser can't provide an overall view of 
your application's object model. The browser lists all the 
objects in the Classes list, but it doesn't display that list 
as a hierarchy. 

Fortunately, you can find a diagram of the application's object 
model in the Help system. First, choose Contents And Index from 
the Help menu. Next, click the Contents tab, open the entry for 
Visual Basic reference, and double-click the Shortcut entry. 
Then, open the Visual Basic Reference entry and select Microsoft
ApplicationName Objects.


*7. THE VALUE OF AN INTRINSIC CONSTANT                 
        
Visual Basic offers several intrinsic constants--predefined 
values that can't be changed. For instance, the Mousepointer 
property has several constants that represent the different 
mousepointer cursors. (We talked about this property in 
September.) 

Although we recommend that you use the constant instead of the 
value in your code, sometimes you need to know the actual value.
To learn a constant's actual value, simply run it in the 
Immediate window. For instance, to learn the value for the 
vbArrowHourglass constant, open the Immediate window and type 
the statement 

?vbArrowHourglass 

and press Enter. In response, VB will return the value 13. You 
can use this technique with almost any constant, as long as 
you've referenced the appropriate library.


*8. DOCKABLE WINDOWS                 
        
We've talked a bit about dockable toolbars, but some windows are 
also dockable. Simply double-click the window's toolbar to dock 
a floating window. Not all windows are dockable, however. A quick 
glance is all you need to discern whether a window is dockable or 
not. If the title bar has only a Close button, the window is 
dockable. If the window is floating, just double-click the 
toolbar and VB will dock the window to the nearest border. 
Windows that have the Minimize and Maximize (or Restore) buttons 
in the title bar aren't dockable. If you double-click the title 
bar, VB will maximize or restore that window.


*9. IF FINDNEXT DOESN'T WORK                 
        
You can use the Recordset object's FindNext method to locate 
records that match criteria. Simply use the form 

rst.FindNext criteria 

where criteria is a SQL WHERE clause without the WHERE. For 
instance, you might use the statement 

rst.FindNext "[LastName] = 'Smith'" 

to find the next record with the string Smith in the 
LastName field. 

However, occasionally these statements don't work and you can 
waste a lot of time trying to figure out what's wrong with the 
criteria component. Most likely, there's nothing wrong with 
your criteria component. Rather, you've not defined the correct 
recordset type. You need to define the recordset as a dynaset 
using the form 

db.OpenRecordset("tablename", dbOpenDynaset) 

If you omit the type argument for your statement, VB opens a 
table-type recordset (dbOpenTable) unless you specify a linked 
table or a query. In those cases, the default is the 
dynaset-type. Before you waste a lot of time trying to restate 
your criteria expression, check the recordset's type.


*10. THE ADO FIND METHOD                 
        
DAO's Find method can evaluate multiple criteria strung 
connected with the And operator, but the ADO Find method can't.
When you need to search on multiple criteria in ADO, use the 
Filter property as shown in the following code: 

rst.Open "tablename", cnn, adOpenKeyset, adLockOptimistic 
rst.Filter = "field1 = criteria1 AND field2 = criteria2" 
MsgBox rst!fieldx 
rst.Close 

DAO also has a Filter property, but it works differently from the
ADO property. The DAO Filter property works only on subsequent 
recordsets; the ADO Filter property will filter the current 
recordset.
