• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Preventing duplicate values in a sub form.

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!!
0
mbath20110
Asked:
mbath20110
2 Solutions
 
rockiroadsCommented:
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
        else
            '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
            rs.movenext
        end if
    loop
    rs.close
    set rs=nothing
end sub

0
 
GRayLCommented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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.

mx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
tonydemarcoCommented:

Try locking the date field of the subform under the fields data properties.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"From entering a duplicate value into an embedded subform."
Sorry ... missed this part !  re 'updatable'.

mx
0
 
RyanProject Engineer, ElectricalCommented:
The double primary key is the easiest solution and guarentees that duplicates won't occur.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Sorry MrBW ... ignore last post ... I missed 'double'.

my mistake ...

mx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now