change record to "Yes"

[ed]

I have a supplier table to enter material number,
potential supplier IDs and their status (Supplier_Selected
field (selected or not)). In the subform there
is "Supplier_Selected?" Combobox with Yes/No options. For
same Material Number I can have more than one records (I
have auto ID as primary key for the table). The only thing
I want is to have only one "Yes" for the same material.
Suppose I have 5 records for the material # 115. I should
have only 1 "Yes" and 4 "No" for the Supplier_Selected
field. If the user tries to change one of those records
as "Yes", a msgbox should show up to warn him/her
saying "you can select only one supplier for one material".
How can I do that?
Thanks a lot

 

[Ken Snell]

You might be able to use the BeforeUpdate event of the checkbox to see how
many checkboxes are set to Yes and then to act accordingly. You can use the
DCount function to do the check:

Private Sub chkBoxName_BeforeUpdate(Cancel As Integer)
If DCount("*", "SupplierTableName", "[Supplier_Selected] = True") > 0
Then
Msgbox "You can select only one supplier for one material."
Cancel = True
End If
End Sub

Change SupplierTableName to the actual name of the table that contains the
records.

 

[Guest]

Thanks a lot. It worked.