Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Looking for records containing no data

    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
    Kevin

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for records containing no data

    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.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Looking for records containing no data

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

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Stuttgart, Germany
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for records containing no data

    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.

    <pre>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
    </pre>

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •