Have I got the code correct?

[Tony Williams]

I have a control on a form that I want to ensure the user completes before
moving on. It is a date control and I'm using this code in the BeforeUpdate
of the control
Private Sub txtMontha_BeforeUpdate(Cancel As Integer)
If IsNull(txtMontha) Then
MsgBox "You can not enter a record without completing the Qtr End Date,
vbCritical, Need Qtr End Date"
End If
End Sub

How ever nothing happens, can anyone tell me why. I've tried enclosing the
dates in "#" but the debugger doesn't like that.
Can IsNull be used for date controls?
TIA
Tony

 

[Rick B]

You need to stop it from completing the "update"...

If IsNull(txtMontha) Then
MsgBox "You can not enter a record without completing the Qtr End
Date,vbCritical, Need Qtr End Date"
Cancel=True
Me.TxtMontha.setfocus
End If
End Sub

 

[Dan Artuso]

Hi,
Are you refering to the Calendar control?
If so, try checking for the empty string "" instead

 

[Guest]

You'd better put code on the event LostFocus. After MsgBox I would put an
extra line setting focus on that field again.

 

[Tony Williams]

Thanks that worked in part. However although I get the message box when I
click OK it doesn't set the focus back to the control I've tried all
variations of Me.txtmontha.setfocus like txtmontha.setfocus and
[txtmontha].setfocus but the cursor just wont go back to that control it
goes to the next one. Don't understand that?
Tony

 

[PC Datasheet]

The syntax in the MsgBox line is not correct. Should be:
MsgBox "You can not enter a record without completing the Qtr End
Date",vbCritical, "Need Qtr End Date"

 

[Tony Williams]

Thanks PC I've changed that but now seem to have a problem with Set see my
other replies
Thanks again
Tony

 

[Tony Williams]

Sorry for the typo PC it should have been problems with Setfocus
Tony

 

[PC Datasheet]

Tony,

You have your code in the wrong place for the logic you are trying to
achieve. Say the user uses the mouse and goes to another control other than
txtMontha. He can then close the form which saves the record and your
message never comes up. The Qtr End Date will be blank.

Rather you need to put your code in the Form's BeforeUpdate event. That
event fires when a change has been made anywhere in the record and you will
be able to setfocus back to txtMontha if it is null. Something else you
ought to check for is to see if the user made an entry in the field and then
went back, highlighted the entry and deleted. The value in the textbox would
be an empty string ("") not null if he did that. Finally when you use the
Form's BeforeUpdate event, you should give the user the option to just
forget everything he just did in that record by undoing all his entries.
Here's suggested code for all the above:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim MsgStr As String
Dim TitleStr As String
MsgStr = "You can not enter a record without " _
& "completing the Qtr End Date" & VbCrLf & VbCrLf _
& "Do You Wish To Go Back " _
& "And Enter The Qtr End Date?"
TitleStr = "Qtr End Date Must Be Entered"
If IsNull(Me!txtMontha) Or Me!txtMontha = "" Then
If MsgBox(MsgStr,vbYesNo,TitleStr) = vbYes Then
Cancel = True
Me!txtMontha.SetFocus
Else
Me.Undo
End If
End If
End Sub

 

[PC Datasheet]

Tony,

You have your code in the wrong place for the logic you are trying to
achieve. Say the user uses the mouse and goes to another control other than
txtMontha. He can then close the form which saves the record and your
message never comes up. The Qtr End Date will be blank.

Rather you need to put your code in the Form's BeforeUpdate event. That
event fires when a change has been made anywhere in the record and you will
be able to setfocus back to txtMontha if it is null. Something else you
ought to check for is to see if the user made an entry in the field and then
went back, highlighted the entry and deleted. The value in the textbox would
be an empty string ("") not null if he did that. Finally when you use the
Form's BeforeUpdate event, you should give the user the option to just
forget everything he just did in that record by undoing all his entries.
Here's suggested code for all the above:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim MsgStr As String
Dim TitleStr As String
MsgStr = "You can not enter a record without " _
& "completing the Qtr End Date" & VbCrLf & VbCrLf _
& "Do You Wish To Go Back " _
& "And Enter The Qtr End Date?"
TitleStr = "Qtr End Date Must Be Entered"
If IsNull(Me!txtMontha) Or Me!txtMontha = "" Then
If MsgBox(MsgStr,vbYesNo,TitleStr) = vbYes Then
Cancel = True
Me!txtMontha.SetFocus
Else
Me.Undo
End If
End If
End Sub

 

[Tony Williams]

Thanks PC That's absolutely great!
Tony

Tony,

You have your code in the wrong place for the logic you are trying to
achieve. Say the user uses the mouse and goes to another control other than
txtMontha. He can then close the form which saves the record and your
message never comes up. The Qtr End Date will be blank.

Rather you need to put your code in the Form's BeforeUpdate event. That
event fires when a change has been made anywhere in the record and you will
be able to setfocus back to txtMontha if it is null. Something else you
ought to check for is to see if the user made an entry in the field and then
went back, highlighted the entry and deleted. The value in the textbox would
be an empty string ("") not null if he did that. Finally when you use the
Form's BeforeUpdate event, you should give the user the option to just
forget everything he just did in that record by undoing all his entries.
Here's suggested code for all the above:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim MsgStr As String
Dim TitleStr As String
MsgStr = "You can not enter a record without " _
& "completing the Qtr End Date" & VbCrLf & VbCrLf _
& "Do You Wish To Go Back " _
& "And Enter The Qtr End Date?"
TitleStr = "Qtr End Date Must Be Entered"
If IsNull(Me!txtMontha) Or Me!txtMontha = "" Then
If MsgBox(MsgStr,vbYesNo,TitleStr) = vbYes Then
Cancel = True
Me!txtMontha.SetFocus
Else
Me.Undo
End If
End If
End Sub