Visual Basic Tips #12


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

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

Proudly presents:
Visual Basic

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

*1. MISSING SPACES                 
        
We often concatenate variables when working with SQL statements. One 
of the easiest mistakes to make when working with variables and SQL is 
to omit a necessary space between the variable and the SQL text. It 
can also be very difficult to find because you're not really thinking 
about spaces--you're more likely to be concentrating on logic and 
syntax errors. For example, the simple statement 

"WHERE fieldname=" & variable 

works fine because SQL anticipates the spaces around the equal sign. 
However, the statement 

"SELECT * INTO" & variable & "FROM tablename WHERE tablename.fieldname 
=" & strCriteria & ";" 

won't work because of missing spaces. Specifically, the statement 
needs a space in the following places: 
 - after the INTO clause and before the variable 
- after the variable and before the FROM clause 

 
The correct syntax is 

"SELECT * INTO " & variable & " FROM tablename WHERE 
tablename.fieldname =" & strCriteria & ";" 

If you've been writing SQL statements for a long time, you may have 
learned the hard way to check for these spaces first.


*2. RESETTING THE TAB INDEX PROPERTY                 
        
A control's TabIndex property determines that control's position in 
the tab order sequence. For instance, the control with a TabIndex of 0 
is the first control to receive focus. When you next press Tab, your 
form will select the control with the TabIndex of 1, and so on. 
Initially, this value is relative to the order in which you add 
controls to your form. This means the first control you create has a 
TabIndex value of 0; the next control will receive a value of 1; and 
so on. 

However, it's common to move controls around during the design stage, 
so you'll probably need to update the TabIndex property for a few, if 
not all, of your controls once you've completed the form. A quick and 
easy way to reset the tab sequence is to select the control that 
should be last in the order and set its TabIndex property to 0. Then, 
select the next-to-the-last control in the sequence and repeat this 
process. Doing so will force the last control in the sequence (and the 
first control you set) to update its property to 1. Next, select the 
next-to-the-next-to-the-last control in the sequence and set its 
property to 0. When you do, the next-to-the-last control will reset 
itself to 1, forcing the last control to reset itself to 2. Continue 
in this manner until you reach the control that you want to be first 
in the order. At this point, all of the controls should be in order.


*3. SEEING ALL THE TEXT                 
        
In an earlier tip, we talked about setting a TextBox control's 
MultiLine property to True if you want the control to hold/display 
more than a single line of text. When you set this control's MultiLine 
property to True, you should also consider setting the ScrollBars 
property. Specifically, you should set the ScrollBars property to the 
setting 2--Vertical. That way, you can scroll through all the lines of 
text if the size of the control doesn't accommodate all the text. 
Otherwise, you may see only part of the data.


*4. ANOTHER CONSTANT TIP                 
        
A fairly common sight when working with strings is the expression 

Chr(13) & Chr(10) 

This combination concatenates a carriage return and a line feed. 
However, there's an intrinsic constant you should use instead--vbCrLf. 

We recommend you use constants whenever possible. They're more 
readable and easier to remember (most of the time) than the value they 
represent.


*5. RESETTING ARRAY ELEMENTS                 
        
Generally, the first element in an array is 0. You can force the first 
element to be 1 by using the Option Base statement. Specifically, 
enter the statement 

Option Base 1 

in the General section of your module. As a result, the elements in 
your array will begin with the value 1 instead of 0. It's that simple!


*6. MORE ON RESETTING ARRAY ELEMENTS                 
        
In our previous tip, we showed you a quick way to force an array's 
elements to begin with the value 1. You enter the statement 

Option Base 1 

in the module's General section. You can accomplish the same thing 
when you declare your array. For instance, the statement 

Dim iMyArray(3) As Integer 

declares an integer array with three elements. By default, the value 0 
will represent the first element, 1 will represent the second, and 2 
will represent the third. If you want to force the first value to be 
something other than 0, simply say so in the declaration statement in 
the form 

Dim iMyArray(firstelement To lastelement) As Integer 

For example, if you wanted to start with the value 1, you'd use the 
statement 

Dim iMyArray(1 To 3) As Integer


*7. REPLACE WITH NOTHING                 
        
Most of you probably know that you can use the Replace() function to 
replace one string with another. To do so, you use the function in the 
form 

Replace(string,findstring,replacementstring) 

where string is the string you're searching, findstring is the 
character or substring you want to replace, and replacementstring is 
the string you mean to substitute for findstring. 

What you might not realize is that you can use Replace() to delete a 
character or substring by specifying a zero-length string as 
replacementstring. For example, the following statement 

Replace("Now you see it now you don't", "it", "") 

returns the string "Now you see now you don't." We removed the pronoun 
it. 

Be careful with that zero-length string, though. The zero-length 
string, "", doesn't equal the string " " (where there's a space 
between the two apostrophe characters). These two string characters 
aren't interchangeable.


*8. ADDING LINE NUMBERS TO CODE                 
        
You probably know you can comment your code using the apostrophe 
character or the REM statement. Did you know that you could number 
your lines of code? You can, and it's easy. Just add the number to the 
very beginning of each line of code. For instance, the following 
procedure 

Private Sub Form_Load() 
With cmdButton 
   .Visible = True 
   .Width = 3000 
   .Caption = "I'm the first." 
End With 
End Sub 

becomes 

Private Sub Form_Load() 
01 With cmdButton 
02    .Visible = True 
03    .Width = 3000 
04    .Caption = "I'm the first." 
05 End With 
End Sub 

When numbering lines of code, make sure each number is at the 
beginning of the line. In addition, don't number the procedure's name 
or ending statement.


*9. MEMOS SLOW THINGS DOWN                 
        
When a data source contains a Memo field, you might want to consider 
where you display that field. You see, Memo fields tend to slow down 
your form's performance. I'm not suggesting that you not display them 
at all, but I am suggesting that you not display them on your main 
data form unless you absolutely must. Often, you don't need to see 
each record's memo data anyway. When this is the case, display the 
Memo field on another tab or a pop-up form so the user still has quick 
and easy access to that data.


*10. RANDOM VALUES                 
        
Ever need a random value? It's a simple matter with the Randomize and 
Rnd functions. The following procedure will return a random value that 
falls between the two arguments, upper and lower. 

Function RandomNumber(upper As Long, lower As Long) As Long 
Randomize 
RandomNumber = Int((upper - lower + 1) * Rnd + lower) 
End Function
