Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: check for duplicate on 4 fields

    I am having so much problems with this.. Please can someone help!?!!

    I have a database that records courses.

    The table in which all the information is recorded is "Tbl_courses"
    In this there are the fields

    Course ID, Date, Training Course, Training Venue, Trainer

    When new courses are created they are done so on a form "Frm_Course_Normal" and the control objects are called

    Val_date, Val_TrainingCourse, Val_TrainingVenue, Val_Trainer

    Can someone help me write a code that will warn me that the course I am creating, already exists ... i.e the course I am creating has SAME date and SAME TrainingCourse SAME Venue and SAME Trainer.

    PLEASE PLEASE PLEASE HELP..

    I CANT DO IT...

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    create a multiple unique index on these fields. so it won't let you enter same combinations into these fields.
    Last edited by ghozy; 08-13-04 at 09:46. Reason: typo
    ghozy.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Check for duplicate records

    Hi

    This chestnut keeps cropping up!

    If you do not want to use ghozy’s suggestion, or you want to trap the resulting message and write your own hander in the event of duplication, then try something like this

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Dim Criteria As String
        Dim NumOfRecords As Integer
    
        Criteria = "Date = #" & Format(Val_date, "mm/dd/yy") & "#  "
        Criteria = Criteria & "AND [Training Course] = '" & Val_TrainingCourse & "' "
        Criteria = Criteria & "AND [Training Venue] = '" & Val_TrainingVenue & "' "
        Criteria = Criteria & "Trainer = '" & Val_Trainer & "'"
    
        NumOfRecords = DCount("Course ID", "Tbl_courses", Criteria)
    
        If NumOfRecords > 0 Then
            MsgBox "Your Message records exist here"
            Cancel = True   'this will cancel the update
        End If
    End Sub
    In this example I have assumed you are using UK date format (needs changing to US format) and that all other fields are text (except the ID!)

    Hope this give you some ideas.

    One other thought.

    I have also written a DB with a course table having, date, provider and venue fields.
    All these options are stored in separate table for selection by the user from a combo control, otherwise, when you use code like the above, if it is left to the user to type this venue etc, and they miss type the name, the above will not work!


    MTB

  4. #4
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Talking

    EXCELLENT!!

    THIS IS WHAT I HAVE BEEN LOOKING FOR!

    Thank you so so much. I can finnally work on something else now.

    Your help is ever much appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •