Black record with PK

[Tupacmoche]

Hi Table Masters,

I have a form connected to back end SQL 2008 R2. The PK is call GiftID and as expected is automatically inserted into the table/row when a record is being added. The problem is that if the user decides not to add a new row, I get a record with a PK that is blank. How can, I programmatically prevent such a blank record from being created?

Best

 

[1268]

Not sure you can if the form is bound to an autonumber table.

I tend to not bind my forms and just query the form data into my tables on update.
You could run a query on update to append non blank to a final table.

I am sure someone here will have a better answer.

Sent from my SM-G950U using Tapatalk

 

[Minty]

If the PK is set to be an identity field I'm pretty sure it can't be null.

Are you sure it's set up correctly? The Autonumber(Identity Field) isn't allocated until the record is saved when you are using SQL Server, so what you are describing sounds a little unlikely.

 

[Tupacmoche]

Hi Minty,

What you said about the PK is absolutely correct it is an identity field and is never null. But, that is the issue meaning that if a user decides not to save a record the PK is the only thing saved with a black row. The main form has a lookup table that fills in Donor information if it exists. But, it gives the user the option to enter the Donor information even if a Donor id does not exist. This field can be entered later since it can be null. But, what is happening is that if the user does not enter anything into the record and moves to the next row it is saved with nothing but a PK.

 

[Minty]

Ah sorry I slightly mis-read your post.
On the before Update event check that the user wants to save the record by validating the fields that should be filled in. If not then don't let them save it, or return them to the record to finish it off.

If they say no then Undo and Cancel the record and it shouldn't create the new PK ID.

 

[gemma-the-husky]

However, bear in mind that the autonumber will still get "lost". Try it manually, and you will see. You can't use an autonumber field to manage a truly sequential ID number.

 

[Tieval]

The simplest approach of all is to make one of the other fields in the table a required field. When it tries to save the record you will just get a message box saying it can't save it.