In the following code "subdb" is a Data control which has been set up on a form, with the DataBaseName set to "C:\Jim\VBlects\VB\subjects.mdb" (which is an MS Access data base) and the RecordSource = "sub"
"sub" is a table in subjects.mdb, which has fields subno, subname, subpoints and subgrade.
Note that the fields of "sub" can be displayed on the form by "binding" various controls (eg text boxes, labels, bound data base controls) and setting DataSource to "subdb" (the name of the Data control) and DataField to (say) "subname"
Stepping Through the Records in a Table
Finding a particular record using an index
Using SQL to select from a table
This code totals the credit points for all the subjects in the table when the label is clicked and places the total in the label.
The With statement saves typing (and makes the code easier to read) by giving a default qualifier for all properties and methods within its scope.
The RecordSet has a collection called "Fields" from which individual fields can be "subscripted".
Private Sub Label1_Click()
Dim pcount As Integer
With subdb.Recordset
.MoveFirst
While Not .EOF
pcount = pcount + .Fields("subpoints").Value
.MoveNext
Wend
End With
message.Caption = pcount
End Sub
The index "subno" has previously been defined in MS Access.
The desired subject number is typed into "reqsub" and when Return is pressed the record with that key is made the "current record" (automatically updating any bound controls).
Note that we have to specify whcih index is being searched, since the table may have several indexes. Note also that the RecordSetType must be "Table" for this code to work.
Private Sub reqsub_KeyPress(KeyAscii As Integer)
If KeyAscii = vbKeyReturn Then
subdb.Recordset.Index = "subno"
subdb.Recordset.Seek "=", reqsub.Text
End If
End Sub
Here we change the RecordSet to which the Data control "subdb" is attached.
We could also attach the result of the selection to an internally defined (not on the form) RecordSet. The RecordSetType must be DynaSet or Snapshot. ÞThe selection does not become active until the Refresh statement is executed.
The second subroutine reverses the selection, re-selecting "all" the records.
Private Sub findbutton_Click()
subdb.RecordSource = _
"select * from sub where " & _
"subpoints = " & CStr(reqcp.Text)
subdb.Refresh
End Sub
Private Sub allbutton_Click()
subdb.RecordSource = "sub"
subdb.Refresh
End Sub
"Slider1" is a Custom Slider control. It shows the value of a number (in this case in the range 0 to 10). Try this code without any error handling. The program stops with a "Type Mismatch" error if non-numerics are entered in the TextBox called inbox.
Worse, the program stops if we attempt to clear the box before entering a new value.
As a first solution, simply add the "On Error Resume Next" statement. This actually works quite well, because the slider is simply left at its old value if a wrong entry is made.
The code given allows the program to continue when the TextBox is cleared, but stops otherwise. If the error is other than the "harmless" one, we deliberately cause the error again (Raise). However, this would not work, as the "Resume Next" condition applies until the procedure is Exited, or until it is turned off. This is done by "On Error GoTo 0". Unfortunately this also sets Err.Number to 0, so we have to save that. Hope there isn't an error in that statement! Error handling can become complex fairly quickly.
Private Sub reqsub_Change()
Dim errnum As Long
On Error Resume Next
Slider1.Value = inbox.Text
errnum = Err.Number
On Error GoTo 0
If errnum <> 0 And inbox.Text <> "" Then
Err.Raise (errnum)
End Sub
This page is maintained by Jim
Underwood who can be reached at
jim@socs.uts.edu.au.
This page was last updated on July 23rd 1999.
http://www-staff.socs.uts.edu.au/~jim/avb/dberr.html 