No Duplicates on checkbox field! (1 Viewer)

[Wolf]

I have a continuous form that each record has a checkbox called default.
Problem is that I want only one record to be the default.

So basically I would like when a user presses a checkbox in one record all other records should get unchecked.
Or it should pop up a message box that another record is already the default.

I would use the index no duplicates, but theres a problem because it wont allow blank checkboxes either because other records are already blank.

How would I go about it?

 

[MarkK]

Don't do this with an index in a table, rather,handle the click event of the checkbox on the form and count how many records are already checked (checkout the DCount() function). If one is, issue a "default already exists" error, otherwise just leave it alone. You could handle the before update event too, and cancel the event if a default already exists.
Code example ...

Sub mycheckbox_click()
  If me.mycheckbox then
    If dcount("*", "tYourTable", "IsDefault = True") then
      Msgbox("default already exists.", vbCritical)
      Me.mychekcbox = False
    End if
  End if
End sub

 

[Wolf]

This does not seem to work
My table name is Years checkbox name is Default Year, what am i doing wrong?

Private Sub Default_Year_click()
If Me.Default_Year Then
If DCount("*", "Years", "Default_Year = True") Then
Me.Default_Year = False
End If
End If
End Sub