This is Crazy! Autonumbering & Primary Key.... 2

[jguy]

Hello. I have a db with a table in it to collect job information. It has a primary key set up as "autonumber" for each record in the table.

I have a form that is used to enter data into this table. I have a combo box (lookup) that looks up values from a linked ODBC table, and then some more values are displayed on text boxes based on the combo box value (this data is pulled from the same ODBC) using the ME! command. All works great.

However, I have noticed, when a user picks a value from the combo box (and the other values are displayed in the text boxes) and decides to just cancel entering records (by clicking the X in on the form) there is a record saved in the db with a number(PK) and those "pulled" values and everything else is "null" or empty......this will drive one crazy, especially if I try to view all "open" jobs and there are some of these empty records!

My question is, how in the world do I get rid of these crazy records????? HELP!
Joe W. Guy
Network Admin
MIS Director

 

[clientuser]

The reason you are getting those records is (im assuming - man, i hate to do that!) because you are basing your form off of a table right?

Which means each time you close the form whether you put in all the information or not, Access will save whatever you have there for that record!

What I have done in the past and still do today, is create a form without associating to a table/query and when you click your save button or whatever to save the information, I use sql statements to save the recordsets to wherever you want. This eliminates the "phantom" record everytime you close out of the form.

I also tried a long time ago to do the saveall cmd thing in Access and it never worked properly when the form closed out. Doing it with the sql statements seems to work fine for me, its a little more work to write, but saves you from having those empty records in there!

 

[jguy]

Oh, gosh! I'm like finished with the DB! Man, that's a lot of work!? Is there not a more simple way to do it? Also, What SQL statements do you use? Would an UPDATE query work instead or APPEND??? Ahhhh, very confused now....



Joe W. Guy
Network Admin
MIS Director

 

[ekim]

Use the before update event to check for null values and and undo or close method on the form

 

[ppientini]

HI, try this.

remove the close box for the form to prevent close from here

add abutton to save the record changes
and add the folloving code
runcommand accmdsavrecord

add a button to close the form and add the following code
if me.dirty then me.undo


if the user push close anf there are changes they are simply discarded, but the user have to push "save" to confirm the insert or update

Bye

 

[jguy]

ppientini,
Dude! That did it! Thanks soooooooooooo much for the code! Joe W. Guy
Network Admin
MIS Director

 

[SimonDavis]

Just for some extra flavour . . .

If you're a real slob like me, you run the table through a filter, and put 'is not null' on one or two mandatory fields.

Just have to open the table from time to time and clean out the empties.

Can't recommend it though.

 

[hsitz]

I would second ppientini's recommendation.

If you do that, though, I think you're also going to have to disable the "Escape" key, since I believe a user can get the same effect from hitting that key as he/she would get from clicking on the "close box" in the upper right hand corner of the form.

The escape key can be disabled by turning on "key preview" in the events for the form, and then adding some code to the keypress or keydown event. the code would be something like this:

IF keycode = vbEscapeKey 'might be "vbEscKey", check
' keycodes help
then keycode = 0 ' change keycode to no key
end if


This code will make it so that the escape key is disabled for that form, but will not disturb the processing of any other keys.

(Alternatively, or I guess in addition to blanking out the escape key keycode, you could call a routine attached to one of the command buttons on the form, for example a routine called by a cmdCancelAddition button.) -- Herb

 

[MichaelRed]

Ther eare more ways to get into this than the 'average bear' will find in any reasonable manner. And some of hte work-a-rounds introduce additional issues.

If it is JUST the empty records which bother you, a way to 'fix' the problem is to include a query which deletes recordswith NULL in specific (required?) fields. As they say in Boston :Vote Early. Vote Often." - except here just "Run" instead of Vote!

You could 'run' this query from any number of places, such as the startup object.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over