Solved

Exiting a Form/Subform does not work correctly

Posted on 2016-11-14
9
17 Views
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.RunSQL "DELETE FROM INVOICEITEMS WHERE INVOICENO=" & xInvoiceNo
    DoCmd.RunSQL "DELETE FROM INVOICESUMMARY WHERE INVOICENO=" & xInvoiceNo
    DoCmd.SetWarnings True
  End If
  Me.Undo
  DoCmd.Close
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
0
Comment
Question by:mpim
  • 4
  • 4
9 Comments
 
LVL 6

Expert Comment

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

If Me.Dirty Then
  Currentdb.Execute "DELETE FROM INVOICEITEMS WHERE INVOICENO=" & Me.InvoiceNo
  Currentdb.Execute "DELETE FROM INVOICESUMMARY WHERE INVOICENO=" & Me.InvoiceNo
End If
0
 

Author Comment

by:mpim
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....
0
 
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mpim
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:

CurrentDb.Execute "DELETE FROM INVOICESUMMARY WHERE INVOICENO=" & xInvoiceNo

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:

  xSQL = "DELETE FROM INVOICEITEMS WHERE INVOICEITEM=" & Nz(Me.INVOICEITEM.Value, 0)
  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
0
 

Author Comment

by:mpim
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!"
0
 
LVL 84

Accepted Solution

by:
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:
rst.AddNew
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)
Next
'/ Get the AutoNumber field:
Dim InvoiceID as Double
InvoiceID = rst.Fields("YourIDField")
'/ Save the Header record:
rst.Update

'/ now do the subform records:
rstTemp.Close
rstTemp = Currentdb.OpenRecordset("SELECT * FROM YourTemporaryInvoiceDetailTable")
'/ open an empty Recordset to the Live Detail Table:
rst.close
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:
  rst.AddNew  
  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)
    Else
      rst.Fields(fld.Name) = InvoiceID
    End If
  Next
  rst.Update
  '/ move to the next Temp Detail Record
  rstEmp.MoveNext
Loop

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

Author Comment

by:mpim
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!
0
 
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).
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

746 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

11 Experts available now in Live!

Get 1:1 Help Now