• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

Prevent new record being selected after a deletion

Instead of using form/subform setup I am using a split form based on a query that joins two tables. Hence when a new record is added to the child table I use the following code in the Current even to manage the link to the parent table and make sure the display is correct as soon as a new record is selected:
    If Me.NewRecord Then Me.fkJobNum = Me.txtJobNum

The problem is that if the user deletes the last record on the list (by selecting it and pressing the delete key) and confirms when the record delete confirmation message pops up, MS Access deletes the record then moves to the next record, a new record, and executes the current event that primes the join again. If the user then tries to exit that new record without completing the entries, the system won't allow it because the new record is not complete. Is there any way to fix this?
0
Rob4077
Asked:
Rob4077
4 Solutions
 
Gustav BrockCIOCommented:
Not really. You can't both validate and not validate the record before an update.

So, when entering a new record, don't set the Value of some fields but set the DefaultValue of the fields. This doesn't make the record dirty and allows the user to move off the record without an update.

/gustav
0
 
Dale FyeCommented:
Instead of using the Current event to insert the job number from the main form, use the forms BeforeUpdate event.  This will not fire until just before the record gets saved, so it will not have a value and you should be able to exit.  This is also the event where you would enter values like: Created, LastUpdated, UpdatedBy.
0
 
PatHartmanCommented:
For populating the foreign key, I would use the BeforeInsert event.  It only fires for new records.  If I were to put this code into the BeforeUpdate event, I would have to first check that this is a new record.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Rob4077Author Commented:
Thanks for the comments. They all make sense but it means that my display will not be refreshed until after the event is executed. The top section of the form displays the parent components and the bottom displays the child. Without that field being primed the header will be blank - is that right? Is there a way around that?
0
 
Gustav BrockCIOCommented:
If you set the default values instead of filling in values, there is no event to fire. No update, no insert. It's that simple. And such a record will display as any other record. Of course, a child record cannot exist.

/gustav
0
 
Rob4077Author Commented:
Thanks for your comments. All will work so I have shared the points based on contribution. Thanks again
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now