Multiple records that contain a checkbox.One record should be chec

  • Thread starter Thread starter BZeyger
  • Start date Start date
B

BZeyger

Hello,

I have a form in datasheet view. It shows a number of records which are
associated with projects. It consists of 3 fields. (Active, Name, Amount)
txtActive is a checkbox that determines if an object is active
txtName is the item name
txtAmount is the value in dollars.

The form displays all records. How do I make it so that only one item can be
active?
If the user clicks the txtActive field under record one, the other records
would uncheck. Only one line item should be checked. How would I go about
this?
 
txtActive is a bound control....all of the fields are. Sorry I forgot to
mention that.
It is bound to a table (TableInfo).

Example:

Active Name Amount
Test1 $150
X Test2 $200
Test3 $300

Only one active field at a time. The user has the abilty to modifiy data in
the form view.
 
UNTESTED UNTESTED
You might try an update query to update all txtActive as 0 (zero) in the
BeforeUpdate event of the object used to display txtActive.
 
Hello,

I have a form in datasheet view. It shows a number of records which are
associated with projects. It consists of 3 fields. (Active, Name, Amount)
txtActive is a checkbox that determines if an object is active
txtName is the item name
txtAmount is the value in dollars.

The form displays all records. How do I make it so that only one item can be
active?
If the user clicks the txtActive field under record one, the other records
would uncheck. Only one line item should be checked. How would I go about
this?
Click to expand...

Well, this is a violation of normalization rules in that the Active field
doesn't depend on the primary key of its record, but on the existance of a
value in some *other* record. So you can't manage it with table relationships.

What you could do is use VBA code in the Form's BeforeUpdate event to check to
see if there is already another record with Active checked:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Me.Active = True Then
If Not IsNull(DLookUp("[Name]", "[YourTableName]", "[Active] = True") Then
iAns = MsgBox("There is already an Active item!" & vbCrLf _
& "Click Yes to make this the active one, No to uncheck it,", _
& " Cancel to erase your changes to this record:",
vbYesNoCancel)
Select Case iAns
Case vbNo '
Me.Active = False
Case vbYes
<run an update query to update the other record's Active to False>
Case vbCancel
Cancel = True
Me.Undo
End Select
End If
End Sub
 
Back
Top