Validation question (1 Viewer)

[Timberwolf]

I have a two-character text field that can contain one of about 30 different preset codes. I have a long-integer claim number field. If the two-character code = "CN", the claim number field cannot be null (it's okay if it's null in any other circumstance).

Help?

Thanks!

 

[Mile-O]

What's this on? A table or a form?

 

[Timberwolf]

A form. Thanks. (Or would it be best to do it on the underlying table?)

 

[Mile-O]

You haven't said what controls you have, and you haven't really mentioned much, actually, but here's the assumptions I'm making.

Would you allow the claim number to have a null value if the code was "CN"?

You have a combobox on your form with a list of codes (cboCodes)
You have a textbox on your form (txtClaimNumber)


On the form's BeforeUpdate event is where you want it, I think.

Something like this, possibly.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.cboCodes = "CN" And IsNull(Me.txtClaimNumber) Then
        MsgBox "You must enter a claim number", vbExclamation, "Missing Claim Number"
        Cancel = True
    End If

End Sub

 

[Timberwolf]

I'm sorry I left out so much information. It is okay for the claim number to have a value if the code = CN. And your other assumptions are correct.

I'll give this a go.

Thanks so much!

 

[Timberwolf]

Unfortunately, that didn't work.

Here's the code as I typed it:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.ActivCode = "CN" And IsNull(Me.ClaimNo) Then
MsgBox "You must enter a claim number", vbExclamation, "Missing Claim Number"
Cancel = True
End If

End Sub

The form then gets stuck in the last field of the record and won't allow me to tab to continue to the next record, whether or not the ClaimNo field is empty and whether or not the ActivCode = "CN'.

Any other ideas? Thanks again.

 

[Mile-O]

If you've made a "Save/Confirm record" command button on your form then, maybe altering the code on a little on that's Click() event might do it:

    If Me.ActivCode = "CN" And IsNull(Me.ClaimNo) Then 
        MsgBox "You must enter a claim number", vbExclamation, "Missing Claim Number" 
        Me.ClaimNo.SetFocus
        Exit Sub
   End If