Maintaining cross-reference table

[Dorian]

I have a continuous form that maintains a cross-reference table. This
consists of Team and TeamMember both of which are foreign keys to other
tables. There is also a third column ActiveSW. The tricky bit is that the
TeamMember points to a table record that may become inactive (e.g. if the
person leaves the company). My combo box only selects active records in this
table, so anytime a record has become inactive the team member column appears
as blank - and there is no clue as to who the person was. I'd like to still
display these people but with the Active SW (in xref table) set to False.
However, I still want the drop-down to only allow selection of active people.
I'm having a hard time figuring out how to achieve this.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Jeanette Cunningham]

Hi Dorian,

you can put some code in the Before Update event for the combo.

If Me.[ComboName].Column(2) = False then
Cancel = True
MsgBox "Inactive members can't be selected"
End if

The above assumes that ActiveSW is the 3rd column in your query.
Combo columns have numbering that starts with 0 for the 1st column.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Paul Shapiro]

You could also let the default row source for the combo box display all
TeamMembers. In the OnEnter event for the combo, change the row source to
exclude inactive members. In the OnExit event restore the original row
source. That way it generally displays all the data, but when the user goes
to do data entry, the unacceptable choices aren't there anymore.

Hi Dorian,

you can put some code in the Before Update event for the combo.

If Me.[ComboName].Column(2) = False then
Cancel = True
MsgBox "Inactive members can't be selected"
End if

The above assumes that ActiveSW is the 3rd column in your query.
Combo columns have numbering that starts with 0 for the 1st column.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a continuous form that maintains a cross-reference table. This
consists of Team and TeamMember both of which are foreign keys to other
tables. There is also a third column ActiveSW. The tricky bit is that the
TeamMember points to a table record that may become inactive (e.g. if the
person leaves the company). My combo box only selects active records in
this
table, so anytime a record has become inactive the team member column
appears
as blank - and there is no clue as to who the person was. I'd like to
still
display these people but with the Active SW (in xref table) set to False.
However, I still want the drop-down to only allow selection of active
people.
I'm having a hard time figuring out how to achieve this.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and
they
eat for a lifetime".