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.

 

[Pat Hartman]

YOU are in complete control over whether or not a record gets saved. You just have to add validation code to the correct event which as Minty suggested is 99.9% going to be the Form's BeforeUpdate event. If fields are missing or invalid, you cancel the update. Here's a code example:

If Me.LastName & "" = "" Then
    Msgbox "Last name is required.",vbOKOnly
    Me.LastName.SetFocus
    Cancel = True
    Exit Sub
End If
If Me.FirstName & "" = "" Then
    Msgbox "First name is required.",vbOKOnly
    Me.FirstName.SetFocus
    Cancel = True
    Exist Sub
End If
....

 

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

 

[Pat Hartman]

The question isn't about gaps in the numbers. It is about empty records getting added which is why we are all emphasizing the correct use of the BeforeUpdate event which prevents bad data from being added.

I get a record with a PK that is blank. How can, I programmatically prevent such a blank record from being created?

 

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

 

[Pat Hartman]

Making a single field required may stop accidental bad saves but actually validating data is the best solution. When you build applications for others, you have a fiduciary responsibility to keep their data accurately. Allowing random data to be saved doesn't do anything to forward that goal.