Visual Basic Tips #7


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

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

Proudly presents:
Visual Basic

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


*1. USING ADO'S FIND METHOD                 
        
The Find methods in DAO and ADO aren't the same--although their 
task is still the same. The Find method searches a recordset for 
a record that matches specific criteria. In doing so, the DAO 
Find method allows you to combine multiple conditions using the 
And operator. However, the ADO method doesn't. The ADO method 
uses the form 

rst.Find fieldname operand value 

where fieldname is the name of the field you're searching; 
operand is a logical operator, such as =; and value is the data 
you're trying to match. A simple ADO Find task might resemble 
the following: 

rst.Find "LastName = Smith"


*2. CONTROL ARRAY BASICS                 
        
If you have multiple controls that perform similar tasks, you 
might want to create a control array. Doing so can simplify 
your code. That's because VB treats the controls in the array 
as a group--each control has a unique index number. So, you 
can write one procedure to handle all the controls. If you're 
interested in learning more about control arrays, here are the 
basics you need to get started. 

1. Add two or more controls of the same type and give them all 
the same name. When VB warns you that a control with that name 
already exists, continue. As a result, VB will assign an index 
number to each control as you add each to the array. 
2. To refer to an individual control within the array, 
use the form 

ArrayName(0) 

where 0 is the index number of a specific button--starting with 
the value 0 for the first control in the array. 
3. When an event occurs on any control in the array, VB passes 
the index value of that control as a argument. You then 
determine, within your code, how to proceed. 

Let's consider a simple example--a set of navigation buttons. 

Private Sub cmdNav_Click(Index As Integer) 
Select Case Index 
  Case 0 
      ... go here... 
  Case 1 
      ... go there... 
  Case 2 
      ... go back... 
End Select 
End Sub 

So, instead of needing three procedures, you need just one.


*3. SO MUCH FOR COMPATIBILITY                 
        
VB is specifically designed to work with SQL Server and Oracle. 
However, as you probably know, lots of VB applications access 
Jet databases (Access and FoxPro). Theoretically, you should be 
able to switch from a SQL Server database to another database 
and experience no problems, as long as you change the OLE DB 
provider. Unfortunately, the Jet database has problems with the 
Jet OLE DB provider. 

This means you may run into bugs when connected to a Jet 
database. If you encounter problems, you should consider 
creating a new ODBC data source--one that uses the OLE DB 
provider for ODBC drivers and the Access ODBC driver. 

To create an ODBC data source, open Windows' Control Panel and 
double-click ODBC (32-bit). With the DSN tab selected, click 
Add. Next, select the appropriate Jet database driver and click 
Finish. Enter a name and description for the data source. Next, 
click Select and find the database you're connecting to. Doing 
so should display the path to your database. At this point, 
click OK, and Windows will display the Data Source Administrator 
dialog box with the new data source. Click OK.


*4. PARSING WITH FORMAT()                 
        
Parsing is one of those tasks that most developers hate--mostly 
because you can't do it with a generic round of code. Invariably,
you end up writing unique code for every new parsing task. One 
thing you might consider is using Format() instead of string 
functions. Doing so isn't always appropriate, but when it is, it 
can be simpler to write and apply. For instance, let's suppose 
you have a nine-character string and you need to insert a space 
character (or any other character for that matter) after every 
third character. To do so, you might consider a typical parsing 
expression such as 

results = Left$(9digitstring, 3) & " " & Mid$(9digitstring, 4, 3)
 & " " 

But the Format() function is more efficient and easier to read: 

results = Format$(9digitstring, "!@@@ @@@ @@@") 

This alternative to parsing isn't always available; you can't 
always avoid string functions. But, when Format() can do the 
trick, you might as well use it.


*5. TO COMMENT OR NOT TO COMMENT                 
        
Comments are an important part of your code, and we've discussed 
whether to comment or not to comment in previous tips. What we 
haven't discussed, though, is what your comments should say. So, 
we took a short poll of a few developers and here's the gist of 
their responses:  - Comments should convey the purpose of the 
code, not how it works. You can decipher the code and figure out 
how it works. However, you might not know why it exists--that's 
why you use comments. For instance, you don't need comments to 
break down an expression by its mathematical importance. Instead,
the comment needs to tell you that the following code figures 
each customer's discount, or each salesperson's commission, or a 
student's final grade. Comments shouldn't extend off the edge of 
the screen. Instead of one long line, enter several short lines, 
all of which are visible onscreen without scrolling. 

- Comments can and probably should include notes about problems 
that you encountered while testing and the solutions you tried. 
These notes may keep you from trying the same thing (and failing
again) a year or so down the road.


*6. ARE YOUR FORMS UNLOADED?                 
        
You probably unload forms when you're done with them to save on 
resources. In fact, in March, we showed you a simple procedure 
that will do just that: 

Public Function UnloadForms() 
Dim frm As Form 
For Each frm In Forms 
  Unload frm 
  Set frm = Nothing 
Next frm 
End Function 

James Crowley, host of vbonline at 

http://www.ccrowley.force9.co.uk/vbonline/ 

has shared a few more form-hunting tips. First, if you need to 
check for loaded files during the testing and debugging stage, 
open the Immediate window (while the current form is paused) 
and enter the statement 

? Forms.Count 

This statement will return the number of loaded forms. If you 
want to identify those forms, use the statement 

? Forms(0).Name 

which will return the name of the first form in the collection. 
If there's more than one form, replace the index value with the 
value 1 and rerun the statement. Continue in this manner until 
you've identified all the loaded forms.


*7. UNREGISTERING A DLL                 
        
In May, we talked about Regsvr32.exe--a utility for registering 
DLLs. Unfortunately, sometimes the utility can't register the 
DLL because an existing version of the DLL is still registered. 
When you need to determine if a DLL is already registered and 
where, you can use Regedit.exe's Find feature. It's probably 
the most efficient method. 

To find a registered DLL, choose Run from the 
Start menu and enter 

regedit 

Once Windows launches this utility, choose Find from the Edit 
menu and enter the name of the DLL you're trying to find. Then, 
click Find Next. If the DLL is registered, the Find feature will
take you directly to the appropriate key.


*8. PASSWORD'S NO PROBLEM                 
        
If you choose to use a data control to connect to an Access 
database, you won't be locked out by passwords. You can still 
access the .mdb file programmatically, assuming, of course, that 
you know the password. The following code will get you in: 

Data1.DatabaseName = completepathtodatabase 
Data1.RecordSource= "TableName" 
Data1.Connect = ";Pwd=password" 
Data1.Refresh 

This allows you to maintain security for your records but still 
have access through VB.


*9. AVOID THE MASKED EDIT CONTROL                 
        
Some developers find that the Masked Edit control needs a little
more care and attention than they are willing to provide. In 
short, this control can be difficult to work with. Often, we 
find that we can substitute a Masked Edit control with an 
ordinary text box and a Format statement. Of course, as is the 
case with most shortcuts, you may find times when you can't 
avoid the Masked Edit control. But, if you can, why work harder 
than you need to?


*10. THE SIMPLEST TIMER                 
        
There are many ways to pause your application. One of the 
simplest is to call the Sleep API. To do so, make the 
following declaration: 

Private Declare Sub Sleep Lib "kernel32" (ByVal 
dwMilliseconds As Long) 

Next, pass the amount of time (in milliseconds) to a sub 
function--this is a generic example: 

Function TestSleep(timelength As Long) 
Sleep (timelength) 
MsgBox "I'm done" 
End Function 

You can test this by opening the Immediate window and typing 

TestSleep(x) 

where x represents the amount of time (in milliseconds) you want 
to pause. Try 100 and you'll hardly notice a pause. Then, try 
1000, and you'll see a short pause. Try 5000 to pause for five 
seconds
