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
  Exit Sub
' ==================
  LoginSucceeded = False
  Do Until LoginSucceeded = True Or lcnt = 4
    frmLogin.Show vbModal
  If LoginSucceeded = True Then
'    Debug.Print lAdmin & "   " & lActive
  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
  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
      chkPaidInFull.Value = vbUnchecked
    End If
  Set rec = QueryAmountPaid(RecID)
  Do While rec.EOF = False
    If Not IsNull(rec.Fields("TotalPaid")) Then
      uTotalPaid = rec.Fields("TotalPaid")
      uTotalPaid = 0
    End If
    lblQueryPaid = Format(uTotalPaid, "Currency")
    lblQueryDiff = Format(rec.Fields("Diff"), "Currency")
  Set rec = Nothing
  txtAmtPaid.Enabled = True
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
  Do While frmCalendar.Visible = True
  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 = 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



  • great

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

    so very nice

  • frmMain is in the zip file

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

    Dated 9/30

  • Good

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

    Where is frmmain

  • 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.

  • 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?

  • 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

  • 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.

  • Thats true

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

    Yea, I also dislike bound Controls

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

Top White Papers and Webcasts

  • Moving from an on-premises environment to Office 365 does not remove the need to plan for disruptions or reduce the business risk requirements for protecting email services. If anything, some risks increase with a move to the cloud. Read how to ease the transition every business faces if considering or already migrating to cloud email. This white paper discusses: Setting expectations when migrating to Office 365 Understanding the implications of relying solely on Exchange Online security Necessary archiving …

  • Enterprises are increasingly looking to platform as a service (PaaS) to lower their costs and speed their time to market for new applications. Developing, deploying, and managing applications in the cloud eliminates the time and expense of managing a physical infrastructure to support them. PaaS offerings must deliver additional long-term benefits, such as a lower total cost of ownership (TCO), rapid scalability, and ease of integration, all while providing robust security and availability. This report …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date