How prevent more than one record as the default

strCriteria needs to contain a condition that the primary key is <> to the record you are on. For example, assuming PK is numeric

strCriteria  = "Initials = " & Me.txtInitials & " AND MyPkFieldname <> " & me.MyPk

Select all Open in new window

Are you using 'Initials' to indicate the active one or is there a yes/no checkbox (or something else) also?

Try this:

Private Sub chkbxDefaultSig_BeforeUpdate(Cancel As Integer)

    Dim strCriteria As String
    
    strCriteria = "Initials = " & Me.txtInitials

    Cancel = Not IsNull(DLookup("DefaultSig", "tblSignatures", strCriteria))

    If Cancel = True Then
        MsgBox "Warning: A default signature has already been selected.", vbExclamation + vbOkOnly
        Me.Undo
    End If

End Sub

Select all Open in new window

/gustav

Crystal:  the PK is text.  And the Yes/No checkbox is where the beforeupdate code is going.

Gustav:  I believe my original post contained improper field references.  I have changed it now to be:

    Dim strCriteria As String
    
    strCriteria = "DefaultSig = " & Me.chkbxDefaultSig

    Cancel = Not IsNull(DLookup("DefaultSig", "tblSignatures", strCriteria))

    If Cancel = True Then
        MsgBox "Warning: A default signature has already been selected.", vbExclamation + vbOkOnly
        Me.Undo
    End If

Select all Open in new window

Which works fine if I try to make a record the default via the checkbox and there already is a default record.  But if I try to UNcheck the default record I get the message and it won't let me UNcheck it.

to clarify:
you have a table called tblSignatures
with:
 a Yes/No field called DefaultSig
 a field called Initials to indicate who the signature is for (if more than one person is in here)

What is the PK fieldname?

Worked! with one minor correction:

    Dim strCriteria As String
   
    If Me!chkbxDefaultSig.Value = True Then
        strCriteria = "DefaultSig = " & Me.chkbxDefaultSig
        Cancel = Not IsNull(DLookup("DefaultSig", "tblSignatures", strCriteria))
    End If

    If Cancel = True Then
        MsgBox "Warning: A default signature has already been selected.", vbExclamation + vbOKOnly
        Me.Undo
    End If

Great!
Have a nice weekend.

/gustav

You too!  Thanks again.