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?
Code:
On Error GoTo Err_Command322_Click
DoCmd.GoToRecord , , acNext
Exit_Command322_Click:
Exit Sub
Err_Command322_Click:
MsgBox Err.Description
Resume Exit_Command322_Click
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:
Code:
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
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.
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.
Doesn't matter. If your code dirties the record, Access will save the record whether you tell it to or not. There is almost certainly new code in the module that is causing the problem
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?
Defaults defined at the table level get set when the data gets saved. I found this out the hard way Defaults defined at the form level get set when the form gets dirtied. Defaults, by themselves, do not dirty a form.
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.
I've only run into one situation where this caused me a problem and that is when a value in a field that had a default was checked in the BeforeUpdate event so it was before the record was saved and depending on the value of the field with the default, something different happened. So, in this case, since the BE was SQL Server, the default wasn't applied in the form. I think when the BE is ACE, the default gets applied as soon as the record gets dirty as it does for form defined defaults. So, the solution was to modify the form to add the default for this particular control. The default remained on the table column, not that it mattered.
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.
Did you at least write yourself a note and leave it prominently displayed in the code? Unless this is an emergency release, using bandaids and just moving on is poor practice. You should fix the code while the problem is fresh in your minds.
Unsolicited advice from someone with the scars to prove that good practice matters and you need to make it a habit.
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.
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.
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.
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.
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.
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.
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 reason this code causes empty records is because you are moving to the next record, which is empty and then moving one more so the first empty record gets saved. You have NO validation code that prevents the empty record from being saved. While setting the form's cycle property to current record seems to solve the problem, it doesn't actually. You can still save empty records, just not this way. The better solution is to use the form's beforeUpdate event to keep an empty record from being saved.
PS, ALWAYS give your controls meaningful names. Command322 is just sloppy and also an indication of how many controls you have added to this form.
@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.
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.
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.
Defaults defined at the form level get set when the form gets dirtied. Defaults, by themselves, do not dirty a form.
