View Single Post
 
Old 05-31-2017, 02:13 PM
kawi6rr kawi6rr is offline
Newcomer
 
Join Date: Jan 2009
Posts: 9
Default 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:
' 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.
Reply With Quote