
		       Nod Programming Inc. VB Help Index

This is intended for free use.  The code here is for various skill levels, 
anyone from beginers to advanced programers can use these.  Do what you wish 
with the code it is free for you to use and manipulate!

****************************************************************************

Sure-Fire Way to Allow Users to Cancel Form Unloads:

There are a number of different ways a user can unload a form. Click the
Exit button or menu item; click on the X in the upper-right corner of the
form; select Close from the form window's pop-up menu in the upper-left
corner; even cancel the program from the task manager or reboot the
machine. The best way to give your users the power to cancel a form-unload
activity, whatever its source, is to place all your form-unload checking
code in the QueryUnload event of the form. This event fires no matter which
method is used to unload your form.

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    '
    ' *** universal unload check
    '
    Dim strQuestion As String
    Dim intAnswer As Integer
    Dim aryMode As Variant
    '
    aryMode = Array("vbFormControlMenu", _
      "vbFormCode", "vbAppWindows", _
      "vbAppTaskManager", "vbFormMDIForm")
    '
    strQuestion = "Ready to unload this form?"
    '
    intAnswer = MsgBox(strQuestion, vbQuestion + vbYesNo, _
 aryMode(UnloadMode))
    If intAnswer = vbNo Then
        Cancel = -1
    End If
    '
End Sub

****************************************************************************

Be sure to Close all Data Objects upon Exit:

If you use any data objects in your code (DAO, RDO, or ADO), you should be
sure to explicitly close all open recordsets, databases, and workspaces
before you exit. Even though the pointers to these objects are
automatically destroyed when you exit the program, if you fail to
explicitly close all open items, your database connections may not be
immediately released and the memory used by these objects may never be
re-allocated by the operating system.

Here's a short routine you can add to your Form_Unload event (or some other
terminating code module) that will close all open DAO workspaces,
databases, and recordsets and release the memory reserved by these objects.
This code will work whether you have 1, 100, or even no connections open
when you attempt to exit the form.

Private Sub Form_Unload(Cancel As Integer)
    '
    ' *** close out db objects
    ' *** and release all memory
    '
    On Error Resume Next
    '
    Dim ws As Workspace
    Dim db As Database
    Dim rs As Recordset
    '
    For Each ws In Workspaces
        For Each db In ws.Databases
            For Each rs In db.Recordsets
                rs.Close
                Set rs = Nothing
            Next
            db.Close
            Set db = Nothing
        Next
        ws.Close
        Set ws = Nothing
    Next
    '
End Sub

****************************************************************************

Code After a Call to Form Unload will Prevent the Form from Closing:

If you use control arrays to handle common button or menu activities, you
might be tempted to make one of the buttons call the "Unload Me" event to
end the form execution, as follows:

Private Sub cmdAction_Click(Index As Integer)
    '
    ' *** this doesn't work!
    '
    Select Case Index
        Case 0: Me.WindowState = vbMaximized
        Case 1: Me.WindowState = vbNormal
        Case 2: Unload Me
    End Select
    '
    MsgBox "Current state: " & CStr(Me.WindowState)
    '
End Sub

However, this code won't work. Since there's a line of executing code
after
the "Unload Me" statement, the form will hide, but never unload!

To fix this, you must replace the call to "Unload Me" with a flag
variable
and execute the "Unload Me" as the very last action in the method:

Private Sub cmdAction_Click(Index As Integer)
    '
    ' *** this works!
    '
    Dim blnUnload As Boolean
    '
    Select Case Index
        Case 0: Me.WindowState = vbMaximized
        Case 1: Me.WindowState = vbNormal
        Case 2: blnUnload = True
    End Select
    '
    MsgBox "Current state: " & CStr(Me.WindowState)
    '
    If blnUnload = True Then
        Unload Me
    End If
    '
End Sub

****************************************************************************

Finding the CD-ROM drive's letter:

The following code loops through all your computer's drive letters, to
see
which letter is mapped to the CD-ROM drive. It returns the drive letter
if
successful or an empty string on failure.

Public Function BI_GetCDROMDrive() As String
    Dim lType As Long
    Dim i As Integer
    Dim tmpDrive As String
    Dim found As Boolean

    On Error GoTo ErrorHandler

    'Loop thru A-Z. If found, exit early.
    For i = 0 To 25
        tmpDrive = Chr(65 + i) & ":\"
        lType = GetDriveType(tmpDrive)    'Win32 API function
        If (lType = DRIVE_CDROM) Then    'Win32 API constant
            found = True
            Exit For
        End If
    Next
    If Not found Then
        tmpDrive = ""
    End If

    BI_GetCDROMDrive = tmpDrive

ErrorHandler:
Err.Description = "BI_GetCDROMDrive failed: Unexpected error."
BI_Errorhandler

End Function

****************************************************************************

asily generating random numbers:

If you use random numbers to a large extent, you probably get tired of
always having to put in Randomize and then the equation. This simple
subroutine handles both chores for you. First, put the following routine
in
your project's main module.

Public Function GenRndNumber(Upper As Integer, Lower As Integer) As
Integer
    Randomize
    GenRndNumber = Int((Upper - Lower + 1) * Rnd + Lower)
End Function

To get a random number between 99 and -99, just enter:

      RandomNumber = GenRndNumber(99, -99)

You can get a random letter between "A" and "M" with this:

      RandomLetter = Chr$(GenRndNumber(asc("M"), asc("A")))

You can eliminate the middle of a range by putting the call to
GenRndNumber
inside a DO Loop. The following will get a random number from 50 to 99 or
-50 to -99.

      'Initialize the number to be generated within the area you want
excluded.
      RandomNumber = 0
      Do Until Abs(RandomNumber) > 49
         RandomNumber = GenRndNumber (99, -99)
      Loop

Be sure to declare RandomNumber and RandomLetter as appropriate. This
procedure has a minor benefit for anyone who uses a lot of calls to the
random number generator. It actually generates slightly less machine code
than calls to the RND function. While not generally a concern nowadays,
there may be times when a programmer will need to find a way to cut down
on
the amount of generated machine code.

****************************************************************************

Clearing all text boxes on your form:

By using following code you can clear all the text boxes on your form

Dim vControlFor Each vControl In Me.Controls    'where Me is the form
  If TypeOf vControl Is TextBox Then
    vControl.Text = ""
  End If
Next

****************************************************************************

Limiting the field length for unbound combo boxes:

Sometimes you may wish to limit the number of characters to be entered into
unbound combo boxes. As combo boxes do not have the MaxLength property like
a text box, the limit must be checked programmatically. The combo box
KeyPress event works fine.

Private Sub cmbLimitedText_KeyPress(KeyAscii As Integer)

Const iMAXCHARS As Integer = 25
Const iBACKSPACEKEY As Integer = 8

'Make sure max characters not exceeded
If Len(cmbLimitedText.Text) >= iMAXCHARS Then
   'if not backspace then set to zero
   If KeyAscii <> iBACKSPACEKEY Then
      'Set to zero to cancel keypress
      KeyAscii = 0
   End If

End If

End Sub

This does not affect tabbing/cursor key/delete functions within the combobox.

****************************************************************************

Sorting numbered items in a Listbox:

Consider the following items/files:
FILE1.BMP   FILE2.BMP   FILE3.BMP   FILE10.BMP

If you place them in a sorted List or Combo control, they come up listed
like this:

FILE1.BMP
FILE10.BMP
FILE2.BMP
FILE3.BMP

But what you really want is this:

FILE1.BMP
FILE2.BMP
FILE3.BMP
FILE10.BMP


This is how you do it. After filling your list control, call the ReSort
routine, passing the original offending list control as the only parameter:

Sub ReSort(L As Control)
Dim P%, PP%, C%, Pre$, S$, V&, NewPos%, CheckIt%
Dim TempL$, TempItemData&, S1$

For P = 0 To L.ListCount - 1
  S = L.List(P)
  For C = 1 To Len(S)
    V = Val(Mid$(S, C))
    If V > 0 Then Exit For
  Next
  If V > 0 Then
    If C > 1 Then Pre = Left$(S, C - 1)
      NewPos = -1
      For PP = P + 1 To L.ListCount - 1
        CheckIt = False
        S1 = L.List(PP)
        If Pre <> "" Then
          If InStr(S1, Pre) = 1 Then CheckIt = True
        Else
          If Val(S1) > 0 Then CheckIt = True
        End If
        If CheckIt Then
          If Val(Mid$(S1, C)) < V Then NewPos = PP
        Else
          Exit For
        End If
      Next
    If NewPos > -1 Then
      TempL = L.List(P)
      TempItemData = L.ItemData(P)
      L.RemoveItem (P)
      L.AddItem TempL, NewPos
      L.ItemData(L.NewIndex) = TempItemData
      P = P - 1
    End If
  End If
Next
Exit Sub

****************************************************************************

Enabling/disabling all of the controls in a control array:

As you may know, VB does not allow you to pass a control array as an
argument to a Sub/Function.  So how do you go about writing generic
routines that work on control arrays?  Say for example you wanted to
enable/disable any set of controls on a form - here's one method...

Place this code in a module:

Public Sub EnableControls(Form As Form, ControlArray As Control, State As
Boolean)

Dim ctl As Control 'Control being tested

For Each ctl In Form.Controls
  If ctl.Name = ControlArray.Name Then
    'this control is a member of the control array
    Ctl.Enabled = State
  End If
Next

End Sub

Create a form and add a control array of a few textboxes called txtSample
and an array of check boxes called chkSample.  Add two command buttons
(cmdText & cmdCheck) and label them accordingly.

In the cmdText_Click event add the following code:

Call EnableControls(Me, txtSample(0), Not (txtSample(0).Enabled))

And add this code to the cmdCheck_Click event:

Call EnableControls(Me, chkSample(0), Not (chkSample(0).Enabled))

Run the app and click the buttons to enable/disable the attached group of
controls.  Once I started using this technique, I can't think how I ever
coded without it.

****************************************************************************

    	               End of Help 6 of how many I do!!