Option Group and setfocus problem

[Guest]

I have a text box on a form as well as an option group within a frame
containing two option buttons. I have written code to display a message box
if the user does not enter any data in the text box. I have a setfocus
statement following the closing of the msgbox but the focus goes to the the
option box. Why and how can I fix that?

Dim intMsg as Integer
IF txtDistance.text = "" Then
intMsg = MsgBox("Distance value is blank", vbOKOnly,"Invalid Entry")
txtDistance.setfocus
End If

Thank you.

 

[Allen Browne]

Two issues: timing, and nulls.

Use the BeforeUpdate event of the control to validate it. If the entry is no
good, just cancel the event and Access won't let you out of the text box.

If nothing has been entered, the value will be Null. That's not the same as
a zero-length string: you need to test for it with IsNull().

Try something like this:
Private Sub txtDistance_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txtDistance) Then
Cancel = True
MsgBox "Distance is blank", vbOKOnly,"Invalid Entry"
End If
End Sub

Notes:
1. It might be easier just to open your table in design view, select the
field, and set its Required property to Yes in the lower pane of table
design.

2. If txtDistance is a Text field (not a Number field), set the
AllowZeroLength property to No (also in the lower pane in table design).
That way you won't have to test for both IsNull() and also = "".

For more information on working with nulls, see:
Common errors with Null
at:
http://members.iinet.net.au/~allenbrowne/casu-12.html

 

[Guest]

Thank you Allen. In conjunction with this post, the calculator is not
associated with a table, just a way the user can calculate the distance. The
code module works but now when I try to close the form, the invalid msg
appears and I end up in a loop. How would I avoid this?

 

[Allen Browne]

So you do want to let the user out of the text box if it's blank, but you
don't want to run the calculation if it's blank?

Then move the IsNull() text out of that event, and put it in the event where
you actually run the calculation (e.g. the Click of a button?)