Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   .NET Database and Reporting (http://www.xtremevbtalk.com/-net-database-and-reporting/)
-   -   Compare a value from record set to prevent duplicate recordset in DB (http://www.xtremevbtalk.com/-net-database-and-reporting/328646-compare-value-record-set-prevent-duplicate-recordset-db.html)

kawi6rr 05-31-2017 01:13 PM

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.

Kluz 05-31-2017 08:04 PM

When you fill a recordset object it defaults to the first record. You can fill the object from a SQL statement limiting it to only the one record where Me.IDnum.Value occurs or an empty record set if it can't be found. The rstPractitioner object has a "RecordCount" property: test for > 0. It also has a filter property if you need to test for several values one at a time and there are the EOF and BOF properties will will both be true for an empty recordset.


All times are GMT -6. The time now is 11:57 PM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.