form validation - easy code

[scroce]

Having trouble validating a txt box based on the value of a cbo box. See the code below.

Private Sub Form_AfterUpdate()

If Me.cboSST.Value = "hello" And IsNull(Me.txtSSP.Value) = True Then

DoCmd.CancelEvent

MsgBox "You can't have SSP be null while SST is the value hello"

DoCmd.GoToControl "txtSprinklerSystemPct"

End If

End Sub


I'm trying to not let the form move to the next record if cboSST has the value "hello" in it, and force the focus back to txtSSP. It works the first time, (i.e. you get the message box, and the focus goes to txtSSP and the form doesn't proceed to the next record) but then you can just click the next record button again and it goes through, just like the validation was never there to begin with. What am I missing? am I using the wrong form event? I tried the Refresh method but it doesn't work - Can someone point me in the right direction? I'm using access 2002.

Thank you How much more water would there be in the ocean if it weren't for sponges?

 

[jebry]

Hi!

Use the BeforeUpdate event. The form doesn't pass your validation, then set Cancel = -1

hth Jeff Bridgham
bridgham@purdue.edu

 

[scroce]

what does cancel=-1 do?

i assume that it just gets plopped in the code? How much more water would there be in the ocean if it weren't for sponges?

 

[jebry]

Hi!

Cancel = -1 terminates the update so the record doesn't get saved. By the time you get to the AfterUpdate procedure the record is already saved to the table. Here's the whole procedure:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.cboSST.Value = "hello" And IsNull(Me.txtSSP.Value) = True Then
MsgBox "You can't have SSP be null while SST is the value hello"
Cancel = -1
End If

End Sub

hth
Jeff Bridgham
bridgham@purdue.edu

 

[scroce]

ah thank you very much. How come docmd.cancel event doesn't work? That's how I had tried it several times, and obviously it wasn't working.

I had the right idea but the wrong syntax. Do you know what the difference is off hand? How much more water would there be in the ocean if it weren't for sponges?

 

[jebry]

Hi!

In this case the difference was that the event you wanted to cancel already occurred. If you use DoCmd.CancelEvent in the code above instead of Cancel = -1, I think it would work.

hth
Jeff Bridgham
bridgham@purdue.edu