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.
