Exiting a Form/Subform does not work correctly

Posted on 2016-11-14
Last Modified: 2016-11-15
If the user leaves a form/subform and does not want to save any changes made, I use the following code to exit the form/subform (triggered by an event in a button on the mainform):

Private Sub btnExitNoSave_Click()
Dim xSQL, xInvoiceNo As Variant

  If Forms!CreateNewInvoices.Form.Dirty = False Then
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SetWarnings False
    xInvoiceNo = Nz(Forms!CreateNewInvoices!InvoiceItems!INVOICENO, 0)
    DoCmd.SetWarnings True
  End If
End Sub

If I executed this code and the user has changed the subform (only then the error occurs) then the program stops and jumps somewhere within the subform and displays a random error message. I do not use any relationship on these two tables and I'm at a loss what is happening. Thank you so much for any help... Cheers Michael
Question by:mpim
  • 4
  • 4

Expert Comment

ID: 41887148
it would help us determine if you post at least the first error msg..
LVL 84
ID: 41887706
Assuming you're running this on CreateNewInvoices:

If Me.Dirty Then
End If

Author Comment

ID: 41887797
Sorry, the error message I get is "The data has been changed - Runtime error -2147352567 (800200009) but I might also get Runtime error 3075 - Syntax error (missing operator) in query expression "Inovoiceitem=' or any other messages....
LVL 84
ID: 41887801
Sorry, my bad on this one:

You cannot Delete items like this from a Bound Form, since Access "locks" that record when you are on it. With bound forms you must use the builtin processes to delete, like DoCmd.RunCommand acCmdDeleteRecord (instead of your Delete queries). With a Master/Child form, you'd also have to introduce methods for the Child form (since you're doing this from the Parent, it would seem).

A better solution, if you want to give users the ability to delete, is to use temporary tables that hold your data until such time as you wish to commit that data. Essentially you'd mimic your Invoice_Header and Invoice_Detail tables, and use those when binding your forms. If the user selects to Save the data, you run Append or Update queries to move the data from your temporary tables into the live tables. If the user doesn't save, you just empty the tables in preparation for the next use.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 41887807
Scott, thanks for the help but if I use DBExecute as per your example I now get the following error message:

Runtime error 3329 - Record in table 'InvoiceSummary' was deleted by another user... I then changed the second line to:


Now the execution stops saying that there is a syntax error and it breaks and a completely different place somewhere in the subform where i also use the following statements:

  DoCmd.RunSQL xSQL

No clue what is happening... by the way what is the difference between DoCmd.RunSql and CurrentDB.Execute? Thank you so much for any help... Rg Michael

Author Comment

ID: 41887811
Scott thank you so much  ... sorry to bother you is there any small example you could provide me with for your second option (the use of temporary tables) as I'm not sure how to use it? Thank you so much!"
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 41887840
You create a temporary table (or tables) based on your "live" tables.

Bind your forms to those tables.

When the user clicks Save, you execute Append or Update queries. If your tables are IDENTICAL, then you can do this:

Dim sql As String = "INSERT INTO YourLiveInvoiceTable SELECT * FROM YourTemporaryInvoiceTable"
Currentdb.Execute sql
sql = "INSERT INTO YourLiveInvoiceDetailTable SELECT * FROM YourTemporaryInvoiceDetailTable"
Currentdb.Execute sql

Of course, you may need to get the value from an AutoNumber field, if you're using that to relate your Details table back to the Header table. If so, you can do something like this:

'/ get the values in the Temp Invoice Table:
Dim rstTemp As DAO.Recordset
Set rstTemp = Currentdb.Openrecordset("SELECT * FROM YourTemporaryInvoiceTable")
'/ Open an empty recordset for the Live Invoice Table:
Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM YourLiveInvoiceTable WHERE 1=0")
'/ Add a new record:
Dim fld As DAO.Field
'/ Loop through the Fields in the Live recordset, and get the value of the corresponding field in the Temp recordset
For each fld in rst.Fields
  rst.Fields(fld.Name) = rstTemp.Fields(fld.Name)
'/ Get the AutoNumber field:
Dim InvoiceID as Double
InvoiceID = rst.Fields("YourIDField")
'/ Save the Header record:

'/ now do the subform records:
rstTemp = Currentdb.OpenRecordset("SELECT * FROM YourTemporaryInvoiceDetailTable")
'/ open an empty Recordset to the Live Detail Table:
Set rst = Currentdb.OpenRecordset("SELECT * FROM YourLiveDetailTable WHERE 1=0")
'/ Loop through the records in the Temporary Detail table:
Do Until rstEmp.EOF
  '/ Add a new record to the LIve Detail Table:
  For each fld in rst.Fields
    '/ Add data to live from corresponding Temp, but ONLY if it is NOT the ForeignKeyField:
    If fld.Name <> "YourForeignKeyField" then
      rst.Fields(fld.Name) = rstTemp.Fields(fld.Name)
      rst.Fields(fld.Name) = InvoiceID
    End If
  '/ move to the next Temp Detail Record

The above is all air-code, and so may have some syntax errors. You would obviously have to change any Table, Field or Control Names to match your own.

Author Comment

ID: 41887915
Scott - thank you so much for your help. Just one more question. Your solution works great if you want to add invoices but what happens if you want to change an invoice? Do I first copy the existing record to the temporary files? Perhaps an easier solution would be rather than deleting the records in case they don't want to go ahead with the change is to set a cancel flag to true to both tables entries (invoicesummary and invoicedetails). I tried using the RunSql command to do the update but get the same error. Would it be better and workable if i use the db.execute rather than the RunSql - (what is the difference)? Thank you!
LVL 84
ID: 41888107
You'd do much the same process, except you'd use UPDATE queries instead of INSERT queries. When the user wants to Edit an existing Invoice, you'd copy the Invoice and InvoiceDetail items to the relevant Temporary tables, and you'd modify your code to check for the existence of the Invoice prior to running the INSERT statements. That could be as simple as checking whether the InvoiceHeaderTemporaryTable actually contains a value in the Invoice field (since you would not have a value in that field if you're adding a New Invoice), or using SQL or the DCOUNT function to query the InvoiceHeader table (if you "issue" the Invoice number before saving).

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now