Delete record only when fields are filled out

[jp3scr]

Hello,

I have a project tracking database. Project Managers update their projects on the table via a form. Once the project is closed I have a button on the form to delete the project from the "active project table".

I already have an append query that brings the record to the history table, so i'm not worried about deleting the record from the active project table.

I want to write VBA for the button into the "on click" event to only delete if certain fields equal a value, and if not spit out an error telling the Project Manager they need to fill out those fields before they can close the project.

Me.Proj_Status = "Closed"
Me.Proj_Phase = "Closure"
Me.Act_Finish_Date = any date(just needs to have a date)

Thoughts??

 

[pbaldy]

For starters, I wouldn't have two tables unless you're talking about a massive number of records (millions). The status field is enough to let you select active or closed projects as desired from a single table. That said, you can test those fields before deleting, using IsDate() to test the date field.

 

[jp3scr]

Well the combo box pulls from the 1st table of active projects. Anytime a change is made to the project to any field, a whole new record is created in the history table (the 2nd table) "trial audit". So when the project isn't active, I don't want that project to show up in the combo box, reason why I need to delete the record from the main table.
So I see the IsDate() listed above, makes sense but if you could elaborate it would be appreciated. I've been learning access on a fly and really haven't mastered VBA.

so for those three fields should it read....????

IsMe.Proj_Stauts "Closed"
IsMe.Proj_Phase "Closure"
IsMe.Act_Finish_Date()
Then
DoCmd.DeleteObject
else
"I need help writing the on error message box"

 

[pbaldy]

Your reason isn't one. You can simply change the combo to only pull records where the status field isn't "closed".