Automatic deletion of subform's record

[Lars Brownies]

Form A contains table A (main table). Subform B contains table B, the
many-side of the relationship between table A and B.

When user start entering data into a table B's record, some fields in this
record automatically get filled, for instance the user name. I want to make
sure that a certain field is always filled, and if not I want to give them
the choice to either fill it in or delete the whole record. However, it's
not possible to delete the record within the before_update event. I don't
want to make this field a required field.

Any hints on how I can delete the current record (if certain field is
empty), before they leave the subform?

Thanks,

Lars

 

[John W. Vinson]

Form A contains table A (main table). Subform B contains table B, the
many-side of the relationship between table A and B.

When user start entering data into a table B's record, some fields in this
record automatically get filled, for instance the user name. I want to make
sure that a certain field is always filled, and if not I want to give them
the choice to either fill it in or delete the whole record. However, it's
not possible to delete the record within the before_update event. I don't
want to make this field a required field.

Any hints on how I can delete the current record (if certain field is
empty), before they leave the subform?

Thanks,

Lars

You can't *delete* the record in the subform's before update event, because -
*before* the update - there is no record in the table to be deleted!

But you don't need to do so. You can instead just set the Cancel argument to
True, and the record won't be saved to the disk at all.

 

[Lars Brownies]

But you don't need to do so. You can instead just set the Cancel argument

to
True, and the record won't be saved to the disk at all.

Thanks. But with Cancel they are forced to enter a value(, before they can
delete the record).

If they leave the field blank I would like to popup a msgbox like "You left
field X blank. Do you want to cancel the whole record? Would this be
possible, perhaps with some other event?

Lars

 

[John W. Vinson]

Thanks. But with Cancel they are forced to enter a value(, before they can
delete the record).

Are you doing this in the *form's* beforeupdate event (which should be
correct) or the *control's*? Again, there is no record to delete before it's
been saved to disk! What is it that you're trying to "delete"? What's the
current code, and what's the context?

 

[Douglas J. Steele]

Are you doing this in the *form's* beforeupdate event (which should be
correct) or the *control's*? Again, there is no record to delete before
it's
been saved to disk! What is it that you're trying to "delete"? What's the
current code, and what's the context?

Might he have to do an Undo to erase whatever's been entered into the form,
John?

 

[John W. Vinson]

Might he have to do an Undo to erase whatever's been entered into the form,
John?

Thanks, Douglas... I suspect that's where I'm misunderstanding!

If so just a line

Me.Undo

will erase the user's input.

 

[Lars Brownies]

Thanks John, Douglas,

Yes, the code is in the form's before_update. The underlying table consists
of 4 fields, of which 2 get entered automatically (username and date) and 2
fields are entered manually. One of those fields should not be blank.

I tried out Douglas 'undo' tip and that works good.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Description) Then
If MsgBox("Field Description is empty. Do you want to cancel the whole
record?", vbYesNo) = vbYes Then
Me.Undo
End If
Cancel = True
End If
End Sub

The only problem still remains when a user empties the Description field of
a record that already exists. Then the record will remain there. It would be
nice if I could 'cancel' that record in that case too.

Lars

 

[John W. Vinson]

The only problem still remains when a user empties the Description field of
a record that already exists. Then the record will remain there. It would be
nice if I could 'cancel' that record in that case too.

Can do. If they "empty" it it's replacing it with a zero length string, which
is not the same as a NULL. Try replacing

If IsNull(Me.Description) Then


with

If Len(Me.Description & vbNullString)=0 Then


to catch both possibilities.

 

[Lars Brownies]

John,

The 'IsNull(Me.Description)' does get fired when they empty the Description
field manually.

It's not quite solved yet. I now have the following code:

If Len(Me.Description & vbNullString) = 0 Then
If Me.NewRecord Then
If MsgBox("Field Description is empty. Do you like to cancel the whole
record?", vbYesNo) = vbYes Then
Me.Undo
End If
Else
If MsgBox("Field Description is empty. Do you like to delete the whole
record?.", vbYesNo) = vbYes Then
Me.Username = Null
Me.DateEntry = Null
Me.Category = Null
End If
End If
Cancel = True
End If

When the user empties the field Description of an existing record, I thought
I'd empty the other fields as well, but obviously the record remains there
since it's not a new record. So the user will have to fill the Description
field again before he can manually delete it. Since the record can't be
deleted in the before_update event, I'm still wondering if there is an event
that gives the opportunity to delete the record programmatically.

Lars

 

[Lars Brownies]

After thinking some more about it, I think an acceptable interface in this
case is the following.

If Len(Me.Description & vbNullString) = 0 Then
If Me.NewRecord Then
If MsgBox("Field Description can't be empty." & vbCrlf & _
"Do you want to cancel the whole record?", vbYesNo) = vbYes
Then
Me.Undo
End If
Else
MsgBox("Field Description can't be empty." & vbCrlf & _
"If there's no info available for this field, you have to delete
this record.")
Me.Undo
End If
Cancel = True
End If

Lars

John,

The 'IsNull(Me.Description)' does get fired when they empty the
Description field manually.

It's not quite solved yet. I now have the following code:

If Len(Me.Description & vbNullString) = 0 Then
If Me.NewRecord Then
If MsgBox("Field Description is empty. Do you like to cancel the whole
record?", vbYesNo) = vbYes Then
Me.Undo
End If
Else
If MsgBox("Field Description is empty. Do you like to delete the whole
record?.", vbYesNo) = vbYes Then
Me.Username = Null
Me.DateEntry = Null
Me.Category = Null
End If
End If
Cancel = True
End If

When the user empties the field Description of an existing record, I
thought I'd empty the other fields as well, but obviously the record
remains there since it's not a new record. So the user will have to fill
the Description field again before he can manually delete it. Since the
record can't be deleted in the before_update event, I'm still wondering if
there is an event that gives the opportunity to delete the record
programmatically.

Lars

 

[Eddie Gene Clemmons]

I want to get rid of all of u

 

[Danaë Geens]

hello i spreak dutch !!

 

[Larry Linson]

I want to get rid of all of u

One very simple way to not be bothered with "all of us" would be to not read
this newsgroup.

It's for technical discussion of, and questions and answers about, Microsoft
Access database software; if you are expecting something else, it's easy to
understand why you wouldn't be satisfied with it.

Larry Linson
Microsoft Office Access MVP