Results 1 to 13 of 13
  1. #1
    kawi6rr is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    39

    Compare a value from record set to prevent duplicate recordset in DB

    I’m creating a small database to store some peer review records.



    I created two tables
    • Practitioner (One)
    • Peer Review (Many)


    On my form I have an IDNum field as a mandatory field to prevent duplicate practitioners from being entered. I wrote some VB to check for the IDnum field to match it to any IDNum in the database. If there is a match I prevent the new record from being created and set focus on the IDNum select list.

    My recordset is searching the practitioner table’s IDNum field to see if it matches the value in the IDNum text box.

    I setup a Msgbox to see what’s being matched “MsgBox strIDNumber + Me.IDnum.Value”

    Msgbox results: 3636 2424

    For
    some reason the only value that’s being matched is the first record in the IDNum field which is 3636 and it’s not moving on from there. So if I put in 2424 into the IDNum text box the code thinks that there is no 2424 in the database and creates the new record.


    Code:
    Private Sub IDnum_AfterUpdate()
    ' Displays a message box with the yes and no options.
          NewPeerRecord = MsgBox(prompt:="Are you sure you want to add a new peer review record? 'Yes' or 'No'.", Buttons:=vbYesNo)
    If NewPeerRecord = vbNo Then
        MsgBox "New Peer Record has been canceled!"
        Cancel = True
        Me.cbo_Select.SetFocus
        Me.IDnum.Value = ""
    End If
    If NewPeerRecord = vbYes Then
        Dim dbsPeerReview As DAO.Database
        Dim rstPractitioner As DAO.Recordset
        Dim strIDNumber As String
        Set dbsPeerReview = CurrentDb
        Set rstPractitioner = dbsPeerReview.OpenRecordset("Practitioner")
     
            rstPractitioner.MoveFirst
            strIDNumber = rstPractitioner!IDnum
       
            If strIDNumber = Me.IDnum.Value Then
                MsgBox strIDNumber + Me.IDnum.Value
                MsgBox "There is already a peer review with that IDNumber"
                Cancel = True
                Me.IDnum.Value = ""
                Me.cbo_IDNumber.SetFocus
            End If
       
            rstPractitioner.Close
            dbsPeerReview.Close
       
        Set rstPractitioner = Nothing
        Set dbsPeerReview = Nothing
        
        Me.Last.SetFocus
        
    End If
    End Sub
    I hope that makes sense and many thanks in advance to all replies.
    Last edited by kawi6rr; 05-31-2017 at 02:29 PM. Reason: wrong title, needed to be changed to match post

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You are only looking at the first record. You need a loop with rstPractioner.MoveNext until EOF.

    Or else use DLookup - quicker and easier.

    I presume the ID number has to be an entered field, not an Autonumber?

  3. #3
    kawi6rr is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    39
    I noticed that later and came up with this code. But for some reason the "cancel = true" and "Me.cbo_IDNumber.SetFocus" isn't working.

    Code:
    Dim rs As DAO.Recordset
        Dim strIDnum As String
        Set rs = CurrentDb.OpenRecordset("SELECT IDnum FROM Practitioner")    'Check to see if the recordset actually contains rows
        If Not (rs.EOF And rs.BOF) Then
            rs.MoveFirst 'Unnecessary in this case, but still a good habit
            Do Until rs.EOF = True
                'check for record and assign IDnum to variable
                strIDnum = rs!IDnum
                If strIDnum = Me.IDnum.Value Then
                Cancel = 1
                    MsgBox "There is already a peer review with that IDNumber"
                    Me.cbo_IDNumber.SetFocus
                    Me.IDnum.Value = ""
                    Exit Do
                End If
                'Move to the next record. Don't ever forget to do this.
                rs.MoveNext
            Loop
        Else
            MsgBox "There are no records in the recordset."
        End If

  4. #4
    kawi6rr is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    39
    Could it be because its on the afterupdate event?

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Yes! Do this in the BeforeUpdate even, and the Cancel = True will automatically leave the Focus on IDnum.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    kawi6rr is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    39
    I have it working for the most part and thanks for all your help. I’m getting an error on this line of code “rstPscore.MoveFirst” if no record is found, how do I account for no record being there?

    Code:
    rstPscore.MoveFirst
        Do Until rstPscore.EOF
          If rstPscore.Pract_ID = Me.Pract_ID.Value Then
          'MsgBox rstPscore.Pract_ID
          'MsgBox rstPscore.Peer_Score
            If rstPscore.Peer_Score = "P0" Then
                Me.PScore.Value = "P0"
            End If
            
            If rstPscore.Peer_Score = "P1" Then
                Me.PScore.Value = "P1"
            End If
            
            If rstPscore.Peer_Score = "P2" Then
                Me.PScore.Value = "P2"
          End If
          
        End If
          rstPscore.MoveNext
       Loop
    Thanks

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    right before the movefirst, check for no records:
    Code:
    If rstPscore.recordcount = 0 then 
        msgbox "no records to process"
        exit sub
    endif

  8. #8
    kawi6rr is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    39
    Thanks, I should have known that lol. Do you have any idea why when I open my main form my sub form automatically enters a record into its table?

    Its setup as a one to many relationship.

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    With a main/sub form setup, when you open the main form, it should show all the records in the subform that are related to the record in the main form.
    If there is no related subform record, it should show a blank record, which is OK. Is that what you're seeing?

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Out of curiosity, you have this snippet of code
    Code:
                If strIDnum = Me.IDnum.Value Then
                Cancel = 1   '<<<--- ????????????????
                    MsgBox "There is already a peer review with that IDNumber"
                    Me.cbo_IDNumber.SetFocus
                    Me.IDnum.Value = ""
                    Exit Do
                End If
    Why are you using 1 instead of TRUE??
    I've always seen "Cancel = True".

  11. #11
    kawi6rr is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    39
    Quote Originally Posted by ssanfu View Post
    Out of curiosity, you have this snippet of code
    Code:
                If strIDnum = Me.IDnum.Value Then
                Cancel = 1   '<<<--- ????????????????
                    MsgBox "There is already a peer review with that IDNumber"
                    Me.cbo_IDNumber.SetFocus
                    Me.IDnum.Value = ""
                    Exit Do
                End If
    Why are you using 1 instead of TRUE??
    I've always seen "Cancel = True".
    That has since been changed as I was developing the code.

    For some reason when I open the main form two blank records get added to two of my sub forms but not the other 3 sub forms and I can't figure out where it's coming from. Every time open the main form those 2 empty records get added.

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Single step through the code. Open the dB by holding down the shift key.

    What forms opens first? Is there any Form open event code? Set a breakpoint at the top of the event.
    Open the form, then use the F8 key to step through each line. Somewhere you should find where the records are being added.

    If you cannot find where the records are being added, maybe the next step would be to post your dB..

  13. #13
    kawi6rr is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    39
    Solved: I had an piece of code that entered a record when the form Onload that I didn't need. Thanks for all your help everything works great now.

    Code:
    Code:
    Private Sub IDnum_BeforeUpdate(Cancel As Integer)
    ' Displays a message box with the yes and no options.
    NewPeerRecord = MsgBox("Are you sure you want to create a new record?", vbYesNo + vbQuestion, "New Record?")
    If NewPeerRecord = vbNo Then
        NewPeerRecord = MsgBox("New Peer Record has been canceled!", vbOKOnly + vbInformation, "Record Canceled")
        Cancel = True
        Me.Undo
        
    ElseIf NewPeerRecord = vbYes Then
        Dim rs As DAO.Recordset
        Dim strIDnum As String
        Set rs = CurrentDb.OpenRecordset("SELECT IDnum FROM Practitioner")
        'Check to see if the recordset actually contains rows
        If Not (rs.EOF And rs.BOF) Then
            rs.MoveFirst 'Unnecessary in this case, but still a good habit
            Do Until rs.EOF = True
                If IsNull(strIDnum) Then
                    DoCmd.Save acForm, "Practitioner"
                    Me.Last.SetFocus
                End If
                
                If rs!IDnum = Me.IDnum.Value Then
                    Cancel = True
                    Me.Undo
                End If
                'Move to the next record.
                rs.MoveNext
            Loop
        End If
        rs.Close 'Close the recordset
        Set rs = Nothing 'Clean up
        
        If Cancel Then
            NewPeerRecord = MsgBox("There is already a peer review record with that" & vbNewLine & "IDNumber. You can select that number from the" & vbNewLine & "ID Number dropdown list above.", vbOKOnly + vbExclamation, "Record Exists!")
        End If
    End If
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-16-2016, 12:55 AM
  2. Replies: 4
    Last Post: 10-22-2016, 07:47 PM
  3. Replies: 2
    Last Post: 08-06-2015, 08:13 AM
  4. Replies: 2
    Last Post: 06-08-2015, 02:56 PM
  5. Replies: 16
    Last Post: 11-13-2013, 12:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums