Bound Controls: Evil or Not?

I've been quoted for posting that statement in the CodeGuru forums, and now I want to show why. Some things cannot be done using bound controls. An example in this application is a dropdown combo that lets you query any field in the table. The fields are different types!

Many people have asked, "How can I use a database in my VB program" with replies repeating that although SQL terminology might be the same, normally, no two tables are exactly alike. Code samples are rarely helpful because the fields and data types are specific to one table. This is a near-complete application that uses tables developed with Access, being accessed VB6. Here are the tables in Access:

Most textbooks show examples of bound controls, where you select a provider, connection, and table. Using the Data Link Adapter gets your data on-screen, but leaves little room for customization.

When you see how easy it is to do the work yourself, you will agree that 'Bound Controls Are Evil."

The program starts in Sub Main, with no form open.

Sub Main()
' ==================
' jump start
  lUser = "dg"
  lAdmin = True
  lActive = True
  frmMain.Show
  Exit Sub
' ==================
  LoginSucceeded = False
  Do Until LoginSucceeded = True Or lcnt = 4
    frmLogin.Show vbModal
  Loop
  If LoginSucceeded = True Then
'    Debug.Print lAdmin & "   " & lActive
    frmMain.Show
  End If
End Sub

You are supplying the default user and password for testing, but this allows three tries for a password, before it ends the program without showing any forms at all. Only if LoginSucceeded = True, is frmMain then shown.

This program is menu-driven. You click a button to see another form and drill down to get to the details.

Setup has a few options for Users and the Client (operator). Userlist may be printed using code that prints any flexgrid (in FlexPrintModule). Print allows two different printers to be set up independently (Report and Receipt). Also, it allows receipts to be printed automatically.

Collect really shows the simplicity of filling in your own data. You load frmPayment, and you load arrays for fields that will be placed into textboxes. The drop-down combobox also is populated with the client name.

A timer displays the current time on the form while the session is running, collecting payments.

Private Sub Form_Load()
  Dim x As Integer
  Set rec = ClientLoadCombo
  Do While rec.EOF = False
    ReDim Preserve ClientLastName(ct + 1)
    ReDim Preserve ClientFirstName(ct + 1)
    ReDim Preserve ClientID(ct + 1)
    ClientID(ct) = rec.Fields(0)
    ClientLastName(ct) = rec.Fields(1)
    ClientFirstName(ct) = rec.Fields(2)
    cmbName.AddItem rec.Fields(1) & "," & rec.Fields(2)
    ct = ct + 1
    rec.MoveNext
  Loop
  txtDate = Date
  lblTime = Time
  Timer1.Interval = 500
  Timer1.Enabled = True
  Set rec = Nothing
  cmdPay.Enabled = False
  txtAmtPaid.Enabled = False
End Sub

Clicking the dropdown selects a name, which then populates the textboxes. You are using arrays with the same number as the record number, which is also the combobox item number. For more complicated programs, you use Field(x) where x is also the same as the textbox(x) value to iterate thru controls to fill data.

Private Sub cmbName_Click()
  If Val(txtAmtPaid) <> 0 Then Exit Sub
  Set rec = ClientQueryCombo(ClientID(cmbName.ListIndex))
  Do While rec.EOF = False
    RecID = rec.Fields(0)
    txtLastName = rec.Fields(1)
    txtFirstName = rec.Fields(2)
    RecDueAmt = rec.Fields("FixedDebt")
    lblAmountDue = Format(RecDueAmt, "Currency")
    RecPayAmt = rec.Fields("Amount")
    lblPayAmt = Format(RecPayAmt, "Currency")
    If rec.Fields("PaidInFull") = True Then
      chkPaidInFull.Value = vbChecked
    Else
      chkPaidInFull.Value = vbUnchecked
    End If
    rec.MoveNext
    Loop
  Set rec = QueryAmountPaid(RecID)
  Do While rec.EOF = False
    If Not IsNull(rec.Fields("TotalPaid")) Then
      uTotalPaid = rec.Fields("TotalPaid")
    Else
      uTotalPaid = 0
    End If
    lblQueryPaid = Format(uTotalPaid, "Currency")
    lblQueryDiff = Format(rec.Fields("Diff"), "Currency")
    rec.MoveNext
  Loop
  Set rec = Nothing
  txtAmtPaid.Enabled = True
  txtAmtPaid.SetFocus
End Sub

You use an instance of a form to pick the date. It allows you to move it around so that it pops up anywhere.

Function PickDate() As Date
  Load frmCalendar
  frmCalendar.Calendar1.Value = txtDate
  frmCalendar.Left = txtDate.Left + txtDate.Width
  frmCalendar.Top = txtDate.Top
  frmCalendar.Show
  Do While frmCalendar.Visible = True
    DoEvents
  Loop
  PickDate = strDate
End Function

You keep a special table of deleted records. Here is the routine that inserts the record.

Sub InsertIntoDeletedClients()
  Dim vPaid As String, vFixed As Currency, vAmount As Currency
  strSQL = "INSERT INTO DELETEDCLIENTS "
  strSQL = strSQL & "(AccountNo,LastName,FirstName,AccountType,"
  strSQL = strSQL & "AccountDetails,Amount,StartDate,"
  strSQL = strSQL & "EndDate,Length,Frequency,Location, "
  strSQL = strSQL & "FixedDebt,PaidInFull,ClosedOn,ClosedBy) "
  strSQL = strSQL & "VALUES ("
  With frmClients.flx
    ' Location
    strSQL = strSQL & "'" & .TextMatrix(mrow, 10) & "',"
    strSQL = strSQL & "'" & .TextMatrix(mrow, 0) & "',"
    strSQL = strSQL & "'" & .TextMatrix(mrow, 1) & "',"
    strSQL = strSQL & "'" & .TextMatrix(mrow, 2) & "',"
    strSQL = strSQL & "'" & .TextMatrix(mrow, 3) & "',"
    strSQL = strSQL & "'" & .TextMatrix(mrow, 4) & "',"
    strSQL = strSQL & .TextMatrix(mrow, 5) & ","
    strSQL = strSQL & "'" & .TextMatrix(mrow, 6) & "',"
    strSQL = strSQL & "'" & .TextMatrix(mrow, 7) & "',"
    strSQL = strSQL & Val(.TextMatrix(mrow, 8)) & ","
    strSQL = strSQL & "'" & .TextMatrix(mrow, 9) & "',"
    strSQL = strSQL & Val(.TextMatrix(mrow, 11)) & ","
    strSQL = strSQL & "" & .TextMatrix(mrow, 12) & ","
    strSQL = strSQL & "'" & Now & "',"
    strSQL = strSQL & "'" & UCase(lUser) & "') "
  End With
'    Debug.Print strSQL
  cn.Open GetConnString
  cn.Execute strSQL
  Set cn = Nothing
End Sub

You also use the index number to choose fields that have their own unique data types:

Sub QueryClients(idx As Integer, txt As String)
  Dim sqlField As String, strValue As String
  sqlQuery = "SELECT * FROM Client WHERE "
  Select Case idx
  Case -1
   Exit Sub
  Case 0     ' "Account ID"
    sqlQuery = sqlQuery & "AccountNo = " & Val(txt)
  Case 1     ' "Last Name"
    sqlQuery = sqlQuery & "LastName = '" & txt & "'"
  Case 2     ' "First Name"
    sqlQuery = sqlQuery & "FirstName = '" & txt & "'"
  Case 3     ' "Account Type"
    sqlQuery = sqlQuery & "AccountType = '" & txt & "'"
  Case 4     ' "Account Details"
    sqlQuery = sqlQuery & "AccountDetails = '" & txt & "'"
  Case 5     ' "Amount"
    sqlQuery = sqlQuery & "Amount = " & Val(txt)
  Case 6     ' "Start Date"
    sqlQuery = sqlQuery & "StartDate = #" & txt & "#"
  Case 7     ' "End Date"
    sqlQuery = sqlQuery & "EndDate = #" & txt & "#"
  Case 8     ' "Length"
    sqlQuery = sqlQuery & "Length = " & Val(txt)
  Case 9     ' "Frequency"
    sqlQuery = sqlQuery & "Frequency = '" & txt & "'"
  Case 10    ' "Location"
    sqlQuery = sqlQuery & "Location = " & Val(txt)
  Case 11    ' "Fixed Debt"
    sqlQuery = sqlQuery & "FixedDebt = " & Val(txt)
  Case 12    ' "Paid in Full"
    sqlQuery = sqlQuery & "PaidInFull = " & txt & ""
  End Select
End Sub

All the fields are populated into the flexgrid, after the query is run for the Advanced Search of the Client Payment Information in the Setup section.