can't get validation to work

G

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
 
J

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
 
J

Jeff Boyce

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

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

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
 
T

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
 
G

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
 
G

Gator

ignore that previous reply by me...

Gator said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top