Results 1 to 3 of 3
  1. #1
    Star Lounger Roderick's Avatar
    Join Date
    Jan 2004
    Location
    Near Halifax, Nova Scotia, Canada
    Posts
    88
    Thanks
    5
    Thanked 4 Times in 4 Posts

    Prevent duplicate record via form entry (Access 2000)

    I'm not an Access wiz by any means but I should be able to figure this out and must be missing something. I have about the simplest database possible ... just one table with two fields ... a date field and a dollar amount field. The database is to store a daily currency exchange rate that can be entered and viewed by various people in the office. I want to use a form to enter this information into the table. Since there is only one entry per day, I want the form to reject a duplicate entry for a date that already exists in the table should someone try to duplicate an entry.

    In the table, the date field is Required=Yes and Indexed=Yes (No Duplicates). If I enter data directly into the table via data sheet view, I get an error if I enter the same date twice so I know it is working there. The dollar amount field has a validation rule that it must be greater than 1. Required=Yes and Indexed=No. I haven't set any primary key field.

    On the form, the date is the first field and on the Properties Data tab has a default value =Now() so that it automatically picks up the current date. Enabled=Yes (although I'd prefer No as there is no real need to have toe focus go here) and Locked=Yes (so the date can't be changed). The dollar amount field is Enabled=Yes and Locked=No as this is the only field where anything needs to be entered.

    The problem is that I can use the form to enter an amount, then go back and use it again with the same date and it puts multiple entries into the table with duplicated dates. Is there some way to get the form to refuse an entry if the date already exists in the table?

    Yeah, I know I could very simply use an Excel spreadsheet or even a Word document for the same purpose but the Access form looks so much nicer and is very simple to use if only I could get it to refuse duplications.

    Rod Corkum

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent duplicate record via form entry (Access 2000)

    You aren't really entering duplicate dates because the Now() function returns a date and time, so that each time you use it, you get an unique date/time. Change the default to Date() and that should cure the problem.
    Charlotte

  3. #3
    Star Lounger Roderick's Avatar
    Join Date
    Jan 2004
    Location
    Near Halifax, Nova Scotia, Canada
    Posts
    88
    Thanks
    5
    Thanked 4 Times in 4 Posts

    Re: Prevent duplicate record via form entry (Access 2000)

    Thanks, I knew it had to be something simple I was missing.

    Rod

Posting Permissions

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