Solved Unwanted records are created in a table when user changes forms (1 Viewer)

hhag

Member
Local time
Today, 18:58
Joined
Mar 23, 2020
Messages
69
Hi,
I've one annoying thing that happens every know and then.
I've added a navigation form (An Access standard one) where I've included all forms in different category tabs. Some forms add new records in a table while others just update existing records with an additional data.
It's when I move in the navigation form from a form meant to add new records (no data has been entered yet) to another form that a new record is added. This is not the result I want. Since no data has actually been added, I don't want any new record to be created. (As soon as new information has been added I use data validation to secure that all necessary data is added before you can close the form).

Does anyone have any idea how to solve this? I'm about to use a closing event and write some code in order to erase the new record, but there might be an easier solution? If code is necessary - any smart ideas how to solve it?

Rgds,
HHAG
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:58
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you using any code to pre-populate any of the fields in the form for adding new records? If so, you may have to change your code to avoid creating a "blank" record.
 

June7

AWF VIP
Local time
Today, 09:58
Joined
Mar 9, 2014
Messages
5,423
Provide relevant code for analysis. If you want to provide db for analysis, follow instructions at bottom of my post.
 

hhag

Member
Local time
Today, 18:58
Joined
Mar 23, 2020
Messages
69
I set 'standard values', which I define in the table design. It might be this that creates the unwanted records?
When staring to enter data, I use this code to secure all necessary data (Some in Swedish....)

1589320242247.png
 

hhag

Member
Local time
Today, 18:58
Joined
Mar 23, 2020
Messages
69
I'll respond to any new answers tomorrow.
And I I'm so greatful for all your advices! 🙏
 

June7

AWF VIP
Local time
Today, 09:58
Joined
Mar 9, 2014
Messages
5,423
I would not use special characters such as * (which is wildcard) as a flag in Tag property. Use some text such as Validate then change the code to:

ctl.Tag = "Validate"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:58
Joined
May 7, 2009
Messages
19,169
see post #2.
also, are there subforms that are DataEntry set to Yes and pre-populating some of the control's value.
without warning, this record will be saved when you move on another form/subform.
 

hhag

Member
Local time
Today, 18:58
Joined
Mar 23, 2020
Messages
69
I don't have any subform.
I deleted all pre-populating fields (some where pre-populated in the table, and some in the property sheet of the form) but still the record is saved.
And then I added the follwing code just after the IF clause above:

Code:
If IsNull (Me.cboVäljAffM) OR IsNull (Me.cboKontakt) Then
Msgbox ".....(some swedish text) .....", vbOkOnly, "....( some more swedish text) ...."
     DoCmd.RunCommand acCmdDeletRecord
End if
Doesn't above erase the created record, or at least prevent it from being created?
My goal is no record should be saved if you haven't added necessary data and if you move to another form .... How difficult could it be?
 
Last edited by a moderator:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:58
Joined
Feb 28, 2001
Messages
26,999
My goal is no record should be saved if you haven't added necessary data and if you move to another form .... How difficult could it be?

What you said isn't the way it works, though, the key word being "necessary" data. Access doesn't care whether the particular info in the bound controls is necessary to you. To Access, if you have data in a form bound to a record, you must either erase it all or UNDO it or something similar. If you have a bound form, even with default values in fields you consider unimportant, moving to another form is navigation and navigation saves "dirty" (i.e. non-empty) records. If you don't want to save a record, it MUST be blank before you attempt to navigate.

This is pretty much what theDBguy told you in post #2 but I am using different words to emphasize a point.
 

hhag

Member
Local time
Today, 18:58
Joined
Mar 23, 2020
Messages
69
Thank you for claryfing @The_Doc_Man. So, you mean that if not some important controls have data, I should first erase all other controls having data before I navigate? All controls should be empty before moving along?
 

hhag

Member
Local time
Today, 18:58
Joined
Mar 23, 2020
Messages
69
I try once more to explain my issue.
I use a navigation form (standard one included in Access, both with horisontal and vertical tabs). Here I've placed my form which I intend to use adding new records.
The issue is a new record is created as soon as I select this 'Record-adding form'. Maybe since some of the fields in a record has pre-set values.
I don't want any new record to be saved if I directly, without entering any new data into the form, jump to another form or if two specific fields in this new record not are populated by the user.
I've tried everything I believe. I've tried 'DoCmd.RunCommand acCmDDeleteRecord'. I've tried to set all pre-set values to blank when the form is closed.
Now I've an idea to manage the property sheet and actually save a new record with a Command button. I actually also have added this nice 'Delete' button that are included in Access without any success.

How on earth do I prevent an incomplete record from being saved?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:58
Joined
Feb 28, 2001
Messages
26,999
All controls should be empty before moving along?

What you said sounds pretty much like what I meant.

How on earth do I prevent an incomplete record from being saved?

There is a thing called the Form_BeforeUpdate event which fires just before you are about to update the recordset that is associated with a bound form. If you navigate with a "dirty" form, you have to save it before that navigation, and the Save "high-level" action triggers a sequence of form events at the lower level of what is going on. At the low level, Access has two and only two major options for bound forms. Either you can make the form look like the underlying record or you can make the underlying record look like the form. These are not piecemeal operations - they are "all or nothing at all" operations. Making the form look like the record is an "UNDO" action and making the record look like the form is a "SAVE" action. You might wish to look up the UNDO action to understand it better.



BeforeUpdate at the form level can be canceled because it is invoked via

Private Sub Form_BeforeUpdate(Cancel as Integer)

If you fear saving the content of a form that doesn't meet your requirements, this is the place where you have to intercept it and take other action. Generally, having non-blank default values on a bound form will lead to what you describe. If you tried to delete the record in code, your problem is that it isn't saved until the step performed by Access that executes between the BeforeUpdate and AfterUpdate events. Can't delete what hasn't been saved. When I had a form that was not going to be saved, first I didn't have non-blank default values and second I used the UNDO operation that would reset all fields to their initial values. But in your case, that would include reasserting defaults, I think.

I might advise that you take the OTHER approach in the BeforeUpdate event. Rather than trying to interfere with saving an incomplete record due to those defaults, I would not have default values in the form. Instead, I would have code in the BeforeUpdate event that tests each field and if necessary, assert a default while in the BeforeUpdate event.
 
Last edited:

hhag

Member
Local time
Today, 18:58
Joined
Mar 23, 2020
Messages
69
Thank you @The_Doc_Man! You would just now how much I appreciate you time and feedback. 🙏 :)
I'll try your advice with removing all default entries and then test each fields in the form and if necessary set some to default values. And I will return within soon.
 

hhag

Member
Local time
Today, 18:58
Joined
Mar 23, 2020
Messages
69
Well, soon it wasn't.... too much work.
I've done above changes and I've also set the data property "Allow extensions" (translated from Swedish since I've a Swedish version of Access) till 'No', and when I want to add a new record I've inlcuded a Commandbutton (from the scope of different command buttons included and ready to use in Access) so the user is more 'active'.
This seems to work.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:58
Joined
Feb 28, 2001
Messages
26,999
I didn't say it wouldn't be a lot of work. However, in the long run you have better control and get the results you want. This is not a bad thing when taken as a whole.
 

Users who are viewing this thread

Top Bottom