[vb6] Shuting down/Unload form in Form_Load event

You are doing some validation code in the Form_Load event. If that validation
code fails, then you do not want to show the form. If you call "Unload ME", you will get an error in the form that called ".Show" of the form loading, saying that the form is already unloaded.

  Private mbCancelForm As Boolean

  Private Sub Form_Activate()
    If mbCancelForm = True Then Unload Me
  End Sub

  Private Sub Form_Load()
    mbCancelForm = False

    If {SomeCondition} = True Then
      MsgBox "False condition, unable to load the form", vbExclamation + vbOKOnly
      mbCancelForm = True
    End If
  End Sub

[mssql] Server Side Paging

The script below can be used to fetch the record according to certain page number and size. The syntax is:

  SELECT {field_list} FROM
    (SELECT TOP {page_size} {field_list} FROM
      (SELECT TOP {page_size * page_number} {field_list}
        FROM {table} (NOLOCK)
        WHERE {where_criteria}
        ORDER BY {field_to_order} {order_type}
      ) AS SortedData
      ORDER BY {field_to_order} {reverse_order_type}
    ) AS PagedData
    ORDER BY {field_to_order} {order_type}

Let's say we would like to fetch the data from table customer for page number 10 where the page size is 20, which descending order by username.

  SELECT username, fullname FROM
    (SELECT TOP 20 username, fullname FROM
      (SELECT TOP 200 username, fullname
        FROM customer (NOLOCK)
        WHERE fullname like '%ali%'
        ORDER BY username DESC
      ) AS SortedData
      ORDER BY username ASC
    ) AS PagedData
    ORDER BY main_username DESC

[vb6] Convert formatted string into numeric

The function below is used to convert formatted string that produced by Format() function into numeric value (Double datatype). This function will remove all non-numeric string except minus and decimal delimiter.

Create new module and paste the source below inside your module file (.bas).

  '****************************************************************************
  ' Name        : StrToNumeric
  ' Author      : Chandra Gunawan
  ' Date        : 22-Jan-2009
  ' Description : Convert formatted string into numeric
  '****************************************************************************
  Public Function StrToNumeric( _
         ByVal pString As String, _
         Optional ByVal pDecimalDelimiter As String) As Double
         
    Dim I%, J%, strVal$
    
    If pDecimalDelimiter <> "." And pDecimalDelimiter <> "," Then
      pDecimalDelimiter = Trim(Format(0, "#.#"))
    End If
    
    'Remove all non-numeric except dot and comma
    For I = 1 To Len(pString)
      If InStr(1, "-0123456789" & pDecimalDelimiter, Mid(pString, I, 1)) > 0 Then
        strVal = strVal & Mid(pString, I, 1)
      End If
    Next
    
    'Remove unused delimiter
    J = Len(strVal) - Len(Replace(strVal, pDecimalDelimiter, ""))
    If J > 1 Then
      strVal = Replace(strVal, pDecimalDelimiter, "", 1, J - 1)
    End If
    
    'Replace to proper decimal delimiter
    If strVal = "" Or strVal = "." Then strVal = "0"
    StrToNumeric = CDbl(Replace(strVal, pDecimalDelimiter, "."))
  End Function  'StrToNumeric

[vb6] Numeric input mask for Textbox

Place the 2 Textbox objects in your form with name Textbox1 and Textbox2.
And then paste the code below into your .frm file.

  Private Sub Text1_KeyPress(KeyAscii As Integer)
    Call KeyPressNumeric(KeyAscii)
  End Sub

  Private Sub Text2_KeyPress(KeyAscii As Integer)
    Call KeyPressNumeric(KeyAscii, True, Text2.Text, True, Text2.SelStart)
  End Sub

  Private Sub Text1_GotFocus()
    Text1.Text = Replace(Replace(Text1.Text, ",", ""), ".", "")
  End Sub

  Private Sub Text1_LostFocus()
    Text1.Text = Format(Text1.Text, "###,###,##0")
  End Sub

  Private Sub Text2_GotFocus()
    Text2.Text = Replace(Replace(Text2.Text, ",", ""), ".", "")
  End Sub

  Private Sub Text2_LostFocus()
    Text2.Text = Format(Text2.Text, "###,###,##0.00")
  End Sub

Create new module and place the code below inside your module file (.bas).

  '****************************************************************************
  ' Name        : KeyPressNumeric
  ' Author      : Chandra Gunawan
  ' Date        : 22-Jan-2009
  ' Description : For use with Textbox_KeyPress events
  '               Allows only numeric entries
  ' Note        : If you set pAllowDecimal = True
  '                 then you must set pTextBox_Text = TextBox.Text
  '               If you set pAllowMinus = True
  '                 then you must set pTextbox_SelStart = TextBox.SelStart
  '*****************************************************************************
  Public Sub KeyPressNumeric( _
         pKeyAscii As Integer, _
         Optional pAllowDecimal As Boolean = False, _
         Optional pTextBox_Text As String, _
         Optional pAllowMinus As Boolean = False, _
         Optional pTextbox_SelStart As Long = 0)

    Dim strDecDlm As String

    'Note: 8=backspace, 13=enter, 27=escape
    strDecDlm = Trim(Format(0, "#.#"))
    If Chr(pKeyAscii) = strDecDlm Then
      If Not pAllowDecimal Or InStr(1, pTextBox_Text, strDecDlm) > 0 Then pKeyAscii = 0
    ElseIf Chr(pKeyAscii) = "-" Then
      If Not pAllowMinus Or pTextbox_SelStart <> 0 Then pKeyAscii = 0
    ElseIf InStr(1, "0123456789", Chr(pKeyAscii)) < 1 And _
           Not (pKeyAscii = 8 Or pKeyAscii = 13 Or pKeyAscii = 27) Then
      pKeyAscii = 0
    End If
  End Sub  'KeyPressNumeric

[mssql] How to count records with distinct option?

SELECT
  (SELECT count(*) FROM
    (SELECT DISTINCT City, Gender
      FROM Database1..Person (NOLOCK)
      WHERE Age > 18
     UNION
     SELECT DISTINCT City, Gender
      FROM Database2..Person (NOLOCK)
      WHERE Age > 18) x)