Search for Duplicate Record in Access

[elephant97]

Hi, I have a form that is used for data entry. My inputs are the following:

Date
Company
Item
Price

What I need to do, is have access search my table storing the information and refuse to save entries where the date and company already exist. So, if the table already has an entry is 8/19/04 for company ABC, a user cannont make another entry for 8/19/04 and company ABC. Rather, I would like access to load the record the user is trying to "overwrite". I suppose this could be accomplished via a VBA record search when the user attempts to save their entry.

My vision is that they would click the "Save" button on the form, an error message would pop-up, and then the record matching their inputted date and company would load.

THANK YOU, THANK YOU!!

 

[Garry in Perth]

I would be designing a seperate table called something like Daily Company Prices and having both the Date and Company fields set jointly as the Primary Keys. Then only unique combinations of Company Names and Dates could be stored into it.

Other (linked) tables would most likely be Companies and Items.

HTH

 

[Garry in Perth]

Sorry - I may have mislead you in my earlier response. You won't need an additional table to the table you already have.

Just re-design the current table to have both the Company and the Date fields as Primary Keys. Assuming that your data entry form has been designed as a pure data entry form, then if the Company-Date pair being entered already exists in your table, then MS Access will automatically display an error message and prevent users from entering such a duplicate Company-Date pair.

On the otherhand, iIf you really must have the pre-existing record displayed, then I think you need to alter the DATA ENTRY setting on the form properties.

HTH