Results 1 to 3 of 3
  1. #1
    mrm975 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    1

    Check that date and time entered into a form are not already present in a table (not key fields)

    Hi, I am hoping someone could help me.



    I have created a form that allows a Member of a club to make a booking for an Event at a certain date and time. Is there a way to check that before the new entry is added to the table that the Member who is making the booking has not already booked a different Event on the same date and time?

    I have used a composite key of Member ID, Event ID, Date and Time to ensure the member is unable to book the same event at the same time. This does not solve the problem above however.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One option is a DCount() with the criteria testing those 3 fields.

    http://www.theaccessweb.com/general/gen0018.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  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
    1) I really hope you do not have a field named "DATE", nor a field named "TIME". They are reserved words in Access and also built in functions.
    2) Spaces should not be allowed in object names. (Field, Table,Form, Query and Report names)
    3) I would suggest that the PK field be an Autonumber - create a composite (multi-field) INDEX to stop duplicates.


    Create an composite index on the "Member_id" and the "Date_Time" fields to stop duplicate entries. (Event type does not matter.)


    Q) What about if a member signs up for 2 different events where the times overlap (starting 30 minutes apart)? Say tennis is from 1 pm to 3 pm and lawn darts is from 2pm to 4pm.

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

Similar Threads

  1. Replies: 9
    Last Post: 04-11-2019, 03:38 PM
  2. Replies: 2
    Last Post: 09-20-2018, 02:02 PM
  3. Replies: 8
    Last Post: 08-25-2018, 10:00 AM
  4. Replies: 2
    Last Post: 09-26-2017, 03:18 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

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