can't get validation to work

[Gator]

When the user clicks OK, data is inserted into a record...unless the date
field is empty...then a msgbox prompts for the date and the insert should
cancel.
the following msgbox prompts correctly but when I click OK, the record is
created anyway, why is this happening?

Private Sub Command17_Click()

If IsNull(Text21) Then
MsgBox "Enter a Date on Top", vbOKOnly, Date
Cancel = True
Me.Text21.SetFocus
End If

Dim mycon As New ADODB.Connection
Dim ADOrs As New ADODB.Recordset
Set mycon = CurrentProject.Connection
ADOrs.Open "TreasurerDeposits", mycon, adOpenKeyset, adLockOptimistic
ADOrs.AddNew
ADOrs!Source = Me.Text9
ADOrs!Type = Me.Text11
ADOrs!Fund = Me.Text13
ADOrs!Account = Me.Text15
ADOrs!Date = Me.Text21
ADOrs.Update
ADOrs.Close
mycon.Close
Me.TreasurerDeposits.Requery
End Sub

 

[Jeff Boyce]

Gator

Have you tried setting a breakpoint on your "If" statement so you can step
through each line's execution and see where it breaks?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Hmmm, I thought "Cancel" worked on a BeforeUpdate event, not the "Click"
event.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Gator]

I tried that as well but it did the same thing...it inserts the data into a
new record....what it is suposed to do unless the date field is null...
thanks

 

[Tom van Stiphout]

On Fri, 17 Oct 2008 15:08:02 -0700, Gator

At the top of the code write:
Option Explicit
Also turn this on in the default settings.
"Cancel=True" will then be illegal, because you did not declare a
variable named Cancel.

Set a breakpoint on the "If IsNull(Text21)" line, and inspect the
value of Text21. For example in the immediate window write:
?IsNull(Text21)
=> False
?(Text21="")
=> True
Then you know the value was not null, but an empty string, and you can
correct your code.

-Tom.
Microsoft Access MVP

 

[Gator]

1-My edited code is below.
2-I went to the properties settings for the Command4 and set Cancel equal to
true.
3-I ran the click event and got an error "user defined type not defined" at
the line "Dim mycon As New ADODB.Connection"
4-I'm using 2000

Private Sub Command4_Click()
Dim mycon As New ADODB.Connection
Dim ADOrs As New ADODB.Recordset
Set mycon = CurrentProject.Connection
ADOrs.Open "Customers", mycon, adOpenKeyset, adLockOptimistic
ADOrs.AddNew
ADOrs!CustomerID = Me.Text0
ADOrs!CompanyName = Me.Text2
ADOrs.Update
ADOrs.Close
mycon.Close
End Sub

Option Explicit
Private Sub Text0_BeforeUpdate(Cancel As Integer)
If IsNull(Text0) Then
MsgBox "Enter an ID", vbOKOnly, Id
Cancel = True
Me.Text0.SetFocus
End If
End Sub

thanks

 

[Gator]

ignore that previous reply by me...

1-My edited code is below.
2-I went to the properties settings for the Command4 and set Cancel equal to
true.
3-I ran the click event and got an error "user defined type not defined" at
the line "Dim mycon As New ADODB.Connection"
4-I'm using 2000

Private Sub Command4_Click()
Dim mycon As New ADODB.Connection
Dim ADOrs As New ADODB.Recordset
Set mycon = CurrentProject.Connection
ADOrs.Open "Customers", mycon, adOpenKeyset, adLockOptimistic
ADOrs.AddNew
ADOrs!CustomerID = Me.Text0
ADOrs!CompanyName = Me.Text2
ADOrs.Update
ADOrs.Close
mycon.Close
End Sub

Option Explicit
Private Sub Text0_BeforeUpdate(Cancel As Integer)
If IsNull(Text0) Then
MsgBox "Enter an ID", vbOKOnly, Id
Cancel = True
Me.Text0.SetFocus
End If
End Sub

thanks