Code in subform to set focus upon failed validation works unless you leave subform

[fire2ice]

I'm trying to use code to set validation which checks if the value of Audience is "other" then the value of the control Other cannot be empty. It works fine while I'm in the subform. However, if I click out of the subform it puts focus where the user clicked.

I tried putting the code on the BeforeUpdate event of the subform and the control to make sure that the user couldn't bypass the validation. However, if I click out of the subform, the code triggers the message box but then when user says OK (which should set focus back to the empty field) it puts focus where the user clicked.

(Rural Guy: This is the same database -- Centralized Data Collection -- that you've been helping me on)

My code for the control is as follows:

________________________________________________________________________________________

Private Sub Other_Exit(Cancel As Integer)

If Me.cboAudience = "Other" And (IsNull(Me.Other) Or Me.Other = "") Then

If MsgBox("If you entered Other as your audience, you must enter what the Other is in order to continue. Choose OK to enter Other. Choose Cancel to clear Audience choice and try again.", vbOKCancel, "Other is required!") = vbOK Then
Me.Parent.sfrmAudience.SetFocus
Forms!frmEvents.sfrmAudience.Form!Other.SetFocus

Else
Me.cboAudience.Value = Null
Me!cboAudience.SetFocus

End If

Exit Sub

End If

 

[RuralGuy]

Try just setting Cancel = True to hold the focus in the control.

 

[fire2ice]

I'm trying to accomplish a little more than that. It is a vbOKCancel so that the user can choose OK to try again or cancel to erase the value of Audience in case they incorrectly chose "other." Cancel = True would only work with vbOK or vbCritical and wouldn't give the user the opportunity to correct a mistake in Audience. This would force them to enter something under Other and then go back and change the mistake in Audience.

I hope that made sense.

 

[RuralGuy]

What is the purpose of:

Me.Parent.sfrmAudience.SetFocus
Forms!frmEvents.sfrmAudience.Form!Other.SetFocus

...if the user answers OK?

 

[fire2ice]

That part of the code keeps focus on that control so that users can enter data there if they say OK (meaning they really did choose "other" as the audience and need to enter information in the Other field). Otherwise the code clears the Audience field and sets focus back on Audience for them to choose something than "other" as the audience type. If you look at the thread you were helping me on just recently, you will find the database attached to that thread. Although I've made adjustments to the database since then, they don't really impact this functionality.

Thanks again.

 

[RuralGuy]

As I stated in post #2 of this thread, this:

Me.Parent.sfrmAudience.SetFocus
Forms!frmEvents.sfrmAudience.Form!Other.SetFocus

...can be replaced with:
Cancel = True

Both the BeforeUpdate and Exit event can be canceled and the effect of cancelling these events is the focus remains on that control. You can not successfully execute a SetFocus to a control if you are currently in that control! That is why Access has provided the two events that can be canceled.

 

[fire2ice]

If I put Cancel = True, then the focus just moves to the next tab field when the user clicks on OK. It doesn't keep focus in that control.

 

[fire2ice]

Copy of the Database Attached

I've attached a copy of the database so you can actually work with the code.

Thanks.

 

[RuralGuy]

We need to start by getting a clean compile. Open the code module of any form and Debug>Compile. Fix any errors and then do a Compact and Repair before zipping the db for upload. Post a new db when the errors have been corrected.

 

[fire2ice]

Compiled, compacted and repaired copy of database

This a clean copy to work with. Thanks again.

 

[RuralGuy]

If you want to hold the focus in a control then you need to use Cancel = True in either the BeforeUpdate or Exit event of that control. You will have nothing but grief trying to do this in other events like LostFocus. Which one are we working on right now? I've lost track.

 

[RuralGuy]

I've made some changes to your sfrmAudience. This should give you an idea what can be done for your issue. Post back if you have questions.

 

[fire2ice]

I appreciate the help. Sorry I haven't gotten back to you earlier, but I've been out of the office. Just a couple of notes regarding my code. Some of the code you changed to notes served a purpose that needs to remain.

One part of the code enabled the Other control if "other" was chosen as the audience type. It's not a perfect solution because it affects the control itself instead of the control for a single record on the subform. However, it's better than having it enabled if they don't need to enter anything in that control.

Another function the code served was to take the focus out of the subform if they tabbed out of an empty record. In the case of the subform you worked on, it set the focus to a command button. Without the code, tabbing would simply loop through the subform.

It seems that the validation code being on the form's beforeupdate event works well. However, I would like to have the focus go back to the Other control when they select OK. That way the user won't have to go back to enter the data. If I put in a SetFocus to this control, it works fine as long as the user tabs out of the control. However, if they left the subform by clicking elsewhere in the database, it will throw an error. How would I have the code check wheter the focus is in the subform or not? I'd like to say that if the focus is in the subform, then SetFocus to the Other control. Also, if focus is not in the subform, then SetFocus to the subform then Setfocus to the Other control. Would this be the correct solution?

Thanks again and again.

 

[RuralGuy]

Are you going to work on those points you mentioned and then post back when you are stuck?

 

[fire2ice]

Yes. I'm working on them. If I find myself stuck again, I will certainly enlist your aid. However, if you could answer the question of how to check whether the focus is on the form or subform, that would help me out a lot.

 

[RuralGuy]

Screen.ActiveControl points to the control that currently has the focus.

 

[RuralGuy]

Try Screen.ActiveControl.Name

 

[fire2ice]

Sorry I haven't responded to this thread for a while. I had a major report to the government due and had to shift my priorities to that for a while.

I made the changes and adopted your BeforeUpdate solution on the form. The only change I needed to make was to put an If statement after the Cancel=True so that it would SetFocus to cboAudience if that control were empty and to Other if cboAudience contained data. This solved my issue with where focus was set after the event kicked off and I don't seem to be getting errors any longer.

I'm going to apply this to my other subforms and expect no problems. Hopefully this will close this issue.

Thanks again.