Exiting a Form/Subform does not work correctly

Posted on 2016-11-14
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4

Expert Comment

ID: 41887148
it would help us determine if you post at least the first error msg..
LVL 85
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....
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 85
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.

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 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 85
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

765 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