Preventing duplicate values in a sub form.

Posted on 2007-07-26
Last Modified: 2013-11-28
Can somebody give me some code  that will prevent my users from entering a duplicate value into an embedded subform.

My main form is called, frmMainForm and my embedded subfrom is called, frmSubForm.  

The subform is formatted as a datasheet and contains a field called cboYear.

I want to prevent my users from entering in the same year twice in the Year field on the subform.

Thought about setting an index on the underlying subform table but since it's a related table, it can potentially have the same value entered twice in the table .........I just don't want it to appear twice when viewing it as a subform.

Thanks All!!
Question by:mbath20110
    LVL 65

    Expert Comment

    Perhaps a beforeupdate function

    its been a while since I last coded vba so bear with me

    private sub cboYear_BeforeUpdate(cancel as integer)

        dim lKey as long
        dim lYear as long
        dim rs as dao.recordset
        dim bLoop as boolean

        lKey = Me.SomeIDField          'it can be string also, anything unique to a row
        lYear = Me.cboyear                'make note of entered year

        'Loop thru current subform records
        set rs = me.recordsetclone
        bloop = true
        do while bloop = true
            if rs.eof = true then
                bloop = false
                'if years match but id does not then report duplicate, set cancel=true to abort update
                if rs!SomeIDField <> lKey and rs!YearField = lYear then
                    cancel = true
                    bloop = false
                    msgbox "Record already exists"
                end if
            end if
        set rs=nothing
    end sub

    LVL 44

    Accepted Solution

    If, for example, the subform was related to the main form on CourseID, you could make the primary  key of the table behind the subform CourseID and YearOfStudy.  Now you can never get the same year in twice for the same course.  Something like that?  You shouldn't need any code.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    IF ... the subform does not need to be updateable (?) then you could Group (in your query) on that date.

    Also ... you could still define a unique Index on that date AND another field (if there is one) wherein the combination of those *two fields together* cannot occur more than once.

    Just a thought.

    LVL 9

    Expert Comment


    Try locking the date field of the subform under the fields data properties.
    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    "From entering a duplicate value into an embedded subform."
    Sorry ... missed this part !  re 'updatable'.

    LVL 13

    Expert Comment

    The double primary key is the easiest solution and guarentees that duplicates won't occur.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Mr BW: (re PK)

    "it can potentially have the same value entered twice in the table ......... **I just don't want it to appear twice when viewing it as a subform."

    Note ... just an fyi ...

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Sorry MrBW ... ignore last post ... I missed 'double'.

    my mistake ...


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now