Solved

Prevent new record being selected after a deletion

Posted on 2016-11-28
6
66 Views
Last Modified: 2016-11-30
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
Comment
Question by:Rob4077
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 41905296
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
 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 41905517
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
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 41905981
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Rob4077
ID: 41906669
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
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 41906812
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
 

Author Closing Comment

by:Rob4077
ID: 41908070
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question