Trapping Null Value in Primary Key Field

[kermit5]

I would like to trap the error that arises if the user leaves the PK blank to replace the error message

Index or PK cannot contain a null value

with a more useful message to the user. There is no error number given.

How do I do this?

Scott

 

[ghudson]

In the design view of the table...

Type Is Not Null in the Validation Rule for your PK field and type your custom message in the Validation Text.

HTH

 

[kermit5]

This traps the error if I enter a value and then delete it but if I simply skip this field (Tab out of it) and enter a value in another control, when I attempt to advance to the next record I still get the standard error "Index or primary key cannot contain a null value."

How do I trap this?

 

[ghudson]

Try something like this in the OnLostFocus event of the text box...

If IsNull(TextBox1) or TextBox1 = "" Then
Msgbox "You can not leave the X field empty!"
TextBox1.setfocus
End If

HTH

 

[kermit5]

So that accomplished what I wanted to but created a new problem. When I am done entering all of my records, I can't do anything including close the form, navigate around the form etc. Maybe what I need it to do is have a msgbox that gives the user an option to cancel the erorr. I'm not sure how to code this. Here is my code right now:

If IsNull(txtMark) Then
MsgBox "Please enter a valid door mark.", vbCritical + vbOKOnly, "Missing Door Mark"
Me.txtFocus.SetFocus
Me.txtMark.SetFocus
End If


I suspect I will need to change my vbOKOnly to vbOkCancel but I'm not sure how to code the event if the cancel button is clicked to allow the user to leave the txtMark control without entering a value.

 

[ghudson]

Not sure what is going on. Can you post a sample Access 97 db of your form and record source. Delete sensitive data if needed and I will look at it. What happens if you correctly enter the data the first time and try to save it or exit the form?

The VBCancel will not work the way you are thinking. Also, the first setfocus is meaningless with the second one following. A field is no longer Null if the value was deleted (pressing backspace or delete key). IsNull would not catch that.

If IsNull(txtMark) Or txtMark = "" Then 'txtMark = "" traps user when deleting cell value
MsgBox "Please enter a valid door mark.", vbCritical + vbOKOnly, "Missing Door Mark"
Me.txtMark.SetFocus
End If

HTH

 

[kermit5]

Here is where I am:

Private Sub txtMark_LostFocus()
On Error GoTo Err_txtMark_LostFocus

Dim strMessage As String
Dim strTitle As String
Dim bytChoice As Byte

strMessage = "Please enter a valid door mark."
strTitle = "Missing Door Mark"
If IsNull(txtMark) Or txtMark = "" Then
bytChoice = MsgBox(strMessage, vbOKCancel + vbCritical, "Invalid Door Mark")
If bytChoice = vbOK Then
Me.txtFocus.SetFocus
Me.txtMark.SetFocus
Else
Exit Sub
End If
End If

Exit_txtMark_LostFocus:
Exit Sub

Err_txtMark_LostFocus:
MsgBox Err.Description, vbCritical + vbOKOnly, "Error Number:" & Err.Number
Resume Exit_txtMark_LostFocus

End Sub

This seems to capture the errors at the txtMark control. If I select vbOK, it sets the focus back to txtMark. If I select vbCancel, it moves the focus to the next field and allows me to navigate the form or exit, etc.
The reason I have two SetFocus statements is due to the fact that I cannot set the focus to the control that has the focus. I am required to set the focus on another control and then return focus to my control. My form is a dynamic form and therefore I am not guarenteed to have any other field but the txtMark field. I created a control, txtFocus, that is unseen on the form for the sole purpose of controlling the focus.

There is still the scenario where I Cancel the door mark error which lets me advance to the next control. If I then enter a value in this field (and any or all of the following controls), when I attempt to leave the record, I still get an Access error.

I don't know where this error is generated. I would like to give the user the option of entering a valid door mark (perhaps using an inputbox) or deleting the record and resetting the focus on the txtMark control.

Any ideas?
Thanks for your help.

 

[ghudson]

What if you stuck an undo command in the 'Else' which will undo any changes to the current record?

Private Sub txtMark_LostFocus()
On Error GoTo Err_txtMark_LostFocus

Dim strMessage As String
Dim strTitle As String
Dim bytChoice As Byte

strMessage = "Please enter a valid door mark."
strTitle = "Missing Door Mark"
If IsNull(txtMark) Or txtMark = "" Then
bytChoice = MsgBox(strMessage, vbOKCancel + vbCritical, "Invalid Door Mark")
If bytChoice = vbOK Then
Me.txtFocus.SetFocus
Me.txtMark.SetFocus
Else
DoCmd.RunCommand acCmdUndo
Exit Sub
End If
End If

Exit_txtMark_LostFocus:
Exit Sub

Err_txtMark_LostFocus:
MsgBox Err.Description, vbCritical + vbOKOnly, "Error Number:" & Err.Number
Resume Exit_txtMark_LostFocus

End Sub

 

[kermit5]

If I put it here, I get error#2046 "The command or action 'Undo' isn't available now."

I think this is the code that I want but I don't think that it is the right place. I need it to be wherever the process of writing the current record to the associated table. That is, when I leave a record, Access writes that record (saves it) to it's table(s). Before this happens, I need to verify that the PK (txtMark) is not Null or blank. The last control of the record is not a fixed control, meaning it could be one of 45 or so available fields. I need a more general way of interupting this process (of saving the record) to require the user to either enter a valid door mark or undo the changes to the current record.

Do you know where this occurs?

 

[ghudson]

You should check if the field(s) is Null in the 'Forms' BeforeUpdate event. That will allow you to check for errors and stop the saving process if needed.

Also, you can trap for that error number...

Exit_txtMark_LostFocus:
Exit Sub

Err_txtMark_LostFocus:
If Err = 2046 Then 'The command or action Undo is not available now
Exit Sub
Else
MsgBox Err.Description, "Error Number:" & Err.Number
Resume Exit_txtMark_LostFocus
End If

End Sub

 

[kermit5]

Here is my new code on the Forms BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'This verifies that a valid value is entered in the PK (txtMark)
On Error GoTo Err_Form_BeforeUpdate

Dim bytChoice As Byte
Dim newMark As String

If IsNull(Me.txtMark) Then
bytChoice = MsgBox("You failed to enter a valid door mark." & vbCrLf & _
"Please enter a valid door mark.", vbCritical + vbOKCancel, _
"Missing Door Mark")
If bytChoice = vbOK Then
GoTo NewMark_Form_BeforeUpdate
Else
DoCmd.RunCommand acCmdUndo
Me.txtMark.SetFocus
Exit Sub
End If
End If
NewMark_Form_BeforeUpdate:
newMark = InputBox("Please enter a valid door mark.", "Missing Door Mark")
Me.txtMark = newMark

Exit_Form_BeforeUpdate:
Exit Sub
Err_Form_BeforeUpdate:
If Err.Number = 3022 Then
MsgBox "The door mark you have entered has already been used." & vbCrLf & _
"Please enter a unique door mark.", vbCritical + vbOKOnly, "Duplicate Door Mark"
Resume NewMark_Form_BeforeUpdate
End If
MsgBox Err.Description, , "Error Number:" & Err.Number
Resume Exit_Form_BeforeUpdate
End Sub

Here is my new problem.

It works just like I wanted with the following exception:
If I cancel out of the first error (advancing to the next control), I enter a value for that field, attempt to update (resulting in the msgbox that I left the txtMark control blank), click vbOK (resulting in an input box for the newMark), I have the following results:
If I enter a unique value, the form works properly, the record is saved and all is merry & bright.
If I enter a duplicate value via the imput box, I get the following error:

The changes that you requested were not successful because they would create duplicate values in the index, pk, or the relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to allow duplicate entries and try again.


I would also like to trap this error. Where is this occurring?

Thanks for your help

 

[ghudson]

In the forms BeforeUpdate event, I would verify if the value in the txtMark field already exists in the table.

I suggest using the DLookup function to search for the value. If the value already exists, then exit the sub. Check the help files for the DLookup functon for more info and examples.

HTH