You'll have table Members say, with one line per member. This should have a
unique numeric column, MemberID say, as its primary key, as names can be
duplicated, as well as columns such as FirstName, LastName etc. Create a
form, in single form view, bound to this table, or better still to a query
which sorts the members by LastName so they are presented alphabetically on
the form. You can use the form wizard to create the form or design it
yourself.
To find a member add an unbound combo box to the form header set up as
follows:
RowSource: SELECT [MemberID], [FirstName] & " " & [LastName] FROM
[Members] ORDER BY [LastName], [FirstName];
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
In the AfterUpdate event procedure of the combo box put the following code:
Dim rst As Object
Dim ctrl As Control
Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl
With rst
.FindFirst "[MemberID] = " & ctrl
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
By selecting a member in the combo box the form will move to that record.
When it comes to family membership it depends how you've stored that data as
there are several ways you might have approached this:
1. If you've used a separate table, FamilyMembers say, with a foreign key
column MemberID referencing the primary key of members and other columns such
as FirstName, LastName, DoB, Relationship etc. (the last for husband, wife,
son, daughter etc) then you can create a continuous view subform based on
this table and embed it as a subform in the main members form, linking the
parent form and subform on MemberID by setting this as the subform control's
LinkMasterFields and LinkChildFields properties.
2. An alternative model you might have used is to include everybody in the
one Members table and to relate this to itself by a second FamilyMembership
table with columns MemberID and FamilyMemberID and Relationship (this time
the latter will include a value Self as well as husband, wife etc). With
this model the main form would be based on a query which joins the two tables:
SELECT [Members].*
FROM [Members] INNER JOIN [FamilyMembership]
ON [Members].[MemberID] = [FamilyMembership].[MemberID]
WHERE [Relationship] = "Self"
ORDER BY [LastName], [Firstname];
and the subform on a query such as:
SELECT [FamilyMembership].[MemberID], [FirstName], [LastName], [DoB]
FROM [Members] INNER JOIN [FamilyMembership]
ON [Members].[MemberID] = [FamilyMembership].[FamilyMemberID]
WHERE [Relationship] <> "Self"
ORDER BY [LastName], [Firstname];
Note that in this query you need to return the MemberID from the
FamilyMembership table and the names etc from the Members table. You only
need to qualify the meberID with the table name as this is the only column
which exists in both tables.
The parent and subforms would be linked in the same way as with 1. Note
that even if a member has no family they must still have a row in
FamilyMembership joining the member to themself by having the same value in
the MemberID and FamilyMemberID columns and 'Self' in the Relationship column.
Ken Sheridan
Stafford, England
