Results 1 to 3 of 3
  1. #1
    MvdBergh is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Location
    South Africa
    Posts
    3

    Searching for a duplicate date in a table, based on 4 fields entered.

    I need some assistance on a warning message for scheduling duplicate events for a lecturer.

    I have a scheduling form based on a table named “Beplan_rooster” with the following fields:

    Venue
    Main Programme
    Intake
    Heading
    Date


    Module
    Lecturer


    What I need is VBA code with a warning message to search for duplicate scheduling for a specific lecturer (from the current table “Beplan_rooster”) in the event when a lecturer is scheduled on a date already allocated to him. I would like the warning to display the Heading Date, Module and Lecturer of the already allocated date. This VBA code will be entered in the event “After Update”.

    Any assistance shall be greatly appreciated.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This would be in the BeforeUpdate event - after the update it is too late
    Code:
    Dim CheckDupe
    CheckDupe=DlookUp("pk_field","Beplan_rooster","...criteria, such as: Heading='" & Me!Heading & "' AND Date.....")
    If Not Isnull(CheckDupe) Then
    error message
    Cancel=True
    End If
    Do not use "Date", it is a reserved word in Access and will cause you problems.

    To figure out the syntax of the criteria for the DLookUp, create a new query and add some made-up criteria. Then go to the SQL and copy from WHERE, the syntax will match. Put single quotes around text fields and "#" around date fields.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "Module" is also a reserved word in Access and shouldn't be used as an object name.

    Having spaces in names is also a bad idea. Names should be only letters, numbers and/or the underscore.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-17-2017, 08:57 AM
  2. Replies: 1
    Last Post: 06-20-2013, 05:06 PM
  3. Replies: 4
    Last Post: 06-18-2013, 07:36 AM
  4. Replies: 2
    Last Post: 06-11-2012, 10:34 PM
  5. one date entered to hit 3 fields in one table how?
    By techexpressinc in forum Queries
    Replies: 0
    Last Post: 12-10-2008, 10:43 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums