Delete record if Textbox empty

[Derek Brown]

Hi all

This is the desperate result of an attempt to simply delete a bloody record
if the only text field on the form is empty.

The fields are Text1 (Text) and ID (AutoNumber)

Private Sub Text1_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
DBEngine.Idle dbRefreshCache
DoEvents
DoCmd.SetWarnings False
Me.Requery
Me.Refresh
If ID > 0 Then 'Test to see if record exists. if I try to delete a record
that has already been deleted I get an error
If Text1 = "" Or IsNull(Text1) = True Then

Recordset.Delete
Me.Requery
Me.Refresh

End If
End If
DoCmd.SetWarnings True
End Sub

It cannot be this hard to delete a record if Text1 is empty. Even with all
of this when i delete a record and then try to delete the first record i get
"No Current Record" ERROR I also stumbled on a phrase in a help screen
"depending if you are in immediate update mode" Is there such a thing? If
there is then it is not referred to again in any of the help files. This can
be a frustrating business.

 

[Allen Browne]

To prevent the form accepting records where the Text1 field is null:
1. Open the table in design view.

2. Select the Text1 field.

3. In the lower pane, set the Required property to Yes.

4. If it is a Text or Memo field, set the Allow Zero Length property to No.

No code needed: the record will not save in the form. If you started
entering a record and backspaced it out, just press the Esc key twice to
undo it.

If you having existing blanks in the table, you can delete them like this:
1. Create a query that uses this table.

2. Change it to a Delete query (Delete on Query menu.)

3. Drag the * from the table in to the grid.
Accept Delete under this field.

4. Drag the Text1 field into the grid.
Choose Where under this field.
In the Criteria row under this field, enter:
Is Null
If it is a Text or memo field, use:
Is Null OR ""

5. Run the query.

It would also be possible to use the BeforeUpdate event of the Form. Cancel
the event if the field is null. But the first solution above requires no
code at all.

 

[Derek Brown]

Sincere Thanks Allen

Do excuse my frustrated reply!

The main form is for a manager of 5 teams. The main form has 5 subforms each
lists members each team. the problem is that sometimes team members leave. I
am simply trying to remove the blank space when a team member leaves the
organisation. I see no reason why I should have to instruct a user to click
a record selector and press delete and then explain why a warning box pops
up with "You are about to delete a record" (which would frighten the c***
out of an inexperianced operator), when all I need is for the database to
remove the record if it's blank! God is this impossible?

As usual my situation is a little more complicated.
Your first solution is great unless you want to delete an entry, and the
second is great if I want to put a "Remove blank boxes" button on each of 5
subforms or run the query when the form opens but I would have to run 5
delete queries. I know it works because I've done what you suggested and it
works brilliantly. but why is it so difficult to just delete an empty
record?

 

[Derek Brown]

Hi Allen

OK so I've calmed down now.

I have used your sugestion to solve the problem with:

If Text1 = "" Or IsNull(Text1) = True Then
DoCmd.SetWarnings False
Me.Refresh
DoCmd.OpenQuery "Delete1", acNormal, acEdit
Me.Requery
DoCmd.SetWarnings True
End If

Would you believe it would not work properly without the Refresh command.

Thanks Allen. Still love to know how to delete a record without all this
fuss!