GoToControl macro action

[Russ]

In my form I've written a short macro that has a message box popping
up when a user enters the wrong data in a control. Works fine. The
action that I want to occur after the message box OK is clicked is to
GoToControl 'my control name'. However, my form's focus moves to the
next control in the tab order instead. I've got the macro checking
the value in the original control when it loses focus. Why won't the
desired control get the focus?

 

[Russ]

Nothing except that same macro in On Lost Focus and On Change.

 

[Russ]

Here is what is there:

Condition Action
[E58No] Not Between 6294350 And 6296000
.... MsgBox
.... GoToControl
.... StopMacro

 

[BruceM]

The Change event fires after every keystroke. It may not be what you want.
As was suggested in another thread on what seems to be the same topic,
SetFocus may work better than GoToControl. Another idea is to use the
Before Update event, which runs before you leave the control, and can be
cancelled if the test fails, but I rarely use macros (I use Event
Procedures), so I'm not quite sure how that works in the macro world.

 

[Russ]

Thanks Bruce, I'm not much of a mover and shaker in the Event
Procedures world and sometimes I get in over my head when it gets
beyond fairly simple. I've got several controls on this form that
will likely need similar checking, and I figured if I got one down, I
could apply the same logic to the others.

 

[BruceM]

Let me outline how to do this in an Event Procedure. I do not use macros
enough to advise you how to accomplish this with a macro. It sounds as if
you knoiw something about Event Procedures, but since I don't know what you
know I will start from scratch.

In form design view, click the control to select it, then click View >>
Properties (or whatever method you use to view the Property Sheet). Click
the Event tab, then click in the Before Update line. Click the three dots
at the right side of the line, click Code Builder, OK.

This should open the VBA editor, with something like the following:

Private Sub ControlName_BeforeUpdate(Cancel As Integer)

End Sub

The cursor should be blinking between those lines. At the cursor:

If Me.ControlName <6294350 Or _
Me.ControlName >6296000 Then
MsgBox "Out of range"
Me.ControlName.Undo
Cancel = True
End If

Use the actual control name in place of ControlName. Note that the
underscore is an optional line continuation character. You can leave it
out, and have this instead:

If Me.ControlName <6294350 Or Me.ControlName >6296000 Then

This code will have no effect if the user ignores the text box. Same for a
macro that runs when the user tries to leave the text box. The form has a
Before Update event that can be used to validate data. To see the form's
Property Sheet, if the Property Sheet is already open just click the small
square just to the left of the horizontal ruler; otherwise click that square
and open the property sheet as before.

In the form's Before Update event:

If IsNull(Me.Control_1) Then
MsgBox "Control_1 needs a value"
Me.Control_1.SetFocus
Cancel = True
ElseIf IsNull(Me.Control_2) Then
MsgBox "Control_2 needs a value"
Me.Control_2.SetFocus
Cancel = True
ElseIf IsNull(Me.Control_3) Then
MsgBox "Control_3 needs a value"
Me.Control_3.SetFocus
Cancel = True
End If

This is the general idea. It can be streamlined when you become a little
more comfortable with VBA.

 

[Russ]

Thank you Bruce. I'll give your work a try.

I've done some very simple VB work in the past, but most of the time
I'm just fumbling around until something comes out right and quits
passing out error messages. Do you know of a source that I can pick
up to get a handle on some of these commands such as Me and DoCmd so
I'll know what goes with what?

 

[BruceM]

There is a lot of good basic information here:

http://allenbrowne.com/casu-22.html

A lot of links here, but I can't suggest a specific one for VBA:
http://www.allenbrowne.com/links.html

More here:
http://www.allenbrowne.com/tips.html

Also, Help can have useful information, particularly on syntax. I think
DoCmd is explained pretty well, but on the other hand the explanation for Me
spins its wheels for a while in the middle of the trip without imparting
much information about the starting point or the destination.

 

[BruceM]

You're welcome. I think you'll like VBA better than macros for most
purposes. Some macros, especially AutoExec, have their uses, but VBA seems
to be more flexible and powerful.