Visual Basic Tips #23


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

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

Proudly presents:
Visual Basic

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


*1. WITH STATEMENTS                   
          
The With statement is an easy way to save yourself some typing. In a 
previous tip, I used the With statement to set values in the 
ColumnHeaders collection of the ListView control. Here's a copy of 
that code: 

With lvwProducts.ColumnHeaders 
   .Add , "ProductName", "Product Name", 2880 
   .Add , "UnitPrice", "Unit Price ($)", 1440, lvwColumnRight 
   .Add , "UnitsInStock", "Units In Stock", 1440, lvwColumnRight 
End With 

Note that you can use With even with a nested object (an object that 
is part of another object). You can also do nested With statements. 
Here's the same code in a different format using two With statements: 

With lvwProducts 
   With .ColumnHeaders 
      .Add , "ProductName", "Product Name", 2880 
      .Add , "UnitPrice", "Unit Price ($)", 1440, lvwColumnRight 
      .Add , "UnitsInStock", "Units In Stock", 1440, lvwColumnRight 
   End With 
End With 

The downside of the second version is that the more you nest, the 
harder it is to read. For this particular code, the first version uses 
less coding and is easier to read.


*2. BUILDING A MOST RECENTLY USED FILE LIST                   
          
One common feature in applications like Word and Excel is the most 
recently used (MRU) list at the bottom of the File menu. This list 
shows the last four (typically) files that had been opened in the 
application. Fortunately, this list is easy to build using the 
standard Visual Basic menu editor and a bit of code.  

In a typical File menu, there is a separator bar between the Exit 
choice at the bottom and those choices preceding it. If you have an 
MRU list, there is another separator before those items. In your File 
menu, add a choice called mnuFileMRU with an index of zero. The 
Caption for this menu item should be a single dash, which will create 
a separator bar. Since we don't want to show the separator if there 
are no files in the list, mark this choice as invisible at startup. 

Once you've done that, you can add this code to your form: 

Private Sub AddToMRUList(strFilename As String) 
   Dim i As Integer 
   
   mnuFileMRU(0).Visible = True 
   If m_intMRU < 4 Then 
      Load mnuFileMRU(m_intMRU + 1) 
      m_intMRU = m_intMRU + 1 
   End If 
   For i = m_intMRU - 1 To 1 Step -1 
      mnuFileMRU(i + 1).Caption = "&" & (i + 1) & " " _ 
         & Mid(mnuFileMRU(i).Caption, InStr(mnuFileMRU(i).Caption, " 
") + 1) 
   Next i 
   With mnuFileMRU(1) 
      .Caption = "&1 " & strFilename 
      .Visible = True 
   End With 
End Sub 

You'll also need to add the following variable declaration to the 
declarations section of the form (the m prefix indicates a 
module-level variable): 

Private m_intMRU As Integer 

Since we know we will either be adding an item or changing an item in 
the MRU list, we show the separator bar, currently named 
mnuFileMRU(0). The code first determines how many items are in the MRU 
list. Since we can't use the UBound function on a control array, we 
keep a separate variable (m_intMRU) with the current number of files. 
If that number is less than four, we use the Load statement to create 
a new menu choice with a new index value, which is automatically added 
after the choice with index zero.  

We then have to shuffle all the names down--that is, #1 becomes #2, #2 
becomes #3, #3 becomes #4, and #4 is dropped if we already have four. 
Each menu item will look like this: 

&1 Filenamegoeshere.txt 

The ampersand causes the 1 to be underlined in the menu choice. When 
we shuffle the choices down, we have to remove the ampersand and 
number before putting on the new number. Once the old choices are 
shuffled down, we store the new one in spot #1.  

If you want to add an extra feature, have the code check to see if the 
file you selected is already in the list. If so, move it to the top 
and shuffle the rest down to fill the empty spot.


*3. SETTING WATCH EXPRESSION SCOPE                   
          
One of Visual Basic's overlooked debugging features is its ability to 
set watch expressions and to give those expressions scope. In some 
cases, you'll want to watch a global variable, which is valid at all 
times. However, if you're looking at a local variable to a subroutine, 
it will be valid only when VB is running that code.  

When you add a watch expression (by selecting Debug, Add Watch), 
you're presented with a number of options for watching that variable: 
You can specify the subroutine in which to watch it, a form/module, or 
throughout the entire application. You can also specify a break when 
the value changes or is True. This is helpful if you're watching a 
flag or other accumulator and want to be able to trace how the value 
changed.


*4. SAVING A RECORDSET TO DISK                   
          
One of the new features with ADO is the ability to save a recordset to 
a disk file. For data that doesn't change frequently (ZIP codes, 
states, and so on), you can write your program to retrieve the data 
from the database on a periodic basis (monthly, weekly, etc.) or based 
on a table's value. Once you retrieve the data, you save it to disk 
using the recordset's Save method. To open it again, you create a 
recordset and give it the filename (instead of a SQL statement) as the 
source of data. This is a nice way to cut some database traffic back 
and forth across the network.


*5. ADO RECORDSETS ARE FORWARD-ONLY BY DEFAULT                   
          
If you're using ADO recordsets in your applications, remember that 
using the Connection.Execute method creates a forward-only recordset. 
This type of recordset can be used only with the MoveNext statement. 
To move back and forth through the recordset, you'll need to create a 
recordset and use its Open method to retrieve the data. When you do 
this, be sure to specify a recordset type other than forward-only, or 
you'll end up with the same problem. Forward-only recordsets are the 
most efficient type for rapidly moving through data (for example, to 
load into another control), but if you need more ability to move 
around, choose another option.


*6. INHERITANCE IN VISUAL BASIC                   
          
A user recently asked about how Visual Basic implements inheritance. 
For starters, inheritance is used when discussing objects and classes. 
To take a simple example, let's say you have three classes: Animal, 
Dog, and Cat. There are some characteristics that are common between 
cats and dogs, and some that aren't. In this case, you would put the 
common characteristics (color, weight, height, etc.) in the Animal 
class and put things that are specific to each animal type in the 
appropriate class. Dog and Cat would inherit characteristics from the 
Animal class and add their own characteristics to that list to create 
a composite list of characteristics. 

In the current version of Visual Basic (6.0), there isn't support for 
this type of inheritance. Languages like C++ and Smalltalk support 
inheritance. However, the next version of Visual Basic is going to 
include this and other object-oriented features. For more information, 
you can visit Microsoft's Visual Basic site: 

http://www.microsoft.com/vbasic/ 

Here, you'll find an article about the next generation of Visual 
Studio that includes inheritance, as well as other cool features.


*7. MANUALLY CREATING A RECORDSET                   
          
One of the features of the ADO recordset is that it can be created 
manually. Instead of creating a recordset by way of a database, you 
can create a recordset, add fields of various types to it, and then 
add data. You can then manipulate it just like a database-created 
recordset, save it to disk, or pass it back from a component for use 
in an application. 

After you create the recordset object, you can use the Add method of 
the Fields collection to create new fields for your recordset. When 
you create these fields, you can specify the data type, the field 
length, and so on. Once you have the fields added, you can use the 
Open method to open the recordset for modification. You then use the 
AddNew method to create a new record, followed by the Update method to 
commit the record to the recordset. Refer to the ADO Fields collection 
for more information about this feature. 

Alan Silver mentioned another use for this feature: He was retrieving 
filenames from disk and needed to sort them. Instead of writing his 
own sort routine, he created an ADO recordset, loaded the filenames 
(along with the other data about them), and used the Sort property to 
automatically sort the data. This requires far less effort than any 
other sort routine I've used in the past. Thanks for the tip, Alan.


*8. HIGHLIGHTING TEXTBOX DATA                   
          
If you have an application that has a search routine, it's nice to 
highlight the text for which the user was looking. Using the SelStart 
and SelLength properties of the TextBox control, this is easy to do. 
You set SelStart to be the position to start highlighting, remembering 
that SelStart uses 0 as the first character in the TextBox. SelLength 
specifies the number of characters to highlight. 

Here's a quick example, using txtData as the TextBox on the form: 

Private Sub mnuSearchFind_Click() 
   Dim lngPos As Long 
  
   m_strSearch = InputBox("Enter the text to find.", "Find Text") 
   lngPos = InStr(1, txtData.Text, m_strSearch, vbTextCompare) 
   If lngPos > 0 Then 
      txtData.SelStart = lngPos - 1 
      txtData.SelLength = Len(m_strSearch) 
   Else 
      MsgBox "Search text was not found.", vbExclamation 
      m_strSearch = "" 
   End If 
End Sub 

m_strSearch is a string defined at the module level so that we can 
perform a "search again". The key code sets the SelStart and SelLength 
properties when InStr returns a non-zero result. InStr works with 1 as 
the first character, so we have to subtract 1 from it to get the 
correct starting location in the TextBox control.


*9. VBG FILES                   
          
A VBG file is a Visual Basic project group file. A project group is a 
way to have more than one project grouped together within the Visual 
Basic environment. This is especially handy when debugging ActiveX 
controls or components. You put the control or component in one 
project, and then add another project where you use the control or 
code. With this method, you can step into the code for your component 
or control just as if it were part of a single project. 

To create a project group, use the Add Project option on the File 
menu. This will let you create or add another project to the 
environment. When you save, you will be prompted to save the VBG file. 
You can open the individual projects by themselves, or you can open 
the group file once you've saved it.


*10. UPDATING A DATE/TIME FIELD IN SQL SERVER                   
          
A user sent a question asking why, when he builds a SQL UPDATE 
statement to update a date/time field, he always gets a date and the 
time he wants to store. The user needed to store only a time value 
(such as 15:43) and not the date. 

The reason this happens is that in SQL Server, as in other database 
systems, a date/time value is not stored as text. Instead, the value 
is stored, for example, as the number of seconds that has passed since 
some date, such as December 31, 1899. If you just want to store the 
time, you have a few options:

 - Store a dummy date with your time, such as January 1, 1900. When 
you retrieve the information, ignore the date and use the time 
information by itself.

- Store the time value as a text string and convert it to a time using 
a built-in VBScript or database function. This is not recommended, 
however, because it is more efficient to sort dates kept as datetime 
types.

There are more options, of course, but these should get you started.
