Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I really enjoy your site. You have a lot of helpful and friendly experts who contribute so willingly. Thank you for past (and future) technical advice..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Forms Forum

Warn of duplicate Primary Key before moving to next recordHelpful Member! 

thread702-1680223
Read More Threads Like This One
swertel (TechnicalUser)
11 Apr 12 11:19
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

CODE

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?
Helpful Member!  lameid (Programmer)
11 Apr 12 16:33
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.

CODE

stLinkCriteria = "Field1 = " & Me!txtField2 & " And Field2 = " & Me!txtField2
swertel (TechnicalUser)
11 Apr 12 17:39
Thanks, but can you help clear up a follow-up question?

I changed my version of the example code
FROM

CODE

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

CODE

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

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

CODE

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 (Programmer)
11 Apr 12 18:05
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 (TechnicalUser)
11 Apr 12 18:14
I don't totally understand it yet, but I get it enough and am happy that it works as expected.  Thank you.
PHV (MIS)
11 Apr 12 18:38
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:

CODE

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

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Forms

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close