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....
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)

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.

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

749 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