Unable to setfocus to a control in a subform

[Hrithika]

I know this topic has been addressed plenty of time and I tried almost every methods discussed on this topic. But for some reason, I am not able to set focus on the control in the subform.

The name of my main form is frmLeaveRequest_Add and the name of my subform is TimeCharged

On the before update event of the main form, I have validation to ensure that data is filled in the subform.
It gives the appropriate message if the data is not entered on the subform. After the message I try to set focus to the control. That part is not working. Given below are the various methods I tried and none of them worked. Could someone direct me in the right direction.

1. Me!TimeCharged.Form!TimeChargeCode.SetFocus

2.
[Forms]![frmLeaveRequest_Add].[TimeCharged].SetFocus
[Forms]![frmLeaveRequest_Add].[TimeCharged].[Form].[TimeChargeCode].SetFocus

3.
Me![TimeCharged].SetFocus
Me![TimeCharged].Form![TimeChargeCode].SetFocus

 

[RuralGuy]

What is the name of your SubFormControl. That is the control that displays the TimeCharged SubForm. That control has the LinkMaster/ChildFields properties on the Data tab and its name on the Other tab.

 

[Hrithika]

TimeCharged is the name of the Subform control

Link Master Field is id (in tblLeaveRequest table)
Link Child Field is ID ( in TimeCharged table)
They have one to many relationship.

 

[mdlueck]

I have successfully used this method in a subform context:

Relocate Previously Selected Record in a Multiple Items Form
http://www.access-programmers.co.uk/forums/showthread.php?p=1178436#post1178436

Note: The form that this code is attached to is the subform itself, not the parent form which has the subform control.

I have never attempted to know a record which is on the subform from the parent form and to have the parent form try to select that record from the subform.

 

[RuralGuy]

If the information is accurate then your method 3 should work.

 

[Hrithika]

@mdlueck,

The setfocus command was part of a code to ensure that the data is filled in the subform if the main form contains the appropriate data. If I have to do it from the subform as you suggested, under which event of the subform should I enter the code you suggested.

If Len(Me.Title & vbNullString) <> 0 _
And Len(Me.StartDate & vbNullString) <> 0 _
And Len(Me.EndDate & vbNullString) <> 0 _
And (Len(Me!TimeCharged.Form!TimeChargeCode & vbNullString) = 0 _
Or Len(Me!TimeCharged.Form!TimeCharged & vbNullString) = 0) _
And Len(Me.Typeofrequest & vbNullString) <> 0 Then
On Error Resume Next
MsgBox "You must select the Time Charge Code and enter the Time Charged ", vbCritical, "Invalid Entry"
Me![TimeCharged].SetFocus
'Cancel = True
'Me!TimeCharged.Form!TimeChargeCode.SetFocus
'[Forms]![frmLeaveRequest_Add].[TimeCharged].SetFocus
'[Forms]![frmLeaveRequest_Add].[TimeCharged].[Form].[TimeChargeCode].SetFocus
Me![TimeCharged].SetFocus
'Me![TimeCharged].Form![TimeChargeCode].SetFocus
End If

 

[Hrithika]

@ RurualGuy,

I have cleaned the code as you suggested but still no luck. I am attaching the database just in case if you need to see. I am a rookie so feel free to correct me wherever I am wrong.

If Len(Me.Title & vbNullString) <> 0 _
And Len(Me.StartDate & vbNullString) <> 0 _
And Len(Me.EndDate & vbNullString) <> 0 _
And (Len(Me!TimeCharged.Form!TimeChargeCode & vbNullString) = 0 _
Or Len(Me!TimeCharged.Form!TimeCharged & vbNullString) = 0) _
And Len(Me.Typeofrequest & vbNullString) <> 0 Then
On Error Resume Next
MsgBox "You must select the Time Charge Code and enter the Time Charged ", vbCritical, "Invalid Entry"
'Cancel = True
Me![TimeCharged].SetFocus
Me![TimeCharged].Form![TimeChargeCode].SetFocus
End If

 

[AccessMSSQL]

You need to set the focus to the subform first and then the control, like this:

Me.subformname.SetFocus
Me.subformname.Form!controlname.SetFocus

you can keep the code in your main form.

correction - sorry: This code works but not in the beforeupdate event. I think this is where your problem lies. You can't do anything in before update without cancelling and undoing the users changes first or saving the main form data. My bad.

 

[AccessMSSQL]

My suggestion to you would be to remove the Validation code from the before update event. Add a SAVE button on your form that runs the validation code. This will allow you to set focus to your subform. You can set a global flag in the form that will prevent the user from saving the record. If the user has clicked SAVE and all validation passes, in your save button set the flag to true. Then, in your beforeupdate event of the form check to see if the save flag is true and do nothing. If it's false, cancel the beforeupdate event and alert user that the form data is not saved.

 

[mdlueck]

@mdlueck,

The setfocus command was part of a code to ensure that the data is filled in the subform if the main form contains the appropriate data. If I have to do it from the subform as you suggested, under which event of the subform should I enter the code you suggested.

Could even define your own custom event on the subform which the parent form knows the name of to call.

That is basically what I have done with my use of subforms... have buttons on the parent form call a known event of the subform via passing through the name of the subform control. Once the event code is executing on the subform, that code may refer to itself via Me reference.

Custom events are just any subroutine name that is marked as a Public subroutine. Events need not show up on the list of Form Events to be real events of the Form... I mean via the GUI properties of the Form.

 

[mdlueck]

I second #9.

Wiring data validation to Arrive/Depart events is dangerous business. Such can not deal with "Oh, I realize I placed the data in the incorrect fields, so I would like to cut/paste to the correct fields." The Arrive/Depart validation might require the field to have value at all times and prevent you from playing the shell game with the data. Never mind the fact that DB integrity was never compromised the entire time the shell game was in process. Only when a DB operation is about to be performed does validation checking need to be done.

 

[Hrithika]

@AccessMSSQL,

Thanks for the clarification that this cannot work from the before update event. I will try to follow your suggestion to put the validations on the Save Button.

 

[razor7132]

You need to set the focus to the subform first and then the control, like this:

Me.subformname.SetFocus
Me.subformname.Form!controlname.SetFocus

you can keep the code in your main form.

correction - sorry: This code works but not in the beforeupdate event. I think this is where your problem lies. You can't do anything in before update without cancelling and undoing the users changes first or saving the main form data. My bad.

AccessMSSQL,

Thank you for posting this as it helped me with a similar but different problem.

My issue was that I had three separate subfoms in a tab control that resided on a parent form. I was unsuccessful in setting the focus on a Textbox.control until I used your solution of setting the focus on the subform first, followed by setting the focus on the textbox.control.

Thanks again for sharing your knowledge!