Pete:

You have a foreign key column EquipmentID in tblEquipmentType which
references the primary key of tblEquipment, so once you store an
EqipmentTypeID value in tblMembersEquipment you know what row in tblEquipment
this refers to.  Consequently you don't need to store the EquipmentID in this
table, and moreover should not do as it would constitute redundancy, which is
'a bad thing'.  When entering data into tblMembersEquipment in a form (the
same can apply in datasheet view though most Access developers would not use
a combo box as the display control in datasheet view of a table on the basis
that data should only be entered via forms) you'd have a combo box bound to
the EquipmentTypeID field.  The RowSource property of the combo box would be:

SELECT EquipmentTypeID, EquipmentType, Equipment
FROM tblEquipmentType INNER JOIN tblEquipment
ON tblEquipmentType.EquipmentID = tblEquipment.EquipmentID
ORDER BY Equipment, EquipmentType;

This combo box, cboEquipmentType say, would have the following properties:

ControlSource:    EquipmentTypeID
BoundColumn:    1
ColumnCount:     3
ColumnWidths:    0cm;2.5cm;2.5cm    or rough equivalent in inches.
ListWidth:            5cm    this should be the sum of the ColumnWidths

Experiment with the ColumnWidth dimensions to get the best for, but the
first dimension must be zero to hide the first column.  The ListWidth value
should equal the sum of the ColumnWidth dimensions you use.

The combo box should now list the EquipmentType and Equipment values in
Equipment order with the EquipmentTypes in order within each Equipment group.
When you select an item it will show the EquipmentType.  To show the
Equipment for the selected type put an inbound text box on the form with a
ControlSource property of:

=cboEquipmentType.Column(2)

The Column property is zero-based so Column(2) is the third column i.e.
Equipment.

For data entry of this type you'd normally use a subform based on
tblMembersEquipment embedded in a form based on tblMember (or better a sorted
query based on the table).  The subform and parent form would be linked on
the MemberID columns, so you would not need a control for MemberID in the
subform as it would be given a value automatically by the linking mechanism.  
So, assuming MembersEquipmentID is an autonumber, the only controls on the
form would be for the following fields, the first being the combo box
discussed above.

EquipmentTypeID
DateInstalled
SerialNo

Ken Sheridan
Stafford, England