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

  • Thanks to wide spread cloud hosting and innovations small businesses can meet and exceed the legacy systems of goliath corporations. Explore the freedom to work how you want, with a phone system that will adapt to your evolving needs and actually save you lots of expense—read Get an Enterprise Phone System without High Cost and Complexity. The article clearly illustrates: The only hardware you'll need is phone equipment for advanced voice and fax. How to join all your employees, mobile devices, …

  • Companies undertaking an IT project need to find the right balance between cost and functionality. It's important to start by determining whether to build a solution from scratch, buy an out-of-the-box solution, or a combination of both. In reality, most projects will require some system tailoring to meet business requirements. Decision-makers must understand how much software development is enough and craft a detailed implementation plan to ensure the project's success. This white paper examines the different …

Most Popular Programming Stories

More for Developers

RSS Feeds

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