Primary Key in in Data enry Form

[stuchdk]

background:
I have created a series of data entry forms for use of data collection. Each has a table as a record source with the same primary key "survey_id" , which is a text field. this is selected as the primary key so that the data from the tables when queried can be linked on this unique identifier; it cannot be duplicated; and must be entered whenever a new record is entered.
Question:
in the access Form, when a field (record source) that is set as the primary key is left blank/null , how do you ensure receiving the system error or any error that it cannot be left null?
when you try to go to a new record, you receive the error; but if you click on the 'close' control that has been embedded, it closes without error.
any recommendations ?

thanks in advance.

[ADezii]

background:
I have created a series of data entry forms for use of data collection. Each has a table as a record source with the same primary key "survey_id" , which is a text field. this is selected as the primary key so that the data from the tables when queried can be linked on this unique identifier; it cannot be duplicated; and must be entered whenever a new record is entered.
Question:
in the access Form, when a field (record source) that is set as the primary key is left blank/null , how do you ensure receiving the system error or any error that it cannot be left null?
when you try to go to a new record, you receive the error; but if you click on the 'close' control that has been embedded, it closes without error.
any recommendations ?

thanks in advance.

I'm a little confused on your question. If you go to a New Record, then place values in 1 or more Fields without entering a value in the Primary Key, then click on the Close Button, you will receive the following Error: Index or Primary Key cannot contain a Null value.

[mshmyob]

I'm with Adezi... If you have a primary key you should always get the message.

I am thinking you have a line in your code that is something like this

On ERROR RESUME NEXT

This line will basically stop the message from being seen by your users. See if you have that in your code or macro.

That is all I can think of.

[missinglinq]

By "close control that has been embedded" he means a command button, probably one created by the Command Button Wizard, that uses the code

DoCmd.Close

to close the form. The problem he mentions is a bug that's been with Access forever. Using this code to close a form does, indeed, cause Access, when faced with missing PKs or other field validation failures, to simply dump the record without warning!

The workaround is to preface the command with code to force the record to be saved.

Replace the DoCmd.Close behind the button with

If Me.Dirty Then Me.Dirty = False
DoCmd.Close

Now the warning for a missing PK field as well as a missing required field will appear before closing the form.

I understand that Micro$oft sort of fixed the bug in ACC2007, by adding the line to force the save of the record, when the Wizard is used to create a “close” button. This, in turn, has created another problem (what a surprise!) If you use the Wizard to create a “close” button for an unbound form, which has no Dirty Property, the code bombs out!

Welcome to TheScripts!

Linq ;0)>

[ADezii]

By "close control that has been embedded" he means a command button, probably one created by the Command Button Wizard, that uses the code

DoCmd.Close

to close the form. The problem he mentions is a bug that's been with Access forever. Using this code to close a form does, indeed, cause Access, when faced with missing PKs or other field validation failures, to simply dump the record without warning!

The workaround is to preface the command with code to force the record to be saved.

Replace the DoCmd.Close behind the button with

If Me.Dirty Then Me.Dirty = False
DoCmd.Close

Now the warning for a missing PK field as well as a missing required field will appear before closing the form.

I understand that Micro$oft sort of fixed the bug in ACC2007, by adding the line to force the save of the record, when the Wizard is used to create a “close” button. This, in turn, has created another problem (what a surprise!) If you use the Wizard to create a “close” button for an unbound form, which has no Dirty Property, the code bombs out!

Welcome to TheScripts!

Linq ;0)>

Thanks for the info, linq.

[missinglinq]

Yeah. Only Micro$oft would use different underlying code for its native close button than it does for its Wizard generated close button! But then, only Micro$oft would correct one bug by creating another one!

Linq ;0)>

[mshmyob]

Good to know.

I do have Access 2007 and MS seems to have fixed the problem. You get the error if bound but no error when not bound. The 'dirty' line does not appear in the macro code just the close line and it seems to work. I guess they must have cleaned up the newer version.

By "close control that has been embedded" he means a command button, probably one created by the Command Button Wizard, that uses the code

DoCmd.Close

to close the form. The problem he mentions is a bug that's been with Access forever. Using this code to close a form does, indeed, cause Access, when faced with missing PKs or other field validation failures, to simply dump the record without warning!

The workaround is to preface the command with code to force the record to be saved.

Replace the DoCmd.Close behind the button with

If Me.Dirty Then Me.Dirty = False
DoCmd.Close

Now the warning for a missing PK field as well as a missing required field will appear before closing the form.

I understand that Micro$oft sort of fixed the bug in ACC2007, by adding the line to force the save of the record, when the Wizard is used to create a “close” button. This, in turn, has created another problem (what a surprise!) If you use the Wizard to create a “close” button for an unbound form, which has no Dirty Property, the code bombs out!

Welcome to TheScripts!

Linq ;0)>

[missinglinq]

So they "cured" the new bug by reverting to the old bug! At least all versions are consistent now! Be nice if they'd fix the orginal bug, after 4 versions!

Linq ;0)>

[stuchdk]

thank you! so because i used the wizard (yes i do not write in vb, not that savvy; i am statistical person) rather than writing the code, i do not receive the error when the form closes without the primary key because of a bug? sounds like microsoft...
i will use your recommendation.
thank you for the help and the welcome! (i am she)
-stuch

By "close control that has been embedded" he means a command button, probably one created by the Command Button Wizard, that uses the code

DoCmd.Close

to close the form. The problem he mentions is a bug that's been with Access forever. Using this code to close a form does, indeed, cause Access, when faced with missing PKs or other field validation failures, to simply dump the record without warning!

The workaround is to preface the command with code to force the record to be saved.

Replace the DoCmd.Close behind the button with

If Me.Dirty Then Me.Dirty = False
DoCmd.Close

Now the warning for a missing PK field as well as a missing required field will appear before closing the form.

I understand that Micro$oft sort of fixed the bug in ACC2007, by adding the line to force the save of the record, when the Wizard is used to create a “close” button. This, in turn, has created another problem (what a surprise!) If you use the Wizard to create a “close” button for an unbound form, which has no Dirty Property, the code bombs out!

Welcome to TheScripts!

Linq ;0)>