• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

How prevent more than one record as the default

I have a form with a checkbox.  There can only be one record in the record source table marked as the default record.  If the user attempts to make a checkbox true for a record when another record is already marked as true I want the user to get a warning letting them know that there is already a default record.

Here is what I have but it is not working:

Private Sub chkbxDefaultSig_BeforeUpdate(Cancel As Integer)

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

    If DCount("DefaultSig", "tblSignatures", strCriteria) > 0 Then
        Me.Undo

        MsgBox "Warning: A default signature has already been selected."
        
        Cancel = True

    End If

End Sub

Open in new window

0
SteveL13
Asked:
SteveL13
  • 3
  • 3
  • 2
1 Solution
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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

Open in new window

Are you using 'Initials' to indicate the active one or is there a yes/no checkbox (or something else) also?
0
 
Gustav BrockCIOCommented:
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

Open in new window

/gustav
0
 
SteveL13Author Commented:
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

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.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Gustav BrockCIOCommented:
Yes, that's right. Try this with a check of the value:
Private Sub chkbxDefaultSig_BeforeUpdate(Cancel As Integer)

    Dim strCriteria As String
    
    If Me!chkbxDefaultSig.Value = True Then
        strCriteria = "Initials = " & Me.txtInitials
        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

End Sub

Open in new window

/gustav
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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?
0
 
SteveL13Author Commented:
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
0
 
Gustav BrockCIOCommented:
Great!
Have a nice weekend.

/gustav
0
 
SteveL13Author Commented:
You too!  Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now