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.



About the Author

David Glienna

2006, 2007 & 2008 - MVP Visual Basic

Downloads

Comments

  • great

    Posted by falahjomor on 10/31/2011 02:41pm

    so very nice

    Reply
  • frmMain is in the zip file

    Posted by dglienna on 02/23/2011 01:00am

    Dated 9/30

    Reply
  • Good

    Posted by Eliminator2009 on 02/22/2011 11:39pm

    Where is frmmain

    Reply
  • Learned a thing or two

    Posted by viperbyte on 08/08/2010 12:35pm

    I never liked working with bound controls.  I have my reasons and I'm sure they have they're place.  I read the article as I am reading everything I can on this forum that intersts me.

    Reply
  • Printing receipt on thermal printer

    Posted by jbaptist on 01/08/2009 10:36am

    i have a working barcode ticketing system which can be used for ticket printing and has barcode number generated into SQL. but i need to implement a print function for the ticket onto the thermal printer. and also i want to print the revenue report onto the thermal printer. can u help me?

    Reply
  • Thanks fro your support

    Posted by iagina on 07/16/2008 05:03am

    I have been reffering to this document over and over. Thanks so much for your input in Code Guru. You have made my programming level rise by the day. Thanks again

    Reply
  • Data-binding in VB6 and VB.NET

    Posted by jmcilhinney on 12/29/2007 08:54pm

    I've never actually coded in VB6 but, from what I understand, data-binding is quite different in VB6 and VB.NET. This is by virtue of the connected and disconnected models employed by ADO and ADO.NET respectively. If you code in VB.NET then ignore any comments about data-binding being evil as they apply specifically to VB6. In VB.NET you should use data-binding wherever possible. There are some situations where it's not quite up to the task, but it cuts down on code volumne and complexity considerably.

    Reply
  • Thats true

    Posted by JonnyPoet on 12/29/2007 09:15am

    Yea, I also dislike bound Controls

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Confused by all the agile advice? Relax! With the Agile for Dummies eBook by your side you'll learn the fundamentals of agile and how to increase the productivity of your software teams while enabling them to produce higher-quality solutions that better fulfill customer needs much faster.

  • Live Event Date: April 22, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Database professionals — whether developers or DBAs — can often save valuable time by learning to get the most from their new or existing productivity tools. Whether you're responsible for managing database projects, performing database health checks and reporting, analyzing code, or measuring software engineering metrics, it's likely you're not taking advantage of some of the lesser-known features of Toad from Dell. Attend this live …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds