Delete record when null

[acarter1]

Hi all,
I have a form which uses a subform.
The main form finds a doctors record eg name, assignmentno (prim key), and the hospital they work at. The subform shows their sick record, when i enter a value into the "daysleave" field on the subform and then delete it and go to go off the the subform, it leaves the new record there and pops up saying you cannot enter a null value in this field as it is a required field, i know that i have set this to be required, this also happens with the other required fields on the subform.

Can i put code on the lose focus event of this field so that, if all field values on the subform new record are null then delete the subform new record so this message doesnt keep popping up.

Thanks in advance!!

 

[Zaeed]

in the before update event for the subform have some code that checks the isnull property for the fields..

IsNull(me.textbox1) And isnull(me.textbox2)

then if this is true, run a sql delete command

 

[acarter1]

Hi Zaeed, i have got an if statement on the isnull, my code looks like this:

Private Sub Days_Leave_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Days_Leave) = True And IsNull(Me.LeaveFrom) = True And IsNull(Me.LeaveTo) = True Then

End If
End Sub

This is how far i have got with this, is this correct also how do i write the sql delete command, i tried using DoCmd.Docommand acRecordDelete or similair, this did not work, help!!

Thanks for your reply!

 

[Zaeed]

the delete command is this

DoCmd.RunSQL("DELETE * FROM TableName WHERE TableName.ID = " & Me.IDField)

TableName is the table that your sick information is held..

ID is the PK for the above table.

 

[acarter1]

Sorry Zaeed, wont this delete all records this user has in this table? i only want to delete the one with the null values which shows on the form.....

Sorry!

 

[Zaeed]

nope.. You use the WHERE clause to specify which record..

I'm assuming you have a table that contains all the sick records. Each record should have its own unique ID value. This value should be in your subform, probably as a hidden text box. Then reference that in the WHERE clause... So what you're doing is deleting just one record..


Google SQL Delete

 

[acarter1]

Each sickness in my sick table is linked to the doctor by their unique id, but you may have more than one entry in the sickness table, you identify the sickness by looking at the doctors id and looking in the sickness table for the sickness entries with that ID, so if they are off sick on 2 seperate occasions then they would have 2 entries in the sickness table. the code you wrote deletes all records with the specified id doesnt it?

Also i tried this code in the before update event:

If IsNull(Me.Days_Leave) = True And IsNull(Me.LeaveFrom) = True And IsNull(Me.LeaveTo) = True Then
DoCmd.RunSQL ("DELETE * FROM tblLeave WHERE tblLeave.AssignmentNo = " & Me.AssignmentNo)
End If
End Sub

and it doesnt work.....if needed i can upload a sample of my database.

 

[Zaeed]

In your sickness table, are you using an ID that identifies each individual sickness record.. such as SicknessID or something similar? Thats what you should be using to delete a specific record..

Can you test the before update event with a message box.. just to make sure its working.. You may need to change it to Me.Days_Leave = "" instead of isnull if it isn't working..

 

[acarter1]

I dont have a id to identify each sickness individually, i only have 1 ID and that is the doctorid so to say. Also i have tested the before update, this only works when it actually goes to update ie. when all the fields have a value in, this code then will not work with the database....

If IsNull(Me.Days_Leave) = True And IsNull(Me.LeaveFrom) = True And IsNull(Me.LeaveTo) = True Then
DoCmd.RunSQL ("DELETE * FROM tblLeave WHERE tblLeave.AssignmentNo = " & Me.AssignmentNo)

so, i cant identify a single sickness, is there not a current record parameter? and i cant use the before update, could i not use on change or something like this? Help appreciated!
Thanks everyone!

 

[acarter1]

anyone able to help?

 

[Zaeed]

You should be adding a Primary Key for your sickness table to identify each sickness.. That way you can identify each individual one and delete it.

An alternate method to the beforeupdate method would be to have a delete button

 

[smig]

Zaeed I think you are wrong here, but I might be wrong either
there is no primery key to this record before it's inserted into the table.

now the big question - what do you want to do if the user missed some importent data (required):
do you want to let him know he missed the data and ask him to put it ?
do you want to close this form and delete the wrong record ?

I think you should let the user know he missed this data and put him back to the form.
to do this you must disable the close button of the form, if he close the form data is stored, and make your oun close button, that will check for the existance of the required fields.