Solved

Prevent new record being selected after a deletion

Posted on 2016-11-28
6
55 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
6 Comments
 
LVL 49

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 47

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 35

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 49

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

803 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