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

[kawi6rr]

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.

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.

 

[The_Doc_Man]

First, your problem may well be that you have a .MoveFirst outside the loop (good) but I don't see a .MoveNext - so you have automatically selected the first record from the table in this code.

Second, to test that a given number exists, you could do a domain function.

x = DCount( "[IDnum]", "Practitioner", "[IDnum]= " & CStr( Me.textbox ) )

where in the above, you use the name of your textbox where that proposed number would be entered. If x comes back as 0, you have not used that number. If x comes back as 1, it is in use. If x comes back as anything else than 1 or 0, you have an inconsistent database and will probably have to hand-massage something.

 

[Gasman]

That is because you Move First and never Move Next until EOF.?
I believe that is also a pretty inefficient way of checking for the ID, and is going to take longer as the data grows.

I think you would be better using DLookup to see if the ID exists if you want to do it that way.
You could create an index on the ID and then make it unique. That would stop duplicates and also make the Dlookup quick?

HTH

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.

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.

 

[Cronk]

I'll add to the comments already made.

For a start, I'd be using a unique index on IDnum.

The code should be in the BeforeUpdate event.

Cancel = true

has no effect after the update. And there is obviously no explicit variable declaration because it would otherwise give a compile error.

I'm mystified as to how you get any result from

MsgBox strIDNumber + Me.IDnum.Value

with different values

Msgbox results: 3636 2424

when the line comes after a test for equality

If strIDNumber = Me.IDnum.Value Then