Looking for records containing no data

Topic

New Reply

Hi, looking to find a way to search a table from the on close event for a form which determines if there are any records in the table which do not contain data. For example, I have a form which addresses are input to a table. In some circumstances the address is deleted in the form from the user by deleting the information in the text boxes (i.e. highlight text box hit delete key). In these instances the record still exists in the table but there is no data left in it. The following line of code is what I am trying to get to work but cannot seem to make it happen since I know this table contains such records.

CODE:
If IsNull(DLookup(“[First Name]”, “Affirmative Action Registration”, “[First Name]”)) Then
MsgBox “You have deleted information during this session which created an empty record. Do you wish to delete this non essential record?”, vbYesNo, “Empty record Notification”
End If
END CODE

I have tried this with ISNULL; ISEMPTY; and ” “.
Also all fields in the table are text fields.

Any help is appreciated.
Thanks
Kevin

Reply | Quote

Replies

If you’re determined to use that kind of a process, you could run a select query that looks for records with specified fields that are null, and report that number of records to the user, then run a delete query which expunges them from the table.

Or, you might consider a design change in the form itself which warns the user that they have deleted useful data, say, in the OnCurrent event, and react to user choices there instead.

Reply | Quote

Or better yet, put at least one required field in the table so that you can’t save an empty record.

Reply | Quote

Kevin,

Another idea:
Check those fields in the form’s BeforeUpdate event proc. If they are empty, set Cancel to false. This prevents the record to be saved with empty fields (and left), no matter what the user is trying to do.

If IsNull(Me![First Name]) Then
   MsgBox "You have deleted the first name during this session. The record cannot be saved without this.", _
   vbOKOnly, "Empty Field Notification"
   Cancel=True
End If

I almost always do so. I have very few mandatory fields in the tables. I tried once to trap the error in the form’s error proc, but the original engine error message kept popping around, next to my own error. But I admit it’s some time since and I may have made something wrong.

The incomplete records, if it’s not very much, can be filtered with Filter by Form and then completed or deleted. Or you can delete them with an SQL delete statement in the database window.

Reply | Quote