Deleting incomplete records in creation form?

[IncidentalProgrammer]

Hi, all!



In my database, I have a series of creation forms; for this example, I'll use the "Create Client" form. The form is meant purely to create the initial client record, with parts spread over 3 tables; full detail and view/edit is through a separate "Client Profile" form. Most of the information for the Client is saved in tblEntity (which also houses accounts, insurance companies, and producers/agents), tblClient (which houses Client-specificy type data), and tblContact (for all the contact info). tblEntity and tblClient go through this main form, and tblContact through a subform on the Create Client form.

The first thing a user does is enter the Client's name, then click a button that generates the PK for the record, and saves it across the tables. The problem I'm running into is if a user backs out of the form, I end up with incomplete entries, and sometimes duplicates.

I've done some reading, and running a cascading delete query across the tables for entries with a PK matching the one on the form seems promising, but the article warns to make a backup before using it, so it makes me question whether that is the best option (my users can't make backups!). Is there a better way to do this? I just want to make sure that the users can back out of this form and erase the record they started.

Thanks!

[June7]

Are you using UNBOUND forms?

I don't see any reason not to use. Just don't allow users to select and delete records.

Research BeginTrans, CommitTrans, Rollback methods.

There should be backups of the db somewhere. I rely on the network server routine backup.

[Torinjr]

Hi, Im creating my first Database and I had the same problem. I'm not sure if how I did it will help you but this is what I did using the Macro Builder.

On my form I had a 'Save' button and a 'Back' Button. If the user starts to fill in the form and has left any fields blank, then neither the Save or Back button will work because of the macros I have attached to those buttons.

1) Backing out of the record. I added an On-click event to the Back button. I then added the Macro 'If' and typed in the function IsNull([ID]). If that criteria was met and the field was Null I added the command 'CloseWindow', which would take the user back to the previous Form. I then added an 'Else' for if the field was not Null (ie the user had started to fill in the form) and I added a MessageBox, "The item has not been Saved" followed by the command 'UndoRecord'.

2) Saving the Records. I added an On-click event to the button. I then added the Macro 'If' and typed in the function IsNull([textboxName]). If that criteria was met and the field was Null I addeda 'Message Box', 'This field is blank, please Add' and then added the command 'StopAllMacros'. You can then add 'ElseIf' and repeat for all the fields in the Forms. Add an 'Else', 'GoToRecord' to save the record and go to next one, which will only work if none of the fields were Null.

I know that this solution has some flaws in it, but this is what has worked for me. I am more accustomed to use the Macro Builder than VBA so was easier for me to pick up. Also if the user closes the tab with a partially filled record then it will unfortunately still save the record (I worked around this by disabling users to view/close the tabs). Hope this helped.

[JeroenMioch]

In my application i have certain fields that have to be filled or else i consider it as no record.
If so i delete te record(s) programmaticly upon exiting the form with the custom exit button. (there is no way to exit my app any other way).
In my next example i delete the record when two key fields are left blank.

Dim strSQL As String
'Delete not completed records without a permit from the system
strSQL = "DELETE * FROM Parkingmanagement WHERE (((Parkingmanagement.permit) Is Null))"
DoCmd.RunSQL strSQL


Dim strSQL1 As String
'Delete not completed records without a licenseplate number from the system
strSQL1 = "DELETE * FROM Parkingmanagement WHERE (((Parkingmanagement.LicensePlate) Is Null))"
DoCmd.RunSQL strSQL1