Creating a record selector class

John Colby

In this article we are going to discuss the record selector class. This is a common control, and the code I use originated in Access’ Help files—though I modified it a bit in the process of making it generic.

When a form loads part or all of a recordset, the user will often need to select a particular record. To add this capability, we use a combo box that pulls the primary key (PK) and another field that contains data the user can recognize. The combo record selector must display the same records that the form does, and it should display them sorted in the same order.

In order to accomplish this, we use RecordsetClone and bookmarks to find and move the form to a record. It is all fairly simple code. The only addition I made to the Help solution allows the combo box to move to the correct record when the form is moved by the user using Page Up and Page Down, and so on.

This class was introduced ever so briefly, without explanation of any kind, in the previous article about the form class. In this article we will show you the code as a further example of building WithEvent classes and having the framework find and instantiate the class automatically.

The record selector class uses two controls— a combo and a text box—to find and move the form to display a specific record. The text box is an aid to easy programming.

The demo uses the title of a movie. Once the user selects the title of the movie in the combo, AfterUpdate uses the form’s RecordsetClone to search for the PK in the form that matches the PK in the combo. The bookmark of form is set to the bookmark of the RecordsetClone, causing the form to display the record. If the user presses Page Up or Page Down or otherwise changes the record displayed in the form, the form’s OnCurrent will set the record selector to match the form.

The code

First, we are using the WithEvents for the combobox declaration. It would be useful to use the OnCurrent of the form here in this class; but if we declare the form as WithEvents, we get a GPF when closing the form. This is a bug in A97 / A2k that supposedly has been fixed in XP. Since we can’t sink the form’s OnCurrent in this class, we will have to have the OnCurrent in the form’s class call a function in this class instead.

Private WithEvents mcbo As Access.ComboBox

Private mfrm As Form

Private mblnFrmEditMode As Boolean

Private mlngBackColor As Long

This is a perfect example of how WithEvents in a class allow us to encapsulate functionality such that other parts of the program don’t even need to know about the workings of the class. Since we cannot sink the form’s events, the form has to know about and intentionally do something to make this class function correctly.

In Init we pass two pointers: one to the form that contains the class, and another to the combo used as the record selector. We are going to change the control’s color when it receives the focus, so we must save the original BackColor setting. And, of course, we hook the three events we will need to make this class work.

'MODIFY THIS TO THE TYPE OF CONTROL BEING MODELED

Public Sub Init(ByRef robjParent As Object, lfrm As Form, ByRef rcbo As Access.ComboBox)

Set mobjParent = robjParent

Set mfrm = lfrm

Set mcbo = rcbo

mlngBackColor = mcbo.BackColor

'ADD EVENTS TO SUIT THE CONTROL BEING MODELED

mcbo.OnGotFocus = "[Event Procedure]"

mcbo.OnLostFocus = "[Event Procedure]"

mcbo.AfterUpdate = "[Event Procedure]"

End Sub

These are the event stubs for the record selector combo. Each combo calls a function. We are going to cut out the error handler stuff to shorten up the code.

'ADD EVENTS TO SUIT THE CONTROL BEING MODELED

'MODIFY THIS TO THE TYPE OF CONTROL BEING MODELED

Private Sub mcbo_GotFocus()

RecSelGotFocus

End Sub

Private Sub mcbo_LostFocus()

RecSelLostFocus

End Sub

Private Sub mcbo_AfterUpdate()

RecSelAfterUpdate

End Sub

The following function saves the BackColor to a local private variable and sets the BackColor to cyan. This implements the BackColor change in GotFocus. It also saves the value of AllowEdits of the form to a local private variable, and then sets AllowEdits True. We do this because if AllowEdits = False, you can’t select anything in the combo, even if the combo is unbound (which it is). And finally, it causes the combo to drop down.

Sub RecSelGotFocus()

On Error GoTo Err_RecSelGotFocus

mlngBackColor = mcbo.BackColor

mcbo.BackColor = 16776960

With mfrm

mblnFrmEditMode = .AllowEdits

.AllowEdits = True

If Screen.PreviousControl.Name <> "dcboRecSel" Then

Screen.ActiveControl.Dropdown

End If

End With

Exit_RecSelGotFocus:

Exit Sub

As the combo loses the focus, we set the BackColor property back to the original color. We also set the form’s AllowEdit mode back to whatever it was when it received the focus.

Sub RecSelLostFocus()

On Error GoTo Err_RecSelLostFocus

Dim ctl As Control

mcbo.BackColor = mlngBackColor

With mfrm

.AllowEdits = mblnFrmEditMode

End With

Exit_RecSelLostFocus:

Exit Sub

The AfterUpdate procedure does all the work of finding the record selected in the combo and moving the form to the record. It builds a WHERE clause for use in finding the record in the form, then causes the form to find the record. Finally it sets the form’s bookmark to the RecordsetClone’s bookmark, causing the form to display that record.

Sub RecSelAfterUpdate()

On Error GoTo Err_RecSelAfterUpdate

Dim strSQL As String

'ctlPrevControl.SetFocus

With mfrm

'BUILD AN SQL STATEMENT

strSQL = !Rec_ID.ControlSource & " = " & !dcboRecSel

' Find the record that matches the control.

.RecordsetClone.FindFirst strSQL

'SET THE FORMS BOOKMARK TO THE RECORDSETCLONES

'BOOKMARK ("FIND" THE RECORD)

.Bookmark = .RecordsetClone.Bookmark

End With

Exit_RecSelAfterUpdate:

Exit Sub

FrmSyncRecSel causes the record selector to stay in sync with the form. It also handles a few other details such as requerying the record selector if the user deleted a record or hiding the record selector if there are no records displayed by the form.

Sub FrmSyncRecSel()

On Error GoTo Err_FrmSyncRecSel

With mfrm

If .RecordsetClone.RecordCount = 0 Then

!dcboRecSel.Visible = False

Else

If Not IsNull(!Rec_ID) Then !dcboRecSel.Visible = True

'If the Record Selector IS NULL or not equal to the Record ID

If IsNull(!dcboRecSel) _

Or .RecordsetClone.RecordCount <> !dcboRecSel.ListCount Then

'REQUERY THE RECORD SELECTOR

!dcboRecSel.Requery

'Set the Record Selector equal to the Record ID

!dcboRecSel = !Rec_ID

Else

If !dcboRecSel <> !Rec_ID Then

'Set the Record Selector equal to the Record ID

!dcboRecSel = !Rec_ID

End If

End With

Exit_FrmSyncRecSel:

Exit Sub

Which of these is better is a judgment call. If the form has no records, the combo will have no records. That will have the user asking why the record selector is blank. Hiding it will have the user asking why sometimes it’s there and sometimes it isn’t. Since the user has to be trained either way, I use the lack of the record selector as a visual cue that the table is empty.

The Not IsNull(!Rec_ID) statement tests that we are not on the new record and that there is a record to display in the combo, and makes the combo visible. If the combo is null or the number of records displayed by the combo is not equal to the number of records in the form, we need to requery the combo and then synchronize the combo to the form. Otherwise, we just check that the combo is not displaying the same record as the form. If not, synchronize the record selector to the form.

The last piece to this puzzle is that the forms’ OnCurrent event must run the function that synchronizes the combo to the form. mFrm_Current() is in dclsFrm. The On Error Resume Next statement allows the code to ignore the error produced when there is no record selector. Otherwise we call the function FrmSyncRecSel for dcboRecSel stored in the form’s mobjChildren collection.

Private Sub mFrm_Current()

FrmEventDbgPrnt mfrm, "Frm_Current"

On Error Resume Next

mobjChildren("dcboRecSel").FrmSyncRecSel

End Sub

Summary

This is a pretty simple class. It depends upon a specific naming convention to work; the combo record selector is always named dcboRecSel and there must be another control on the form, named Rec_ID that is bound to the autonumber PK. The naming convention allows the form’s control scanner to discover that the record selector exists, and instantiate a class to deal with it. The naming convention also allows this class to find the Rec_ID, which is needed in the comparison.

The record selector combo’s OnFocus and LostFocus events deal with the form’s AllowEdits property in case the property is set to False, which would otherwise prevent our using the record selector.

By building a class to deal with this system, we group all of the code required in a single module. If it weren’t for the Access bug that causes a GPF, I would also Dim the form using WithEvents and sink the OnCurrent event for the form directly in this class. That would make the class completely self-contained. Since we can’t dimension the form as WithEvents, we have to place a call to the mobjChildren("dcboRecSel").FrmSyncRecSel in the form class’ OnCurrent event.

This class provides another piece of the framework concept where, if a control is found, a class is automatically loaded to handle it. If the developer wants a record selector, he or she places one on the form and the framework takes care of the rest. No longer does the developer have to make sure the event stubs for the record selector are set up in the form, or that functions in the event stubs call functions in the library.

The only thing the developer has to remember is to have two controls, and to name them according to the convention. You can copy and paste the controls from another form, change the field that Rec_ID is bound to the PK of this form, and change the SQL statement for the record selector combo. Drop these two controls on any form, make these simple changes and the rest is handled by the framework.

Please contact the author at jcolby@myrealbox.com to ask questions or report errors.