How to undo saving a new record?

[Amit]

Hi,

I have a form to enter monthly fees for each budget, based
on a table. Each budget can only have one monthly fee for
any particular month. So, in the BeforeUpdate event of the
form, I check for the selected fee month (which is done
using a combo-box), and if there is already an entry in
the table for that budget and month combination, I give a
warning message, do a CancelEvent, and set the focus to
the combo-box for selecting the month again. It's all
working fine.

But, I noticed that if I close the form after getting the
warning message using the right-hand top corner "x" and
before changing the month, the new record is still written
to the table, creating a duplicate entry for the month.

In the table, I'm using a separate field (FeeID) as the
Primary key instead of the BudgetID-MonthID combination.

How do I stop the record from being written till
everything is fine, or to undo the saving of the record in
case the user closes the form without changing the month?

Thanks!

-Amit

 

[Allen Browne]

Hi Amit

Your approach makes perfect sense, and cancelling the form's BeforeUpdate
event should stop the record being saved.

You could also set a unique index on BudgetID + MonthID (in addition to the
primary key if you wish).

1. Open your table in Design view.

2. Open the Indexes (View menu).

3. In the Index Name column, enter a name such as BudgetIdMonthId

4. In the lower pane, set the Unique property to Yes.
If both fields are required, set Ignore Nulls to No.

5. In the Field Name column, enter
BudgetID
and then on the next line:
MonthID

This prevents a duplicate being entered by any means.

 

[Amit]

Allen,

Once again, many thanks for the clarification and your
help. That worked perfectly!!

-Amit