Visual Basic Tips #14


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

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

Proudly presents:
Visual Basic

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

*1. THE ADO MODEL                 
        
If you're still not familiar with ADO but you're ready to start, you 
might want to start with the ADO Object model. You can find a diagram 
of this model and an explanation of each object at 

http://www.microsoft.com/data/ado/prodinfo/progmod.htm 

After you've reviewed the model and read the documentation, pull down 
the Resources And Feedback menu and select ADO. You'll find several 
resources for ADO available online listed.


*2. COMPARING FLOATING POINT VALUES                 
        
Everyone has struggled with floating point value because you can't use 
the = operator due to the precision of Single and Double variables. 
You can run into the problem anywhere. I recently had a problem with 
Double values in the results of an Access Make Table query. If you run 
into this problem, try using the Round function instead of comparing 
the actual values directly. The Round function takes the form 

Round(value, places) 

where value is the number you're rounding and places is the number of 
decimal places you're rounding the value to. 

When comparing Single and Double variables, just be sure to use the 
same places argument.


*3. AN EFFICIENT WAY TO CLOSE

When you close a form, it's common practice to have the user confirm 
the action. The procedure below is an efficient method that eliminates 
code in your Close command button: 

Private Sub Form_Unload(Cancel As Integer) 
    Cancel = MsgBox("Do you want to quit now?", vbOKCancel) = vbCancel 
End Sub


*4. FINDING AN EMPTY RECORDSET                 
        
You can run a record count of a recordset to see if it's empty, but 
there's an easier way. The following procedure returns True if the 
recordset is empty and False if it isn't. 

Public Function RecordsetEmpty(rst As Recordset) As Boolean 
     RecordsetEmpty = rst.BOF = True And rst.EOF = True 
End Function 

You see, if the current position is both the beginning of the file and 
the end of the file, then there are no records. If either condition 
isn't true, RecordsetEmpty equals False.


*5. WHEN NOTHING ISN'T NOTHING                 
        
Setting an object to Nothing once you're done with it is a good idea 
because it frees up memory. However, if you use the New keyword in 
your declaration statement, as in 

Dim obj As New objecttype 

you can't set that object variable to Nothing later. Well, you can, 
but VBA will immediately create a new instance. 

The solution takes an extra step, but it's worth the trouble in the 
long run. Don't use the New keyword in the declaration statement as 
shown below: 

Dim obj As objecttype 
Set obj = New objecttype 

Using this method to declare and define your object variable will 
allow you to terminate it later.


*6. SHARING FILES BETWEEN VB AND VBA                 
        
Sharing your VB forms and modules with a VBA application and vice 
versa can be an efficient use of your objects and code. Fortunately, 
sharing is easy to do. Basically, all you have to do is import the 
file. Here's what to do if you're in VB. 

First, export the form or module using the VBA application's export 
command. (In the VB Editor, right-click the file in the Project 
Explorer and select Export File from the resulting menu.) Choose 
Project, Add File. Locate the file you want to add in the Add File 
dialog box. Then click Open. 

If you're in a VBA application: 

Open the VB Editor by pressing Alt-F11. Choose File, Import File. 
Locate the file you want to import in the File Import dialog box. Then 
click Open.


*7. GREAT POINTERS                 
        
You've probably noticed that the new versions of MS applications make 
use of a solid triangle as a pointer. You can use these yourself since 
they're included in the Marlett font. Just specify one of the values 3 
through 5 and apply the Marlett font to the value. The following 
identifies the pointer direction with a value: 

3  Left pointer 
4  Right pointer 
5  Up pointer 
6  Down pointer 

Now, here's how to use them to add quality pointers to your controls. 
First, add a label control to your form (object). Then, using the 
table, enter the appropriate value as the label's Caption property. 
Finally, specify Marlett as the label's Font property.


*8. NEAT SQL TRICK                 
        
Several tips ago we talked about displaying the contents of one field 
in a list box while returning the value of a corresponding field. You 
simply bind the control to both fields, and the list box will display 
two columns instead of one. Then, you have the option of hiding one of 
those columns. 

You can take this one step further by concatenating fields and 
displaying those results while hiding the actual fields you combined. 
For instance, most of us enter first and last names in different 
fields. If we want to display a list of names in a list box, we can do 
so by displaying both the first and last name fields, but it doesn't 
look so hot. 

If you're willing to use a SQL statement as the control's DataSource 
property, you can display a list of names in a more familiar 
format--first name, space, and then the last name. Simply use a SQL 
statement in the form 

SELECT EmployeeID, LastName, FirstName, LastName & ', ' & FirstName AS 
Name FROM tblEmployees 

Then, hide the EmployeeID, LastName, and FirstName fields. Once you've 
finished, the list box will display only a list of full names, first 
name first, with a space in-between the two names. 

If you want the names in alphabetical order by last name, that's no 
problem either. Simply add an ORDER BY clause to the statement in the 
from 

SELECT EmployeeID, LastName, FirstName, LastName & ', ' & FirstName AS 
Name FROM tblEmployees ORDER BY LastName, FirstName


*9. LITERAL DATES                 
        
You don't have to use a powerful function to express a date. You can 
use a literal date string instead, and they're generally faster. Save 
the functions for those times when you really need all that power. If 
you just need a date, express it as a literal date--similar to the way 
you express a string. Simply enclose most any established date string 
in pound signs. For instance, all of the following strings represent 
valid dates, and VB will recognize them as dates because of the 
delimiters: 
 - #February 2, 2000# 
- #2/2/00# 
- #Feb 2, 2000# 
- #02/02/2000# 
- #2-Feb-00#


*10. PLEASE OPEN THE DOOR                 
        
Would you like an application to open or close the CD-ROM door? Well, 
you're in luck, because there's an API that will do just that, and 
it's easy to use, unlike many APIs. First, add the following to a 
module's General Declarations section: 

Private Declare Function mciSendString Lib "winmm.dll" Alias 
"mciSendStringA" (ByVal lpstrCommand As String, ByVal 
lpstrReturnString As 
String, ByVal uReturnLength As Long, ByVal hwndCallback As Long) As 
Long 

Next, add the two functions to your module: 

Function OpenDoor() 
mciSendString "Set CDAudio Door Open Wait", 0&, 0&, 0& 
End Function 

Function CloseDoor() 
mciSendString "Set CDAudio Door Closed Wait", 0&, 0&, 0& 
End Function 

To see your door-controlling functions at work, enter the following 
statements (one at a time of course) in the Immediate window: 

?OpenDoor 
?CloseDoor
