Results 1 to 8 of 8
  1. #1
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58

    Validation Rule on a form

    Hi,



    I have this form to enter monthly car sales data.
    I want to write a validation rule so i don't want to enter one salesperson's data twice in a given month.

    That is the values i entered in the fields "Sales Date", "Dealership Name" and "Full Name", combined together should not repeat.
    Attached is the form sample.

    Thanks in Advance.
    Happy Friday everyone.

    Regards,
    Mismag
    Attached Thumbnails Attached Thumbnails Validation rule.JPG  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Validation Rule will not help with that. Options:

    1. set SaleDate and salesperson as compound index in table and let Access notify users when record fails to commit because of duplication

    2. filter combobox to not show sales people who already have record for the entered date - this is called cascading combobox

    3. code in form BeforeUpdate event validates data
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    June7,

    set SaleDate and salesperson as compound index in table and let Access notify users when record fails to commit because of duplication
    Can I do this to already designed table?


    Thanks,
    Mismag

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, table in design view, click Indexes on Design tab. You might Bing or Google the topic for more details.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Watching videos on how to create compound index, while waiting for the reply.
    Thanks a lot.

    Regards,
    Mismag

  6. #6
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    June7,


    Quote Originally Posted by June7 View Post

    2. filter combobox to not show sales people who already have record for the entered date - this is called cascading combobox

    3. code in form BeforeUpdate event validates data
    I am new to Access. May I know how to do these two steps?

    Thanks,
    Mismag

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Both are common topics and there are many discussions describing. I do not want to repeat details here. Suggest you search and when you have code with specific issue, post question.

    Usually with cascading combobox the requirement is to only show records that match a value in the first control. Example: select a state and show only cities in second combobox for that state. You want to list items that are NOT already associated with a given input (a date). This is a bit more complicated. Example combobox RowSource:

    SELECT Person FROM Personnel WHERE Dealership = " & Me.cbxDealer & " AND PersonID NOT IN (SELECT PersonID FROM Sales WHERE SalesDate=#" & Me.tbxDate & "#")

    Advise not to use spaces nor punctuation/special characters in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    June7,

    Both are common topics and there are many discussions describing. I do not want to repeat details here. Suggest you search and when you have code with specific issue, post question.
    Yes I understand. Sorry for asking a common question.

    SELECT Person FROM Personnel WHERE Dealership = " & Me.cbxDealer & " AND PersonID NOT IN (SELECT PersonID FROM Sales WHERE SalesDate=#" & Me.tbxDate & "#")
    This example is good enough for me. I wasn't able to figure out the logic for my scenario.

    Thanks,
    Mismag.

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

Similar Threads

  1. Form Validation Rule from Another Query
    By CodenameAter in forum Forms
    Replies: 11
    Last Post: 05-28-2017, 08:41 PM
  2. Replies: 2
    Last Post: 06-16-2015, 03:52 AM
  3. Form Validation Rule
    By galkej in forum Forms
    Replies: 4
    Last Post: 07-01-2012, 06:38 PM
  4. Validation Rule in Table vs. Form
    By LanieB in forum Forms
    Replies: 2
    Last Post: 03-12-2012, 03:27 PM
  5. Validation Rule Form AfterUpdate
    By GraemeG in forum Programming
    Replies: 6
    Last Post: 02-09-2011, 10:22 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