Stopping movement away from a form control ?

T

TonyB

Hi,
I'm trying to stop a user from moving away from a control on a form using
mouse/kb until something is entered in this field using VBA.
I'm trapping the Exit event for this control, and printing a message
explaining the field should be completed, but I'm unclear how to then put
focus back to the control before exiting the VBA. I tried to use
Me.Controlname.Setfocus after printing the msgbox, but the focus still moves
away from the control to the control which was clicked on once the msgbox
has been OK'd. This must be a common requirement. Can someone explain how to
do this ?
Thanks
Tony
 
A

Allen Browne

You can prevent the user leaving the control simply by canelling the Exit
event, i.e.:
Private Sub MyControl_Exit(Cancel As Integer)
Cancel = True
End Sub

However, this does not guarantee an entry, because the user may never visit
the control.

To prevent the user saving a record if this field is blank, open your table
in design view, and set the field's Required property (lower pane).

To warn the user that the control is blank but allow them to save the record
anyway, use the Form_BeforeUpdate event. This event fires just before saving
the record, and you can check if the field IsNull(), even if the user never
visited the control.
 
N

Nick Coe \(UK\)

Put the SetFocus in the vba which opens the message box
after the msgbox() stuff.

Use a variant to call the msgbox but don't test it's value
just to ensure the execution of that bit of code.

Dim varRet as Variant

blah blah

varRet = MsgBox("Your Message", vbOKOnly, "Value mandatory")

Then use either DoCmd.GotoControl ControlName or
Me!ControlName.SetFocus

Remember to substitute your control name for ControlName...
:)

DoCmd.GoToControl 'WeeklyDosh'
or
Me!WeeklyDosh.SetFocus

And don't forget Me will only work from the subject form's
class module.

Good luck... :)

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/ Download Free Demo Copy

In TonyB typed:
 
T

TonyB

Hi Nick,
I used the Me.controlName.Setfocus after the msgbox code on the exit event
of the control, but the focus still moved to the new control. The msgbox
executed OK, and the Me.controlName.SetFocus appeared to execute ok when I
stepped through the code. I presume that the setfocus to the new control
still runs after the exit event setfocus event.
Thanks
Tony
 
T

TonyB

Hi Allen,
I did have this control as a required property on my table, but I didn't
like the error message which access provides. I was trying to create a user
friendly message
asking user to enter the data if they intended to create a new record. BTW
if create new record is clicked on the form, I ensure this is the first
control to gain focus using the tab order.

Your solution works well, but it has the unfortunate side effect that you
cannot move away from the record now at all ?
This could be a problem. I'm thinking of the case where the user
inadvertently enters create new record, and now cannot get out without
actually completing in this record ?

Thanks
Tony
 
T

TonyB

Hi Peter,
Thanks for the info. As I mentioned to another poster this does mean that
the user cannot now exit the form or go back to an existing record unless
this control is completed.
I suppose what I would prefer is that if the user had inadvertently got to
the new record form they could step back using navigation toolbar, but that
if they tried to save this form they couldn't until this field is completed
? Maybe I could do something with the save event.
Thanks
Tony

Peter R. Fletcher said:
You must set Cancel (the Event Procedure's single argument) to True,
or, indeed, to any other non-zero value. I believe that if you do
that, you don't need to set the focus. If you don't set Cancel, OTOH,
the SetFocus works, but is immediately superceded by the code
triggered by clicking wherever you went next.




Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
=----
 
A

Allen Browne

To get out without proceeding, press the Esc key twice to undo the
entry/record.

If you don't like the default message for Required field, you can set
Required to No, and set a Validation Rule (lower pane of table design) of:
Is Not Null
Then you can set whatever Validation Text you like. (That's easier than
trapping the default engine-level message for Required by using the Error
event of the form.)

Unless you programmatically dirty the record, the user should be able to get
back to the previous record from the new record row.
 

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