Solved saving a record twice

iankerry

Registered User.
Local time
Today, 08:22
Joined
Aug 10, 2005
Messages
190
Hi Guys,

I am not a very experienced programmer, so please bear with my code!

I needed to write this code, because I needed to create a new record in a FILM table, and at the same time create a new record in a FILM_COPIES` using the value of the newly created FilmID to place in the field tblFilms_ID in the filmcopies database.

The routine does what i need it to do. Eventually, I would like to make this a NOTINLIST routine, but baby steps.

The problem I have is, that the routine seems to save two records for the entry i am working on in the Films table. If I use the navigation key to enter a new record, or if i close the NewFilmsForm it creates another copy.

How can I stop it saving again?

many thanks


Code:
Private Sub savebutton_Click()

Dim rs As DAO.Recordset
Dim tid As Integer
Dim tdata As String
tdata = NewData


Set rs = CurrentDb.OpenRecordset("dbo_films", dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs("film name") = NewData

'create data i need to save from the new film field
rs("film name") = NFN 'NFN is the name of the [film name] field

tid = rs("ID") ' save the film id for the next bit

rs.Update ' - this saves and creates the new Film record?

'DoEvents

Set rs = CurrentDb.OpenRecordset("dbo_filmcopies", dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs("tblFilms_ID") = tid
rs.Update

MsgBox "New Film Saved and a Film Copy made."

rs.Close

DoCmd.GoToRecord , , acNewRec ' to clear the form for the next record

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:22
Joined
Oct 29, 2018
Messages
16,239
Hi. Not sure what you're doing but try using Me.Undo in your code.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:22
Joined
Feb 28, 2001
Messages
20,690
Navigating away from the record currently on the form will automatically save it if it is dirty. Your GoToRecord counts as navigation.

Also, it is a fine point and probably subject to discussion, but you are re-using the rs variable. I would close it after the first update even though it probably does get closed eventually anyway. But leave nothing to chance there.

My other question, though, is what is the .Recordsource for the form? Is it either dbo_films or dbo_filmcopies? You are doing a button-based Save operation, but if one of those record sources is also the source of a bound form, you have automatic saves.
 

iankerry

Registered User.
Local time
Today, 08:22
Joined
Aug 10, 2005
Messages
190
Navigating away from the record currently on the form will automatically save it if it is dirty. Your GoToRecord counts as navigation.

Also, it is a fine point and probably subject to discussion, but you are re-using the rs variable. I would close it after the first update even though it probably does get closed eventually anyway. But leave nothing to chance there.

My other question, though, is what is the .Recordsource for the form? Is it either dbo_films or dbo_filmcopies? You are doing a button-based Save operation, but if one of those record sources is also the source of a bound form, you have automatic saves.
Thanks Doc Man, I'll take a look at these suggestions.

Re recordsource - i suppose the answer is both? firstly the dbo.films, then the filmcopies.

But at least it works now, and i will close the rs.

thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:22
Joined
Feb 28, 2001
Messages
20,690
Re recordsource - i suppose the answer is both? firstly the dbo.films, then the filmcopies.

"Both" can't happen without a formal JOIN query as an intermediate.

The .RecordSource for a bound form is the table or query to which it is bound. If you have an unbound query you cannot use the .GoToRecord because you have no records at all. If you tried that with an unbound form, I would expect an IMMEDIATE error, probably of the type "That action is not available now" or something similar.

You COULD dynamically alter your form's .RecordSource, but your earlier comment for being inexperienced suggests to me that you aren't doing that. Therefore, I'm thinking that part of the problem is that your form IS bound.

The fact that using an .Undo helps tends to corroborate that, because that extra "Save" wouldn't occur if the form was not dirty at the time.

I'm belaboring this because you asked "How to stop it?" The answer is not only what to do - but WHY you need to do it. You got a "what to do" from theDBguy, but I like to supply "Why" answers so that next time, you will realize better why something is happening.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:22
Joined
Feb 19, 2002
Messages
32,921
If you are using a bound form, you are writing the record twice yourself. Once with the .AddNew and again when the bound form moves to a new record. Access ALWAYS saves a bound form when it is dirty and you leave the dirty record.

I'm going to ask the question that the others didn't ask. WHY? Why would you need to save the same data in two different tables?
 

iankerry

Registered User.
Local time
Today, 08:22
Joined
Aug 10, 2005
Messages
190
"Both" can't happen without a formal JOIN query as an intermediate.

The .RecordSource for a bound form is the table or query to which it is bound. If you have an unbound query you cannot use the .GoToRecord because you have no records at all. If you tried that with an unbound form, I would expect an IMMEDIATE error, probably of the type "That action is not available now" or something similar.

You COULD dynamically alter your form's .RecordSource, but your earlier comment for being inexperienced suggests to me that you aren't doing that. Therefore, I'm thinking that part of the problem is that your form IS bound.

The fact that using an .Undo helps tends to corroborate that, because that extra "Save" wouldn't occur if the form was not dirty at the time.

I'm belaboring this because you asked "How to stop it?" The answer is not only what to do - but WHY you need to do it. You got a "what to do" from theDBguy, but I like to supply "Why" answers so that next time, you will realize better why something is happening.
Hi Doc_Man

Thanks for helping me get a deeper understanding of things. IT is much appreciated.

Yes, not being very good with the terminology is mis understood your question. Yes there is a query behind the form, with the Films Table and the Films_copies table, joined together on the appropriate fields.

IT is true that I wouldnt know how to dynamically alter the forms recordsource.

I created a test database to work on this code, in case i had to upload it and so i didn't mess with the actual live database. Now that I am putting the code in the live database it is not proving to work, and i am going to go through this this morning.

I think the issue is that in the live database i have linked table (not local) to an SQL server. Then the two tables Films and Filmcopies tables have a foreign key on them...

lets see how far i get on sorting that out!

Thanks again.

Ian
 

iankerry

Registered User.
Local time
Today, 08:22
Joined
Aug 10, 2005
Messages
190
If you are using a bound form, you are writing the record twice yourself. Once with the .AddNew and again when the bound form moves to a new record. Access ALWAYS saves a bound form when it is dirty and you leave the dirty record.

I'm going to ask the question that the others didn't ask. WHY? Why would you need to save the same data in two different tables?
Hi Pat,

A record in the Films table needs to have an associated record in the filmcopies table. This is because we may have several copies of the same film with different info such as dvd region, where the dvd is currently located etc.

so in order to have a link betoween the two records, the filmcopies table has a field called Tbl_Film_ID, which associates that record with the Film name.

Not sure if that is clear!

Thanks

Ian
 

Mike Krailo

Active member
Local time
Today, 03:22
Joined
Mar 28, 2020
Messages
310
You could have a FilmName table and just use the foreign key in your main Film table.
 

Mike Krailo

Active member
Local time
Today, 03:22
Joined
Mar 28, 2020
Messages
310
I think your answer to Pat's question revealed that your tables are not normalized. You should have a Region table, Location table, FilmName table, etc... then all of your foreign keys from those tables go into the main Film table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:22
Joined
Feb 19, 2002
Messages
32,921
You need a films table which holds the common information and then a child table with the info that is specific to a copy. You would do this by using a subform, not an automatic insert because at some point you will need to use the subform to add the second and subsequent copies.. You don't know the details of the copy so you should not be creating an "empty" record. with just a foreign key.

You are doing TWO inserts in the code. One insert for each table. IS THE FORM BOUND? That was my question. If the form is bound, Access is also saving the record and that is what is causing the duplicate in the films table. A form is bound if it has a table or query name in its RecordSource property. The whole point of using a RAD tool (Rapid Application Development) like Access is to take advantage of the things the tool does for you and the major one for Access is bound forms.
 

iankerry

Registered User.
Local time
Today, 08:22
Joined
Aug 10, 2005
Messages
190
I think your answer to Pat's question revealed that your tables are not normalized. You should have a Region table, Location table, FilmName table, etc... then all of your foreign keys from those tables go into the main Film table.
Hi Mike

the tables are normalized at the moment. it is more complex than my original question and we probably have 10 different tables that link, and all of these have foreign keys.

Thanks

Ian
 

iankerry

Registered User.
Local time
Today, 08:22
Joined
Aug 10, 2005
Messages
190
You need a films table which holds the common information and then a child table with the info that is specific to a copy. You would do this by using a subform, not an automatic insert because at some point you will need to use the subform to add the second and subsequent copies.. You don't know the details of the copy so you should not be creating an "empty" record. with just a foreign key.

You are doing TWO inserts in the code. One insert for each table. IS THE FORM BOUND? That was my question. If the form is bound, Access is also saving the record and that is what is causing the duplicate in the films table. A form is bound if it has a table or query name in its RecordSource property. The whole point of using a RAD tool (Rapid Application Development) like Access is to take advantage of the things the tool does for you and the major one for Access is bound forms.
Thanks Pat. We do have other forms for Films, with a subform for copies information. This exercise was just about getting a new film record and corresponding film copy record. and on that form you can create a new copies information.

So yes I have a bound form, and with the me.undo command it stops saving twice and all works well, thanks for your assistance.

Ian
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:22
Joined
Feb 19, 2002
Messages
32,921
WHY would you not use the bound form properties of Access? You should have removed your save code to eliminate the duplicate. If you are going to use Access, you might want to learn to use it correctly. If you feel competent to code your own form functionality, then you should be using some platform that doesn't come with so much overhead or built in functionality. Fighting with Access and overruling something so basic is not a good start to your programming with Access career. You will grow to hate Access if you continue to fight it. If you think you know better, as I said, Access is not the tool for you.

Since you don't know how forms work, I'm pretty sure you didn't put the Undo in the correct event and so Access is still going to save that record on you under certain conditions. If you want to take Access out of the picture, the only safe option is to remove the table/query from the Form's RecordSource so the form won't be bound.

If you want to use Access, it is important to learn how form events let you control what happens. For your situation, leave the form as bound and remove your code that saves the main record. But move your insert of the second record (not a good idea as I've already said but if you insist) to the Form's AfterInsert event. Therefore, the second insert happens only for new records and only after the first record is inserted so the autonumber PK will be available to use as the FK for the second insert.

If you want to have more control over whether or not a record gets saved, use the Form's BeforeUpate event to validate the data. If you cancel this event

Cancel = True

Then, the record won't be saved and the second insert will also not happen. However, the record will still be dirty. If the user wants to cancel the save and discard what he has done so far, you can undo the changes for him also.

Me.Undo

Your code as you have it now will save the bound record but will only insert the second record if your button code runs. Use form events to control things rather than your own button code. You can use buttons to trigger things if you want the action to be conscious. So, pressing a SAVE button should initiate a save

DoCmd.RunCommand acCmdSaveRecord

and that will cause the BeforeUpdate event to run. The BeforeUpdate event is your friend. It is the last event that runs before a record is saved and it ALWAYS runs. It can never be bypassed. That is why it is the most reliable place to do validation. The BeforeUpdate event is the flapper at the end of a funnel. If you close it (Cancel = True), then nothing passes through.
 

iankerry

Registered User.
Local time
Today, 08:22
Joined
Aug 10, 2005
Messages
190
WHY would you not use the bound form properties of Access? You should have removed your save code to eliminate the duplicate. If you are going to use Access, you might want to learn to use it correctly. If you feel competent to code your own form functionality, then you should be using some platform that doesn't come with so much overhead or built in functionality. Fighting with Access and overruling something so basic is not a good start to your programming with Access career. You will grow to hate Access if you continue to fight it. If you think you know better, as I said, Access is not the tool for you.

Since you don't know how forms work, I'm pretty sure you didn't put the Undo in the correct event and so Access is still going to save that record on you under certain conditions. If you want to take Access out of the picture, the only safe option is to remove the table/query from the Form's RecordSource so the form won't be bound.

If you want to use Access, it is important to learn how form events let you control what happens. For your situation, leave the form as bound and remove your code that saves the main record. But move your insert of the second record (not a good idea as I've already said but if you insist) to the Form's AfterInsert event. Therefore, the second insert happens only for new records and only after the first record is inserted so the autonumber PK will be available to use as the FK for the second insert.

If you want to have more control over whether or not a record gets saved, use the Form's BeforeUpate event to validate the data. If you cancel this event

Cancel = True

Then, the record won't be saved and the second insert will also not happen. However, the record will still be dirty. If the user wants to cancel the save and discard what he has done so far, you can undo the changes for him also.

Me.Undo

Your code as you have it now will save the bound record but will only insert the second record if your button code runs. Use form events to control things rather than your own button code. You can use buttons to trigger things if you want the action to be conscious. So, pressing a SAVE button should initiate a save

DoCmd.RunCommand acCmdSaveRecord

and that will cause the BeforeUpdate event to run. The BeforeUpdate event is your friend. It is the last event that runs before a record is saved and it ALWAYS runs. It can never be bypassed. That is why it is the most reliable place to do validation. The BeforeUpdate event is the flapper at the end of a funnel. If you close it (Cancel = True), then nothing passes through.

Hi Pat,

I am sorry if my ineptitude with coding has upset you.

As mentioned in my last post to you I am using a bound form.

"If you are going to use Access, you might want to learn to use it correctly. " - I have been using access for 20 years and whilst I don't feel confident in coding (less so now!), I have built a fairly complex database system, with help my charity run over 1200 events a year.

My issue here was trying to find a way of saving a new film record, and saving the ID number of it in another table, the film copies table.

I had to save the Film Record in order to get the ID number. Then when i closed the form, or navigated to a new record it saved again. This was solved by the Me.Undo code.

It works a treat now, and I thank members of the forum who tried to help me.

Again, I am very sorry if my posts frustrated you.

Best wishes

Ian
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:22
Joined
Feb 19, 2002
Messages
32,921
What frustrates me is that you say you are inexperienced and ask for help. I diagnose your problem, explain why it is occurring, and tell you how to fix it and you ignore the advice. Now you tell us you have been using Access for 20 years. OK.

I'd be willing to donate $100 to your favorite charity if the form as you "fixed" it is not unexpectedly saving records. Or if you find that I am correct, you can donate whatever you want to my favorite charity.

The team that designed Access back in the 90's was top notch. There have been lots of changes/enhancements in the past 25 years but it still works essentially the way it did back in version 2.0 (that's when I discovered it). I am always amazed by what a sound foundation they created. I guess it is because of that sound foundation that Access is still the premier desktop database development platform all these years later. The events they created for bound forms were not random. They were designed for specific reasons to give you control over the process. Ignore them at your peril.
 

iankerry

Registered User.
Local time
Today, 08:22
Joined
Aug 10, 2005
Messages
190
Hi Pat thanks for your message.

Once again I can only apologise for any frustration you feel caused by my post.

Whilst I have been using access for 20 years my programming understanding is not good. I run a charity with 10 employees and this takes up most of my time. Any programming I have done had been in the evening or weekends and have been helped by people like yourself and others on this forum for which I am really grateful.

I posted about a particular problem of a record saving twice and dbguy answered it with something that worked. You seem to doubt that it works but one of my staff members has been using it today and it works. And I posted this.

I can obviously see that duplicate records aren't being saved.

As to your $100 offer I am not sure this is necessary, but if you did want to teamview to my computer you can see it working and also look at some of the dreadful programming behind the system! Email me on ian@artsalive.co.uk if you are interested.

It maybe after a year of lockdown, and the lack of events that my charity runs and the financial problems this brings that I am particularly sensitive at the moment. But I don't think I have received a response like this to a fairly straightforward post. So I think it is time I left this forum and will look to do that now.

Thanks again.
 

Users who are viewing this thread

Top Bottom