How to delete a record in a form?

[lawdy]

The next paragraph has more info. The short of it is "How do I delete a record in form view"? I have the form open and I need to delete that record.



From my 'invoice form', I push a button which adds a new invoice with nothing but the next invoice number on it. Then I add the date and a 'proposal number'. From the proposal number, it looks up the info and completely fills in the invoice form (and table). Now, if the proposal number does not exist in the proposal table or it is determined that the job has allready be billed, I need to cancell this invoice. That is the problem. With code, I can delete the record in the invoice table. Then it displays #Deleted in all the fields. If I blank out the fields on the forms, it will not let me move off that record due to a key vialation.

[JeroenMioch]

This is because you allready add the invoice number.
In that case access makes a new record.
When you delete the data in that record rather then deleting the record itself. I will notice you that the data has been removed from the fields (hence the "deleted" notification")

The solution is that when you dont find any data from the proposal number you just let the data in the form and then delete that (near empty) record programaticly upon exiting the form.
I do the same with one of my applications. The code checks that certain fields are empty. If so, it deletes that record.

[lawdy]

Ok, how do I delete that near empty record?

[Missinglinq]

To get rid of the #Deleted just Requery the Form, using

Me.Requery

Linq ;0)>

[JeroenMioch]

Yes that is a solution to that problem, but to get rid of records that are not soppose to be there use the code below.

First select your forms propperties (little square left top of the forms screen) and right click it.
Now select "on exit" en select "event procedure" from the drop down menu.

Paste this code (adjust the names of the fields and table to your data)

If Me.Dirty Then
Me.Dirty = False
End If

DoCmd.SetWarnings False

Dim strSQL As String
strSQL = "DELETE * FROM YourTableHere WHERE (((YourTableHere.YourControlName) Is Null))"
DoCmd.RunSQL strSQL

What this does is : delete all records that meet there where criteria.
In my case i have a parking management system, and without the license plate number i consider it no record. Thus when my database closes it deletes all records that have no licenseplate numers in my table.

Off couse the users of my database cannot complete the permit without this data. So i know that any record deleted is actually no record at all, just empty space.
But there are circumstances that users still manage to get a "blanc" record in. To ensure clean data i made the above code.

Maybe your problem is solved with missinglinq's code, but if it isnt, try mine

[lawdy]

There is no query.

[lawdy]

That code worked very well. Thank you.

[JeroenMioch]

Your very welcome.
Good luck with your database