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?
Rob4077Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
Gustav BrockConnect With a Mentor CIOCommented:
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 FyeConnect With a Mentor Commented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
PatHartmanConnect With a Mentor Commented:
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
 
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
 
Rob4077Author Commented:
Thanks for your comments. All will work so I have shared the points based on contribution. Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.