Using a check box to uniquely select a record

[Christopher Asaipillai]

Hi there

I have the following problem. I have a master table called
"Sites" and a detail table called "Options"

In a group of site Records I want to be able to select one and
only one Option record with a check box field. However i cant do
this with enforcing keys.

Heres an example of the data

Site ID Option ID
======= =========
1 6
1 2
1 5

Now out of these, I want to select just either 6 or 2 or 5
But of course the options can increase or decrease
Can i do do this at table or should i do it at form level?

 

[John Vinson]

Hi there

I have the following problem. I have a master table called
"Sites" and a detail table called "Options"

In a group of site Records I want to be able to select one and
only one Option record with a check box field. However i cant do
this with enforcing keys.

Heres an example of the data

Site ID Option ID
======= =========
1 6
1 2
1 5

Now out of these, I want to select just either 6 or 2 or 5
But of course the options can increase or decrease
Can i do do this at table or should i do it at form level?

I don't see any way to do this in a table validation rule; in fact the
requirement to do so violates second normal form, since this checkbox
is not dependent on the table's primary key. This doesn't mean you
shouldn't do it - just that you can't do it that way! You'll need to
do it at the Form level; perhaps in the BeforeUpdate event of the
checkbox use DLookUp to see if there is already another record checked
and cancel the update if there is (with a warning to the user
explaining why). It would also be possible to actually open the other
checked record and uncheck it if you want to have it act like a radio
button (checking one unchecks any others).

 

[Christopher Asaipillai]

I don't see any way to do this in a table validation rule; in fact the
requirement to do so violates second normal form, since this checkbox
is not dependent on the table's primary key. This doesn't mean you
shouldn't do it - just that you can't do it that way! You'll need to
do it at the Form level; perhaps in the BeforeUpdate event of the
checkbox use DLookUp to see if there is already another record checked
and cancel the update if there is (with a warning to the user
explaining why). It would also be possible to actually open the other
checked record and uncheck it if you want to have it act like a radio
button (checking one unchecks any others).

Hi John

Many thanks for that. What i decided to use was the 'Before Update'
event. I use code to open the table , pass in the Site ID and reset
all the Selected options (in reality only one would ever be selected)

That seemed to be a lot neater than using a query with which i was getting a
'Write Conflict' message box if i was trying to edit the same record.