Access Cookbook - Ken Getz [177]
Create a new event procedure for the Change event of the txtGoto text box. (If you're unsure of how to do this, see How Do I Create an Event Procedure? in the Preface of this book.) Add the following code to the event procedure:
Private Sub txtGoto_Change( )
' Enable cmdGoto only when a character
' has been typed into txtGoto
Me.cmdGoto.Enabled = (Not IsNull(Me.txtGoto.Text))
End Sub
Create a new event procedure for the Click event of cmdGoto and add code similar to the following (or copy the cmdGoto_Click event procedure from frmPeopleRSChange; however, that event procedure has additional code that times the operation, which is eliminated here):
Private Sub cmdGoto_Click( )
' Go to new record by changing the
' form's RecordSource property
Dim ctlGoto As TextBox
Dim ctlTime As TextBox
Dim varCriteria As Variant
Dim lngStart As Long
Dim lngEnd As Long
Dim dblTime As Double
On Error GoTo HandleErr
Const acbcQuote = """"
' Start the timer.
lngStart = acb_apiGetTickCount( )
Set ctlGoto = Me.txtGoto
Set ctlTime = Me.txtTime
' Create criteria based on the type of data
' entered into txtGoto.
If IsNumeric(ctlGoto.Value) Then
varCriteria = "ID = " & CLng(ctlGoto.Value)
Else
' A string, so search LastName.
varCriteria = "LastName Like " & acbcQuote & _
ctlGoto.Value & "*" & acbcQuote
End If
' Change the form's recordset based on criteria.
Me.RecordSource = "SELECT * FROM tblPeople WHERE " _
& varCriteria
lngEnd = acb_apiGetTickCount( )
' Now check the form's recordset to see if
' any records were found.
With Me.Recordset
If .EOF And .BOF Then
MsgBox "No matching record found.", _
vbOKOnly + vbCritical, "Goto Procedure"
End If
End With
' Post the time to txtTime.
dblTime = (lngEnd - lngStart) / 1000
ctlTime = "Operation took " & Format(dblTime, "##0.00") _
& " seconds"
ExitHere:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error#" & Err.Number & ": " & Err.Description, _
, "Goto Procedure"
Resume ExitHere
End Select
End Sub
See Recipe 8.7.3 for information on how to customize this code for your particular form.
Save the form and switch to form view to test it.
Discussion
In a multiuser environment, it's always important to limit the amount of data sent across the network to your desktop. By default, however, Access binds forms to all records in the table or query to which your form is bound. This is fine for smaller recordsets of perhaps less than 20,000 records (the exact cutoff figure will vary based on the speed of your PCs, the speed of your network cards and file server, and the average network load), but it can slow things considerably for moderately large recordsets. This solution improves the performance of the form and reduces network traffic by carefully limiting the records in the form's recordset.
By using a SQL statement that initially returns no records as the form's record source, you can quickly open the form in append mode. When the user enters a value in the txtGoto text box and presses the Goto Record button, code attached to the button's Click event changes the form's RecordSource to the correct record.
The event procedure behind the cmdGoto command button begins by setting up an error handler, declaring a few variables, and setting ctlGoto to point to the txtGoto text box control:
On Error GoTo cmdGotoClickErr
Dim ctlGoto As TextBox
Dim varCriteria As Variant
Const acbQuote = """"
Set ctlGoto = Me.txtGoto
Next, the criteria of the SQL Select statement is constructed using this code:
' Create criteria based on the type of data
' entered into txtGoto.
If IsNumeric(ctlGoto.Value) Then
varCriteria = "ID = " & CLng(ctlGoto.Value)
Else
' A string, so search LastName
varCriteria = "LastName Like " & acbQuote & _
ctlGoto.Value & "*" & acbQuote
End If
In the case of the people form, we decided to be flexible and allow users to search on either last