Add and save record in continuous form with buttons

[arnelgp]

On Error Resume Next
Forms!MainForm.SetFocus
Forms!MainForm!Subform.SetFocus
DoCmd.RunCommand acCmdDeleteRecord

 

[LarryE]

On Error Resume Next
Forms!MainForm.SetFocus
Forms!MainForm!Subform.SetFocus
DoCmd.RunCommand acCmdDeleteRecord

Yeah, that's what I thought too. He needs to move the focus to the subform first.

 

[MajP]

IMO it is a lot more intuitive to the user to put the buttons on the subform or continuos form next to the record. As long as you have the real estate.

Or at least make it very clear what is the active record

 

[LarryE]

And always, always, always give the user a chance to cancel deleting a record. I use this on all my record delete routines:

Dim Reply As Variant
Reply = MsgBox("THIS ACTION DELETES THIS RECORD." & Chr(10) & Chr(10) & "DO YOU WANT TO DO THIS?", vbYesNo)
If Reply = 7 Then 'NO
    DoCmd.CancelEvent
    Exit Sub
End If
If Reply = 6 Then 'Yes
    DoCmd.RunCommand acCmdDeleteRecord
    Me.Form.Requery
End If

 

[KitaYama]

And always, always, always give the user a chance to cancel deleting a record.

Deleting records is not considered as a good practice.
It's better to mark the record as deleted. I have a DeletedDate field in all my tables. If a user deletes a record, DeletedDate field is populated and it's considered as deleted. It will not show in search results unless the user tick a checkbox (include deleted records) in search form.

In some cases, the user can not delete a record unless adding the reason. I save the UserID, date and the reason the record was deleted.

 

[Pat Hartman]

I'm guessing this conversation was started because you don't know how to control whether or not a new or changed record gets saved. So, you posted a form and a method that YOU think is the solution and asked how to implement it. The generous experts are helping you to implement YOUR solution. All the suggestions require a lot more work than doing this the "Access" way. So far no one has suggested trying to use Access to solve the problem rather than create a complex solution. So, being lazy and not being inclined to write code I don't need to write, I will.

Perhaps if you familiarized yourself with the Form's BeforeUpdate event you would understand that you, with simple validation code, have absolute control over whether or not a record gets saved. This event is the LAST event that runs before any record gets saved. It CANNOT be bypassed. That is why your validation code belongs here. You validate the record and if it is incomplete or has invalid data, you use:

Cancel = True

To tell Access to not save the record. Then you exit with a message to the user regarding the field with the error and ask him to fix it.

PS, if you are going to use unbound controls to add a new record, I would put them at the top of the form rather than the bottom. Also sort your subform descending so the newest record will show up on the top line after it is entered.

 

[MajP]

So far no one has suggested trying to use Access to solve the problem rather than create a complex solution.

See post #17, that suggestion already made.

 

[Pat Hartman]

Not in so many words.