Main form with subform refinement

[rpbsr]

My subject is rather vague, so I'll try to clarify. I have a form based on
tblMembers and subform based on tblCourseRegistration. LastName, FirstName
and related data is pulled into the main form and course information, e.g.,
course, date, tuition, etc. is in the subform. It works nicely in that it
shows the name and corresponding course information with each click of the
navigation button. I would like to use this to update current members (add
new courses to those taken). Assuming there will be a lot of members, using
the navigation button is impractical. Is there a way for this form to query
all members with LastName starting with a certain letter, allow you to select
a name, and display that person's information in the main & subform?

Any help is appreciated.

 

[Ken Sheridan]

I'd suggest adding an unbound combo box, cboFindMember labelled 'Go to
member' say, to the main members form, e.g. in its header or footer, so that
users can select a name form an ordered list. Set up the combo box with its
properties like so:

RowSource: SELECT MemberID, LastName, FirstName FROM tblMembers ORDER BY
LastName, FirstName;

BoundColum: 1
ColumnCount: 3
ColumnWidths: 0cm;2.5cm;2.5cm
ListWidth: 5cm
LimitToList: Yes
AutoExpand: Yes

If your units of measurement are imperial rather than metric Access will
automatically convert them to inches. The important thing is that the first
dimension is zero to hide the first column. The ListWidth should be the sum
of the ColumnWidths dimensions. Experiment with these dimensions and the
width of the combo box to get the best fit.

In the combo box's AfterUpdate event procedure put the following code:

Dim rst As Object

Set rst = Me.Recordset.Clone

With rst
.FindFirst "MemberID = " & cboFindMember
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

To do this select the control in form design view and open its properties
sheet if its not already open. The select the After Update event property in
the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the lines of code between these two existing lines.

I've assumed that your tblMembers table does have a unique MemberID primary
key column. If not you can simply add an autonumber column to the table.
Don't use names as keys, they can be duplicated.

To find a member you can either drop down the combo box's list and scroll
down, or you can begin to type a last name. As you type characters the combo
box will progressively go to the first match. Once you make a selection the
form should move to the selected member. The code in the control's
AfterUpdate event procedure does this by finding the row in a clone of the
form's underlying recordset and then synchronizing the form's Bookmark with
the recordset's Bookmark.

One added refinement is to put the following code in the form's Current
event procedure:

Me.cboFindMember = Me.MemberID

This will keep the combo box in sync with the form's current record if you
navigate by other means such as the navigation buttons or keyboard.

Ken Sheridan
Stafford, England