Next record button has started creating new records

[wackywoo105]

I've used this code for years to move to the next record. Over the last week or so it has started adding a new record if you are on the last record. It has never done this before in probably about 10 years. Can anyone help with how to stop it creating new blank records?

On Error GoTo Err_Command322_Click

    DoCmd.GoToRecord , , acNext

Exit_Command322_Click:
    Exit Sub

Err_Command322_Click:
    MsgBox Err.Description
    Resume Exit_Command322_Click

 

[Minty]

Is your form set to allow new records?
Maybe it wasn't and now it is?

 

[wackywoo105]

Thanks. I found and disabled "allow additions". The problem is now the add new record button won't work. Both this and next record button produce a pop up of "You can't go to the specified record", which is what use to happen with the next record button when you got to the end.

EDIT:

I now have:

On Error GoTo Err_Command327_Click

Me.AllowAdditions = True
    DoCmd.GoToRecord , , acNewRec
Me.AllowAdditions = False

Exit_Command327_Click:
    Exit Sub

Err_Command327_Click:
    MsgBox Err.Description
    Resume Exit_Command327_Click
   
End Sub

and

On Error GoTo Err_Command322_Click

Me.AllowAdditions = False
    DoCmd.GoToRecord , , acNext
Me.AllowAdditions = True

Exit_Command322_Click:
    Exit Sub

Err_Command322_Click:
    MsgBox Err.Description
    Resume Exit_Command322_Click

I've never needed this before. I wish they would stop changing and breaking things that have worked flawlessly for years.

 

[MajP]

Over the last week or so it has started adding a new record if you are on the last record. It has never done this before in probably about 10 years.

Did you add any code to the form that could force a record to save?
For example did you add
me.dirty = false
in one of the events?

 

[LarryE]

I doubt it is actually adding a new record. It is probably just going to a new one so it can be added. Just add
DoCmd.GoToRecord , , acLast
to your error routine, so on error it goes to the last record, not a new one.

 

[wackywoo105]

Hmm. I have a back up copy and testing this there are no new records created. There are no extra me.dirty = false. Allow additions in form properties is true. I can't see why it has changed how it works. I think something must be entering data and thus activating the new form. I've done that much recently I'm not sure what but I guess I will find it at some point.

Either way I prefer the new version, as now definitely can't accidently create a new record.

 

[gemma-the-husky]

Maybe your current event has some code that would set a value in the new record. I'm not sure, but would any default values be set immediately on entering the new record area?

 

[MajP]

I found this out the hard way

Can you explain what are the different impacts of these two methods? In what cases do you need to be concerned of these impacts. My initial assumption would have been that there would not be any impactful difference since nothing is saved until record is saved.

 

[wackywoo105]

Thanks everyone. I think I have altered something to update/save data on a new form. I will track it down when I have time, but for now allowadditions = false is keeping it at bay.

 

[Marshall Brooks]

Semi-old thread, but I used navigation buttons from Stephen Lebans and found it was doing the same thing and modified it slightly.
I can't find the link, it is similar to this: https://www.access-programmers.co.uk/forums/threads/custom-navigation-button.164220/ - and someone else modified it and I found the link recently but apparently didn't save it.

Found: https://www.lebans.com/recnavbuttons.htm

The next button code looks like this:

Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click

'With Me.Parent
  If Me.Parent.CurrentRecord < Me.Parent.Recordset.RecordCount Then
    Me.Parent.Recordset.MoveNext
'  Else - Removed MB 24-Aug-2023
'    .Recordset.AddNew
  End If
'End With

Exit_cmdNext_Click:
    Exit Sub

Err_cmdNext_Click:
    Box Err.DESCRIPTION
    Resume Exit_cmdNext_Click
    
End Sub

The complicated part was having the button "grayed out" when you were on the last record, but I got that working also, eventually.

 

[LarryE]

You can set the forms cycle property to Current Record, that way you must click a button to add a new record and there is no need to alter the forms Allow Additions property. The buttons On Click Event triggers:
DoCmd.GoToRecord acActiveDataObject, , acNewRec
but you need to actually enter something before the record can be saved. If you are concerned about unauthorized records, this may help.

 

[Marshall Brooks]

Try LarryE's suggestion. Was news to me, but it makes sense.

Both this and next record button produce a pop up of "You can't go to the specified record", which is what use to happen with the next record button when you got to the end.

Would not happen with the code in Reply #15

Your Add Record Button Code was:

On Error GoTo Err_Command327_Click

Me.AllowAdditions = True
    DoCmd.GoToRecord , , acNewRec
Me.AllowAdditions = False

Exit_Command327_Click:
    Exit Sub

Err_Command327_Click:
    MsgBox Err.Description
    Resume Exit_Command327_Click
 
End Sub

Two things here:

If you set AllowAdditions = False in the form properties, the code above is correct, BUT ... You need to move the Me.AllowAdditions = False line to the Form_AfterInsert() Event. Then the add button will work again. (I did the same thing when I was trying to prevent unauthorized additions and got the same error and this solved it - from another forum thread on here).

If you set AllowAdditions = True in the form properties, you shouldn't need either Me.AllowAdditions statement for this button.

Your revised Next Record code was:

On Error GoTo Err_Command322_Click

Me.AllowAdditions = False
    DoCmd.GoToRecord , , acNext
Me.AllowAdditions = True

Exit_Command322_Click:
    Exit Sub

Err_Command322_Click:
    MsgBox Err.Description
    Resume Exit_Command322_Click

I think you were just trying things here to see what would work, but ...

If AllowAdditions is false in the form properties, you don't need either of the Me.AllowAdditions statements above. It is already false and you DO NOT want to set it to True. If AllowAdditions = True in the form properties, then this could work, but as you said, you will get the "You can't go to the specified record" message if you are on the last record, which would annoy me.

Finally - I'm assuming that you have the built-in Form Navigation buttons hidden if you are adding code to your own buttons. Otherwise, the built-in buttons may still add a new record - I think LarryE's code may fix that.

 

[Marshall Brooks]

@Pat Hartman posted my code, which does NOT create empty records, instead of the OP's.

Commend322 does indicate at least the minimum number of controls added to the form, but not necessarily the number of controls currently on the form, but I agree, meaningful names would be helpful.

 

[Isaac]

If your code dirties the record, Access will save the record whether you tell it to or not

That's one of the things I dislike about Access. It is just a bit too save-happy, which isn't necessarily "right" or "wrong", it's just wildly different from what most users are used to when using most other platforms, which don't risk eventually finalizing a saved record just because you started filling out the first 3 letters of your name! Access is great, I'm just saying this is one of my few complaints.

 

[Marshall Brooks]

Concur with @Isaac. I've worked around it, but just about any other application let's you make changes and then when you want to exit asks "Do you want to Save?" (which is annoying if you just saved and haven't made additional changes.)

Unless you add some kind of Before_Update verification, Access saves and then hopes you have a backup of the old version if you didn't intend to make the change.

 

[Isaac]

I know we've argued about this before Pat so I won't start, but , while I agree that there is a way to handle and of course, I know how to handle it, but I still wish it didn't err on the side of saving so quickly at so minimal a trigger. And disagree that that is what users expect.

If I started filling out a quote on Allstate's insurance page, and just began typing "I.s.a." of my name and then closed the computer and walked away, I definitely would NOT expect a quote to be formed. In my experience user happiness is a funny thing. Of course they are happy when the app can do great things, but a more universal rule is they are happy when their expectations are met. When an app surprises them by doing something most apps don't, it's more likely than not to be annoying, not impressive - unless they were trained for it

 

[Marshall Brooks]

@Issac - The current version of Word M365 has this correct, in my opinion. It will not save unless you tell it to do so, but if you just type for 20 minutes or so, it will say "You haven't saved this document yet, would you like to do so now?" (And then it prompts you to save to OneDrive or the cloud and it is difficult to figure out how to save it locally, so not quite perfect yet.)

@Pat Hartman - Agree with what you are saying, but sometimes there aren't really good options.

Initially, we didn't have ANY controls. We just told users "Be careful that you don't type anything you don't want in here, b/c it saves automatically and the old data will be overwritten unless we know about it and can restore it from the backups."

A developer before me came up with an Undo and a Save button and called it good, but he didn't realize that the data was still saved automatically first. i.e. if you clicked the save button you saved the data. If you clicked Undo, you went back to how the record originally was. If you didn't click Undo and also didn't click Save, the record was saved.

The prompt makes it work like other programs and you only see the prompt if you made a change and try to go to a different record or close the DB.

Some fields can't be easily validated. We have a Notes field and it will just contain text. The computer can't logically tell if the text makes sense. If someone changes the field contents to YYYYYYY - there isn't much you can do other than saying "Did you intend to change this field?" which is more annoying than asking "Do you want to save this record?"

There are SOME things you can do. Many databases have a Lock/Unlock button before you can edit a record. Our database we change the background of the field to yellow with the OnChange event and enable the UNDO and SAVE buttons and back to tan and disable the buttons when the record is saved. It doesn't stop the users from putting in bad data, but alert them if something fell on the keyboard and they didn't KNOW they had made a change.

 

[Gasman]

So it is up to you to supply that?
I was just on a website and had to enter the email address that I used and then enter it again to confirm it.
This site was so unsophisticated that I could copy and paste from the first entry to the second.?????
Then I decided to check I had the correct address, and I didn't, so changed the first entry and about to change the second entry, but as soon as I left the first entry, it complained addresses were not the same?

So check was in the wrong place?

I know we've argued about this before Pat so I won't start, but , while I agree that there is a way to handle and of course, I know how to handle it, but I still wish it didn't err on the side of saving so quickly at so minimal a trigger. And disagree that that is what users expect.

If I started filling out a quote on Allstate's insurance page, and just began typing "I.s.a." of my name and then closed the computer and walked away, I definitely would NOT expect a quote to be formed. In my experience user happiness is a funny thing. Of course they are happy when the app can do great things, but a more universal rule is they are happy when their expectations are met. When an app surprises them by doing something most apps don't, it's more likely than not to be annoying, not impressive - unless they were trained for it

 

[Marshall Brooks]

@Pat Hartman - We are MUCH closer to being on the same page than you (or I) might have originally thought.

I personally don't care if you use Me.Undo whenever a user makes a data entry mistake as some developers do. Your user might complain though

Valid point. There are numerous places in our database where you can cancel an action. We used to use Me.Undo if you did this - which wiped out ANY fields you had changed since the last change. Now we just set the status field back to the .oldvalue.

and if you also want the user to only save "consciously", then add the Save button and the code that goes with it.

We do one better, although it was annoying to code. We have a Save button, but it is disabled unless you make a change. That makes it very obvious that SOMETHING was changed whether the user intended to do so or not.

I wonder if he was the developer who wrote more than 5,000!!!!!! lines of code to validate the data but never managed to get any into the form's BeforeUpdate event.

Maybe - at the time he knew more about Access than I did - which isn't saying a lot for him.

It makes no sense for a DOB or DOD to be in the future.

I hope my DOD is still in the future, but point taken!!!

The reason I say that the lock/unlock is unnecessary is because it still doesn't keep the user from being a klutz. They just have fewer accidents.

Interesting! I thought that would be something you would recommend. I've seen it on a couple of our company databases by more experienced users than myself. Personally, I found it too many mouse clicks and too annoying - i.e. I have to go to this record, hit unlock, make my changes, hit save, hit lock, go to the next record, rinse and repeat ...

 

[Marshall Brooks]

If you have the lever-style doorknobs, cats can figure out how to jump up to them and hang on them and open the doors ...