Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
Go Back  Xtreme Visual Basic Talk > > > Compare a value from record set to prevent duplicate recordset in DB


Reply
 
Thread Tools Display Modes
  #1  
Old 05-31-2017, 01: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
  #2  
Old 05-31-2017, 08:04 PM
Kluz's Avatar
KluzCompare a value from record set to prevent duplicate recordset in DB Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,444
Default

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.
__________________
No the other right mouse click
Reply With Quote
Reply

Tags
idnum, record, set, peer, msgbox, stridnumber, field, me.idnum.value, review, prevent, box, dbspeerreview, dim, newpeerrecord, database, rstpractitioner, cancel, true, matched, text, match, practitioner, recordset, created, duplicate


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
 
Compare a value from record set to prevent duplicate recordset in DB
Compare a value from record set to prevent duplicate recordset in DB
 
-->