Cleanup Blank Records?

[misscrf]

I have a normalized database (SQL) and Access for UI (forms/data entry). When I add a new sub-record, ie a form - subform record, it starts another record, and then there is an extra new record. It seems that if a user starts a new record, but then abandons it, a record is created in the table, with the ID (identity) and the FK to the main record. None of the other fields have any info.

Has anyone experienced this? I was thinking of a routine to check the tables for records that only have a PK and FK and no other info in any other field, and delete them. I'd really prefer to do it in a way that will truncate the identity, so that the unused ID number will be used again, once there is a real record. That is not imperative. My main concern is to clean up bunk records.

Thanks for any advice on this! (Please don't be mean to me).

 

[Ranman256]

once you start a new record , its there.
My users do this too and dont finish the record.
you can write a cleanup query
delete * from table where [field] is null.

put it in the autoexec macro, and it will clean up when user opens the app.

 

[misscrf]

Thank you for the response. Wouldn't the DELETE * FROM table WHERE NZ([field],0) = 0

need to loop to check all fields that are not the pk and fk? That's where I'm unsure of how to structure the routine.

Also, what is the autoexec macro? Sorry, I don't really use macros, I write everything in vba modules, so I'm not familiar with that. I do have a main menu that loads on open of the application. I could have the onload fire the routine, whatever it is. My concern is I have 41 tables, and this application is going to get bigger. I can classify my tables in terms of ones that just have an ID, vs ones that have an ID and an FKIR (consistent foreign key to central table ID).

Thanks!

 

[misscrf]

I'm wondering if I should be making a SQL job instead, now that I'm thinking about it. That way, I can run it nightly, when people aren't using it, and the performance hit won't impact the end user.

 

[The_Doc_Man]

There is merit to running an SQL job at odd hours for maintenance. Just debug the heck out of it before you unleash it on the world, but otherwise, I say go for it.

 

[misscrf]

Defintely!

 

[Mark_]

Or put validation code into BeforeUpdate for the child form and set Cancel=True if they didn't actually put anything in?

 

[MarkK]

There is merit to running an SQL job at odd hours for maintenance. Just debug the heck out of it before you unleash it on the world, but otherwise, I say go for it.

Doc_Man, if there was such a contest, I think this post would win as your shortest EVER.
Couldn't help but comment on it, and hope you have a great day!
Mark

 

[Pat Hartman]

The better solution is to not create the bad records to begin with. There are several steps to take.
1. In the table definition, make sure that all required fields are defined as required. Remove the 0 default for numeric fields and set the AllowZLS property for text fields to No. Of course if you have any defaults, you can set them.
2. Learn how to control the form's events. The Form's BeforeUpdate event is the most important form level event. This is the event where you do your validation making sure that required data is present and values are valid. If there are errors found, THIS is the event that allows you to prevent the bad record from being saved. Here is an example of validation code. As an error is found, a message is displayed, focus is set to the control I want the user to address, the save event is cancelled and the sub is existed. NOTHING gets past the Form's BeforeUpdate event. Think of it as the flap at the bottom of a funnel. You decide in this event, what falls through to get saved and what doesn't. You have absolute control over what gets saved. You simply need to understand how to control it.

If Me.Status & "" = "" Then
    Msgbox "Status is required.",vbOKOnly
    Me.Status.SetFocus
    Cancel = True
    Exit Sub
End If
If IsDate(Me.StartDate) Then
    If Me.StartDate >= Date() Then
    Else
        Msgbox "Start Date must be >= Today.",vbOKOnly
        Me.StartDate.SetFocus
        Cancel = True
        Exit Sub
    End If
Else
    Msgbox "Start Date is required.", vbOKOnly
    Me.StartDate.SetFocus
    Cancel = True
    Exit Sub
End If

3. Make sure that you do not have any code that is dirtying the record before the user does.
4. NEVER put a band-aid on a problem. TIX the problem. The delete query is a band-aid. It doesn't fix the problem. It simply removes a symptom.

 

[misscrf]

Thanks for the reply, Pat. The thing is, I don't want to alert the user. They don't need to be told to go back. Another issue is that the table field rules (required etc) can't throw errors, that are SQL errors. I would want to trap those and control that I just back out the record, instead of alerting the user. Problem is that Access is not always good at intercepting SQL messages.

You bring up some good points and I will research and test those, to see if they can help.

 

[Pat Hartman]

It doesn't matter what the BE is. There is proper technique and there is improper technique. You should NEVER, EVER allow bad data to be saved in the first place. You can ALWAYS prevent it by proper use of the Form's BeforeUpdate event.

 

[The_Doc_Man]

Pat's suggestion has considerable merit.

The reason the new record is created is because of the sub-form which (presumably) links to a child table. If you have relational integrity enabled, you CANNOT create a child record until the parent exists... and in this case I believe Access makes the parent for you to save you the headache of the OTHER possible situation - trying to create a child with no parent is ALSO going to cause an error.

 

[Mark_]

Additionally, as this has recently come up in another thread, you should be doing something to prevent a user from going in to start a child record before the parent is completed. BeforeUpdate on the Parent should catch this type of activity and prompt the user to finish what they started BEFORE going off to make children.