Warn of duplicate Primary Key before moving to next record 1

[swertel]

I'm tired of entering a data into a form only to get a warning message about a duplicate entry AFTER I have entered everything and am ready to move onto the next record.

I have used the example code and modified it for my tables

Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)

'*********************************
'Code sample courtesy of srfreeman
'*********************************

    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.strStudentNumber.Value
    stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

    'Check StudentDetails table for duplicate StudentNumber
    If DCount("strStudentNumber", "tblStudentDetails", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Student Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Student Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
End Sub

to check single fields for duplicates before moving on. But, I need to check two fields. The combination of data in the 2 fields cannot be duplicated. Field1 can have duplicates. Field2 can have duplicates. Field1 + Field2 cannot be duplicated. I want a piece of code that checks for duplicates after I enter data into Field2 and pop up a message box stating I have a duplicate. How do I do that?

 

[lameid]

You need criteria that uses and to require more than one value... Something like the following. This assumes the values are numeric. You may need to put delimeters in (the single quotes in your code) as appropriate for the datatypes.

stLinkCriteria = "Field1 = " & Me!txtField2 & " And Field2 = " & Me!txtField2

 

[swertel]

Thanks, but can you help clear up a follow-up question?

I changed my version of the example code
FROM

DocumentNumber = Me.DocumentNumber.Value
NextRevision = Me.NextRevision.Value
stLinkCriteria = "[DocumentNumber]=" & "'" & DocumentNumber & "'"

TO

DocumentNumber = Me.DocumentNumber.Value
NextRevision = Me.NextRevision.Value
stLinkCriteria = "[DocumentNumber]=" & "'" & DocumentNumber & "'" & " AND [NextRevision]=" & "'" & NextRevision & "'"

But doesn't the IF statement also have to change?

If DCount("strStudentNumber", "tblStudentDetails", stLinkCriteria) > 0 Then

The IF statement is only checking for a single field, not two. Perhaps it is the stLinkCriteria option of the DCount function I don't understand thoroughly.

(To clarify, the IF statement in the code box of this post was copied from the example above. My IF statement does use the same field names as the stLinkCriteria code blocks in this post.)

 

[lameid]

You pass criteria text to the Dcount function. The variable has text using criteria for two fields.

Basically the parameter takes a Where clause without the Where keyword... Make a query that returns the things you want to count, switch to SQL view and see if that helps make it make more sense.

 

[swertel]

I don't totally understand it yet, but I get it enough and am happy that it works as expected. Thank you.

 

[PHV]

The IF statement is only checking for a single field
No, it checks the return value of the function.
You may try something like this:

If DCount("*", "yourTable", stLinkCriteria) > 0 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886