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

 

[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.

 

[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.