[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 67
  • Last Modified:

Exiting a Form/Subform does not work correctly

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
mpim
Asked:
mpim
  • 4
  • 4
1 Solution
 
nathanielSystem Applications DeveloperCommented:
it would help us determine if you post at least the first error msg..
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
mpimAuthor Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
mpimAuthor Commented:
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
 
mpimAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
mpimAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now