delete subform record when "cancel" button is clicked

[mike_980]

I am using this code for all my forms to prevent empty records in my tables

DoCmd.SetWarnings False
If Me.Dirty = True Then
   If MsgBox("Do you wish to cancel?", vbYesNo, "Delete Confirmation") = vbYes Then
            DoCmd.RunCommand acCmdDeleteRecord
   End If
End If
DoCmd.Close
DoCmd.SetWarnings True

it seems to work well and I can't find any bugs, however I have 1 form which contains a subform and the subform data is still sent through to the table when the form is closed. any ideas?

[ItsMe]

DoCmd.RunCommand acCmdDeleteRecord
Will work for the record that has current focus. You need to set focus on the subform and also navigate to the apropriate record before your docmd.

There is a sample DB I uploaded here that demonstrates controls that interact with subforms
https://www.accessforums.net/sample-...orm-38074.html

[mike_980]

perfect, just the answer i was after thanks alot

[mike_980]

Unfortunately I cannot get it to work,

I will attach a copy of a sample DB, the idea is that when the cancel command is clicked, no record should enter the either table even if the form and subform are completely filled out. I can't get it to work for the life of me.

Ignore the fact it is massively stripped down please I struggled getting it below 500kbs!

example db.accdb

[ItsMe]

You have referential integrity rules set in your relationships window. Using DoCmd.RunCommand will override these rules. Using MenuBar will not override your referential integrity rules. It behaves as though the user is clicking tools within the form's GUI. For instance

DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70

will not delete the entire record because of the rules you set. You can search

"DoCmd.DoMenuItem Method"

in the Visual Basic help files to learn more about the available functions .

Also, when refering to subforms, make sure you are refering to the subform container. The control on the main form that holds the actual object has a name of its own. On your main form the name of the subform control is "subfrm"

When you close a form, make sure it is the last line of code in the procedure. In your case, I moved the setwarnings lines before the .Close

Play around with this a little. Notice that I have code commented out so I can study the behavior of a few isolated lines of code. For instance, I don't need the If Then or MsgBox lines firing to determin if I have focus on the correct object or if I am deleting the correct fields/records.

Another Item is your form's properties. You will want to change the "Cycle" property to = "Current Record". THis goes for all forms, Main and sub forms. Especialy if you are creating referential integrity rules within the Relationships Window, you want the user to stay focused on the current record as they use the TAB and ENTER keys. Disabling "Navigation Buttons" is not enough.

For me, I do not bother with the Relationships Window, ever. I keep track of relations in my Queries that I save as Objects and also in notes I keep that are outside of Access. I maintain referential Integrity by using VBA, much like you are doing here by deleting a record in a subform. There is a link and you know yourself that the linked record needs to be edited. If you really want to take it to the next level, you can add another column to your tables and call it Archive. It can be a Yes/No type field and you can use it as an alternitave to "Delete" THen, in your queries, you can decide wether or not to display the Archived/Deleted records.

[ItsMe]

There was a lot of code that caused me to go around in circles. I ended up creating a new DB and imported the objects needed to fix the close button. I also commented out code that was referencing a query. There is no need for the query as far as I can tell. All of your form's and comboboxes are using SQL in their properties. So I just changed some properties in your subforms combo and commented out all of the subform code.

Now the RowSource for your Combo5 is SELECT tbl_destination.Destination_ID, tbl_destination.Destination_Name FROM tbl_destination;

I simply adjusted the bound column and column widths to hide the additional field/column and selected the combos' Control Source to match the field you want its bound column to populate. In this case, Control Source = Destination_ID. Eleminates the need for the Saved Query Object.

Rather than use the Docmd, I simply went after the PK's for the records and used DAO to delete. There were too many conditions and loops set by your code. It was too confusing for me to contend with. The Docmd may work now that a lot of the VBA and query and stuff is out of the way.

Anyway, try to keep ypur combos clean by using the property windows. You can base their RowSource off of a saved query, but use the bound column and the Control Source to update fields with the proper data. Also, you do not need to use Docmd gotorecord acnew. You can hava a form act as a Data entry form. When it opens it will go to a new record. Under the Data tab you can adjust the Form's Data Entry property to = Yes.

[mike_980]

works perfectly! Thanks so much for the help i'd never have got it working by myself! And thanks for the tips, as you can tell I am a novice with any kind of code and access but i'm slowly learning! Some of the code you used i'm not familiar with but I will look it up to find how it works.

Once again thanks alot, big help!

[ItsMe]

I kept having difficulties with the Docmd. Not sure why, so I just went with DAO. I do not have a lot of experience deleting records. It is something I disable as a habit and use an archive method instead. So when I do delete records, it is on a schedule where the database cleans up, looking for Orphans and very old Archive records.

As you look at the code, note that I abandoned the If Then for Dirty. You could place the code I offered in the form's Before Update event handler. This would fire any time the user decided to move to a new record or close the form, etc. It is just that this can get tricky with the subform. I would guess that you may have instances where the subform may have many records relative to the Main form link. So, maybe there is something you want to add the the subform's before update event.

Going forward, keep an eye on your comboboxes. Like I mentioned. You are using SQL to define them so finish up using the other properties I mentioned. You can also manage these properties via VBA.