Option to edit record if it already exists

[NewShortStuff]

I created a database for data entry. I have selected three fields as primary keys to avoid duplicates. These fields are [Month], [Lease], and [Amount].

For every month I enter an amount for a lease with a data entry form, which opens in add mode. I have a text box for the month and I select the lease from a combo box.

Sometimes I have two amounts for a lease in one month.
When I go to add a new record and if the record already exists, I get the error message, "The changes you requested to the table were not successful because they will create duplicate values in primary key." I want to customise the message to read, "Record Already exists, do you want to edit existing record?"
I then want to go to the record and open it in edit mode to make changes.

I hope this is possible.

Thanks in advance

 

[BNPMike]

Amount is not good key item. It relies on people choosing different amounts in order to make your database work. I would have thought something like Lease (as a foriegn key) plus either payment time (timestamp) or payment_serial (an increasing number).

Your example is not easy to understand. Surely if a person makes a second payment in a month, you wish to keep the two payments separate for all sorts of financial reasons.

So in summary your technical problem goes away if you re-design your database to more closely match the business situation.

 

[NewShortStuff]

I removed the primary keys.
I know this sounds complicated but what I basically need is to check if a record exists
if it does have the option to go to record and edit
if it doesn't create new record.

 

[BobJacksonNCI]

You could create a Before Update event for the form and write code using Dlookup to see if a record meeting your criteria already exists.

It gets a bit complicated because you'll need to toggle between Data Entry and Edit modes.



HTH,
Bob