Return focus to a control

[Joer]

I have a control that the user enters a number. I am trying to insure that
the number entered is at least 5. I tried to create a macro with conditions
that runs a messagebox and then the gotocontrol actions. The messagebox works
but not the gotocontrol. I then wrote an event procedure, which also has the
messagebox and then a setfocus command. Again the messagebox works, but not
the setfocus command. I've tried both the After Update and Lost focus events
with the same results. This is Access 2007. I know I can put a validation
rule and message on the table, but I want to leave a back door to enter lower
numbers.

 

[Klatuu]

You need to use the control's Before Update event. It can be canceled, and
will not loose the focus when you do cancel it. So, if a correct number is
entered, the focus andvances to the next control in the tab order; otherwise,
the control retains the focus:

Private Sub MyControl_BeforeUpdate(Cancel As Integer)
If Me.MyControl <= 5 Then
MsgBox "Must be At Least 5"
Cancel = True
End If
End Sub

 

[Joer]

This did not work, in fact I don't even get the messagebox. I even tried this
code in other events (after update and lost focus) and it didn' t work there
either. Thanks for your time, but I'm still looking for a solution.

 

[Klatuu]

did not work doesn't help much.
This method does work. It is the normal way of doing this.
Did you put a breakpoint in the code to see if the event is firing?
Did you type a value in the text box or do you do it programatically?

 

[Joer]

I run the form and enter values into the text box in question. With this code
nothing happens, it accepts whatever value I enter without the messagebox
appearing and the focus goes to the next text box. I entered a breakpoint,
which is never arrived at. I do not believe this event is firing at all with
this code.

 

[Klatuu]

You have the code in the Before Update event of the text box you are entering
the number in?

Is this by any chance an unbound form?

 

[Joer]

Yes, I entered the code for the procedure event Before Update for the text
box in question. The form is bound to a local table in my database, and this
text box is bound to one of the fields in that table. When that didn't work,
I tried to enter the code into different events (after update, lost focus),
but it didn't work there either. The only time I get anything to work even
partially is to create a macro that has a messagebox action and then a
gotocontrol action and put that macro in the lost focus event or after update
event. In those cases, the messagebox fires, but not the gotocontrol.

 

[Klatuu]

Joer,

I really don't understand your problem. The code I originally posted is the
standard way to validate data entered in a control. Why it is not firing the
message box, I don't know.

 

[Joer]

I created a new form that duplicates the original one that I've been working
with. I entered your code in the before update event on the text box in
question and it works. I then went back to my original form and tried it
again and it will not work on that form. I've no clue why. Maybe because the
original database was created with Access 97 and converted to the 2000
format? But the new form is still in the 2000 format, so I've no clue! Thank
you for your time.

 

[Klatuu]

Great. Sounds like the original form had some corruption going on. I was
sort of beginning to suspect that.
Thanks for letting me know.