I'm fairly new to Access and this is my 1st foray into subforms. Here is what I have.

Main Form: frmOrders
SubForms of frmOrders: frmGuide and frmBotBar

When I get to the my last field of the main form, frmOrders, because of tab control the cursor moves to the form, frmGuide. That is what I want it to do. When I reach the last field of frmGuide, if I press the tab key, another record is created within frmGuide. I do not want that, as there is a 1-1 relationship between the tables behind frmOrders and frmGuide.

I set Allow Additions = NO in the form frmGuide. I was hoping that it would not allow additional records, but it does what it says and allows no records at all.

I changed that back and then I put the following code into the OnExit event of my last field, HPAreaJambSlotSize, of the form, frmGuide, hoping that it would send focus to frmBotBar. It doesn't send focus to frmBotBar, but it doesn't create a new record either. I can then use the mouse to click on frmBotBar or frmOrders to get to those forms, but if I try to go back to frmGuide, I can't leave the field that I was previously in. So, that isn't a good solution either.

Private Sub ComboHPAreaJambSlotSize_Exit(Cancel As Integer)
' Test to make sure field has a value if necessary
If GuideConfig = "B" Then
If IsNull(HPAreaJambSlotSize) Then
MsgBox "HP Area Jamb Slot Size must be have a value when a Guide Configuation = B." ComboHPAreaJambSlotSize.SetFocus
Cancel = True
End If
End If
' Send focus to frmBotBar
If BelowHPJambSlotSpace.Enabled = True Then
Forms![frmOrders]![frmBotBar].Form![ComboBottomBarSize#].SetFocus
Cancel = True
End If
End Sub

I replaced this line
Forms![frmOrders]![frmBotBar].Form![ComboBottomBarSize#].SetFocus
with this line
Forms![frmOrders]![frmBotBar].SetFocus
and it now moves to frmBotBar. I can move to frmOrders, but when I move back to frmGuide, it always goes to BelowHPJambSlotSpace.

What is the proper way to change the focus back to frmOrders or to frmBotBar?

Thanks
Kenton



Edited by: PA232 on Tue Mar 18 16:11:58 EDT 2008.

Use the form's "Cycle" property and set it to "Current Record". That will prevent the new record recieving focus when you tab through the last field.

Further, You set the form's (subform 'A') "Key Preview" property to 'Yes' and in that last field of subform 'A', you set and event procedure in the control's "On Key Press" that resembles this:


hope this helps

I've been beating my head against the wall trying to figure this out. I set the "Cycle" property to "Current Record" and it does exactly what I want. Thanks a Million!!!

I'm assuming that the 2nd part is to move focus from subform to subform or back to the main form. If that is true, I've decided to not allow that and force them to use the mouse to get back and forth. If that code is for something else please let me know.

Thanks again,
Kenton

Good! You'e done exactly what we hope from those we assist, to take what they want and leave the rest.

That's exactly what that second bit of code is about, to get to the detail section.

Glad to have been able to help. Good luck with your project(s).