The subforms I have are continous so that multiple information can be entered under one part number

For example:

Part Number 9999 goes to customer A, B and C

So the main form and the subform are linked by PNID

PNID 1 Part Number 9999

Customer A PNID 1
Customer B PNID 1
Customer C PNID 1

Is there a way so that when the user gets to the last field in a subform (not the last record) and they hit the tab key to make it go to the next control box outside of the subform?

Go to the last control on the subform and look at the properties (Events tab)...see the "OnKeyDown" event?

Use that event and go to the VBA code.

Then use the following code:

If Keycode = vbKeyTab Then
Me.mainformnextcontrol.SetFocus
End If

Where 'mainformnextcontrol' is the next control name that you want it to go to.

Hope that's helpful!

It is already built into access. The correct way really is this:

To move out of a subform using the tab key you must also use the control key. Press Control-Tab to move out of a sub form.

I tried this but did not work when I put it in the last control in the subform. I get an error message saying it can not find the control I am telling it to SetFocus on as it still is in the subform which does not contain that control.
I changed Me.mainformnextcontrol.SetFocus to Forms!DataEntryFrm!RevLvl.SetFocus and it kinda works except it does not go to the control I am telling it to it.

When I manually do Ctrl+Tab it does go to the field I want since I set the Tab order for the form to go from the Subform to the RevLvl control.

Using the "OnKeyDown" the code now looks like:

If Keycode = vbKeyTab Then
Forms!DataEntryFrm!RevLvl.SetFocus
End If

The reason I am trying to do this in code is because too many of the people that I deal with do not know Access.
I am trying to make the database as easy as possible for them to use.

It can be difficult sometimes trying to set focus from a main form to a sub form. I have found that it works best if I first set the focus to the subform, then to the control I want on the subform. Something like this:

Me.frmSubformName.SetFocus
Me.frmSubformName.Form!subformcontrolname.SetFocus.

I would also take extra care to make sure that you are calling the controls by the names you assigned to them. Could there be an error in how you are calling a control's name?

My apologies, I just reread your post and think I have a better understanding now of what your problem might be.

If you are traveling from a control in your subform to a control in your main form, you must use the word "Parent" in there, like this:

Me.Parent.ControlNameYouWantToMoveTo.SetFocus

Try that.

Still not working right. Doing the same thing when I hit the tab key, it goes from the control in the subform I am in down to the subform I have below it not to the control that is to the Right of the Subfrom.

I pasted the coding for the form.

Option Compare Database

Private Sub PartNametxt_AfterUpdate()
Me.PartNametxt = StrConv(Me.PartNametxt, vbUpperCase)
End Sub

Private Sub PartTypetxt_AfterUpdate()

Me.PartTypetxt = StrConv(Me.PartTypetxt, vbUpperCase)
DoCmd.Requery "PartTypetxt"
Refresh
Repaint

End Sub

Private Sub PartTypetxt_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyTab Then
Me.Parent.BWRevLvltxt.SetFocus
End If
End Sub

And yes I have checked the names and it is correct.



Edited by: TitinaLorey on Tue Jun 7 8:17:28 EDT 2005.

Ok been working on it. When I hit the tab key it goes from the subform, SKIPS (at least it lookslike it is being skipped) the one I want and goes to the next control on the form ( I rechecked the tab order for the form and it is ok)....this problem is getting frustrating..

This is the coding in the subform:

Option Compare Database

Private Sub PartNametxt_AfterUpdate()

Me.PartNametxt = StrConv(Me.PartNametxt, vbUpperCase)

End Sub

Private Sub PartTypetxt_AfterUpdate()

Me.PartTypetxt = StrConv(Me.PartTypetxt, vbUpperCase)
DoCmd.Requery "PartTypetxt"

End Sub

Private Sub PartTypetxt_KeyDown(KeyCode As Integer, Shift As Integer)

Dim ctl As Control
Set ctl = Forms!PPAPDataEntryFrm!BWRevLvltxt

If KeyCode = vbKeyTab Then
DoCmd.GoToControl "BWRevLVLtxt"
End If

End Sub

And this is the coding in the Main Form:

Option Compare Database

Private Sub BtnEditRecord_Click()

If MsgBox("Do you want to open PPAP Edit Form?", vbYesNo, "Edit Options") = vbYes Then
DoCmd.OpenForm "PPAPDataSearchFrm", acNormal, , , acFormEdit, acWindowNormal
DoCmd.Close acForm, "PPAPDataEntryFrm", acSaveYes
Else
Me.SelPPAPTypeFrame.Locked = False
HideSelBWMfgPN
EnableButtons
UnlockRecords
End If

End Sub

Private Sub BtnCloseFrm_Click()
On Error GoTo Err_BtnCloseFrm_Click

DisableButtons
DoCmd.Close

Exit_BtnCloseFrm_Click:
Exit Sub

Err_BtnCloseFrm_Click:
MsgBox Err.Description
Resume Exit_BtnCloseFrm_Click

End Sub
Private Sub BtnAddNewRec_Click()
On Error GoTo Err_BtnAddNewRec_Click

'Adds a new record, Unlocks the Combo Box for SelBWMfgNo
'and calls the Subroutine "Hide" and "UnlockRecords"

Hide
Me.SelPPAPTypeFrame.Locked = False
UnlockRecords
EnableButtons
DoCmd.GoToRecord , , acNewRec

Exit_BtnAddNewRec_Click:
Exit Sub

Err_BtnAddNewRec_Click:
MsgBox Err.Description
Resume Exit_BtnAddNewRec_Click

End Sub

Private Sub btnOpenSearch_Click()
DoCmd.OpenForm "SearchFrm", acNormal, , , , acWindowNormal
DoCmd.Close acForm, "PPAPDataEntryFrm", acSaveYes
End Sub

Private Sub BtnSaveRec_Click()
On Error GoTo Err_BtnSaveRec_Click

'Saves the changes made, Calls the Subroutines "LockRecords" and "HideSelBWMfgPN"
'Locks the combo control for SelBWMFgNo.

LockRecords
Me.SelPPAPTypeFrame.Locked = True
HideSelBWMfgPN
Me.SelPPAPTypeFrame.SetFocus
DisableButtons
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_BtnSaveRec_Click:
Exit Sub

Err_BtnSaveRec_Click:
MsgBox Err.Description
Resume Exit_BtnSaveRec_Click

End Sub
Sub Hide()

'This subroutine will be called when the Add New Record button is clicked (On Click event - BtnAddNewRec_Click)
'When the Add New Record button is clicked all the fields and subforms will no longer be visible
'untill you check the box in PPAP Type for either SUPPLIER or CUSTOMER.
'Then the fields for either the SUPPLIER PPAP or CUSTOMER PPAP fields will appear depending
'on which box is checked.

Me.SelBWMfgPN.Visible = False
Me.BWMnfNoSelLabel.Visible = False
Me.BoxBorder.Visible = False
Me.PPAPDataEntrySubFrm.Visible = False
Me.BWRevLvlLbl.Visible = False
Me.BWRevLvltxt.Visible = False
Me.BWCustPNLbl.Visible = False
Me.BWCustNotxt.Visible = False
Me.BWCustPNRevLbl.Visible = False
Me.BWCustRevLvltxt.Visible = False
Me.CustPNLbl.Visible = False
Me.CustNumtxt.Visible = False
Me.CustPNRevLbl.Visible = False
Me.CustRevLvltxt.Visible = False
Me.CustPartDataSubFrm.Visible = False
Me.CustPPAPDataSubFrm.Visible = False
Me.SuppPartDataSubFrm.Visible = False
Me.SuppPPAPDataSubFrm.Visible = False

End Sub
Sub SupplierData()

'This subroutine is called in the AfterUpdate event for SelPPAPTypeFrame
'and also in the On_Current Event for the form.
'If the box for Supplier is checked in the Select PPAP Type Box then the fields belonging to
'Supplier PPAP will appear and the fields that belong to Customer PPAP will no longer be visible.

Me.PPAPDataEntrySubFrm.Visible = True
Me.BoxBorder.Visible = True
Me.BWRevLvlLbl.Visible = True
Me.BWRevLvltxt.Visible = True
Me.BWCustPNLbl.Visible = False
Me.BWCustNotxt.Visible = False
Me.BWCustPNRevLbl.Visible = False
Me.BWCustRevLvltxt.Visible = False
Me.CustPNLbl.Visible = False
Me.CustNumtxt.Visible = False
Me.CustPNRevLbl.Visible = False
Me.CustRevLvltxt.Visible = False
Me.CustPartDataSubFrm.Visible = False
Me.CustPPAPDataSubFrm.Visible = False
Me.SuppPartDataSubFrm.Visible = True
Me.SuppPPAPDataSubFrm.Visible = True

End Sub
Sub CustomerData()

'This subroutine is called in the AfterUpdate event for SelPPAPTypeFrame
'and also in the On Current Event for the form.
'If the box for Customer is checked in the Select PPAP Type Box then the fields belonging to
'Customer PPAP will appear and the fields that belong to Supplier PPAP will no longer be visible.

Me.PPAPDataEntrySubFrm.Visible = True
Me.BoxBorder.Visible = True
Me.BWRevLvlLbl.Visible = True
Me.BWRevLvltxt.Visible = True
Me.BWCustPNLbl.Visible = True
Me.BWCustNotxt.Visible = True
Me.BWCustPNRevLbl.Visible = True
Me.BWCustRevLvltxt.Visible = True
Me.CustPNLbl.Visible = True
Me.CustNumtxt.Visible = True
Me.CustPNRevLbl.Visible = True
Me.CustRevLvltxt.Visible = True
Me.CustPartDataSubFrm.Visible = True
Me.CustPPAPDataSubFrm.Visible = True
Me.SuppPartDataSubFrm.Visible = False
Me.SuppPPAPDataSubFrm.Visible = False

End Sub

Sub LockRecords()

'This subroutine locks the Fields listed below so no changes can be made
'to the fields untill the "Edit" button is clicked when changes need to be done to existing records,
'or until the "Add New Record" button is clicked when a new record is being added.

Me.SelBWMfgPN.Locked = True
Me.PPAPDataEntrySubFrm.Locked = True
Me.BWRevLvltxt.Locked = True
Me.BWCustNotxt.Locked = True
Me.BWCustRevLvltxt.Locked = True
Me.CustNumtxt.Locked = True
Me.CustRevLvltxt.Locked = True
Me.CustPartDataSubFrm.Locked = True
Me.CustPPAPDataSubFrm.Locked = True
Me.SuppPartDataSubFrm.Locked = True
Me.SuppPPAPDataSubFrm.Locked = True

End Sub

Sub UnlockRecords()

'This subroutine unlocks the fields listed below so that changes or additons can be made.
'The "Edit" and "Add New Record" buttons call this subroutine.

Me.SelBWMfgPN.Locked = False
Me.PPAPDataEntrySubFrm.Locked = False
Me.BWRevLvltxt.Locked = False
Me.BWCustNotxt.Locked = False
Me.BWCustRevLvltxt.Locked = False
Me.CustNumtxt.Locked = False
Me.CustRevLvltxt.Locked = False
Me.CustPartDataSubFrm.Locked = False
Me.CustPPAPDataSubFrm.Locked = False
Me.SuppPartDataSubFrm.Locked = False
Me.SuppPPAPDataSubFrm.Locked = False

End Sub

Sub HideSelBWMfgPN()

Me.SelBWMfgPN.Visible = False
Me.BWMnfNoSelLabel.Visible = False

End Sub

Sub UnHideSelBWMfgPN()

Me.SelBWMfgPN = True
Me.BWMnfNoSelLabel.Visible = True

End Sub

Private Sub BWCustNotxt_AfterUpdate()
Me.BWCustNotxt = StrConv(Me.BWCustNotxt, vbUpperCase)
End Sub

Private Sub BWCustRevLvltxt_AfterUpdate()
Me.BWCustRevLvltxt = StrConv(Me.BWCustRevLvltxt, vbUpperCase)
End Sub

Private Sub BWRevLvltxt_AfterUpdate()
Me.BWRevLvltxt = StrConv(Me.BWRevLvltxt, vbUpperCase)
End Sub

Private Sub CustNumtxt_AfterUpdate()
Me.CustNumtxt = StrConv(Me.CustNumtxt, vbUpperCase)
End Sub

Private Sub CustRevLvltxt_AfterUpdate()
Me.CustRevLvltxt = StrConv(Me.CustRevLvltxt, vbUpperCase)
End Sub

Private Sub Form_Current()
'As the records change this code checks to see which PPAPType box is checked and then runs the
'appropriate call routine. Then it hides the SelBWNfgNo combobox.

If SelPPAPTypeFrame = 1 Then
SupplierData
ElseIf SelPPAPTypeFrame = 2 Then
CustomerData
End If
HideSelBWMfgPN
LockRecords
DisableButtons

End Sub

Private Sub SelBWMfgPN_AfterUpdate()
'After being updated, the SelBWMfgNo combobox is requeried as is the PPAPDataEntrySubFrm.

DoCmd.Requery "SelBWMfgNo"
DoCmd.Requery "PPAPDataEntrySubFrm"
Me.Repaint
Me.Refresh
End Sub

Private Sub SelBWMfgPN_NotInList(NewData As String, Response As Integer)
'This uses an Insert statement to add the inputted value to the table. In this case the combobox
'is querying a lookup table where there is only one field (plus an autonumber PK).

Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl

strMsg = "BW Mfg PN " & NewData & " Is not listed!" & vbCrLf & "Do you want to add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO BWMfgPNTbl (BWMnfNo) "
strSQL = strSQL & "VALUES('" & NewData & "');"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub

Private Sub SelPPAPTypeFrame_AfterUpdate()

If SelPPAPTypeFrame = 1 Then
SupplierData
UnHideSelBWMfgPN
ElseIf SelPPAPTypeFrame = 2 Then
CustomerData
UnHideSelBWMfgPN
End If

End Sub

Private Sub BtnDelRec_Click()
On Error GoTo Err_BtnDelRec_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_BtnDelRec_Click:
Exit Sub

Err_BtnDelRec_Click:
MsgBox Err.Description
Resume Exit_BtnDelRec_Click

End Sub
Private Sub BtnUndoRec_Click()
On Error GoTo Err_BtnUndoRec_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_BtnUndoRec_Click:
Exit Sub

Err_BtnUndoRec_Click:
MsgBox Err.Description
Resume Exit_BtnUndoRec_Click

End Sub
Sub EnableButtons()
Me.BtnUndoRec.Enabled = True
Me.BtnSaveRec.Enabled = True
Me.BtnDelRec.Enabled = True
End Sub

Sub DisableButtons()
Me.BtnUndoRec.Enabled = False
Me.BtnSaveRec.Enabled = False
Me.BtnDelRec.Enabled = False
End Sub


Any help would be greatly appreciated.

Edited by: TitinaLorey on Tue Jun 7 11:45:53 EDT 2005.

Aha! You know, I've encountered this problem before. I, like you, came here for assistance, but no one could ever explain to me why the focus was going to the control after the control I was setting focus on.

I don't have time to review all that code you posted, but I will tell you how I found a work-around to the problem in my program. I ended up setting the focus on one control before the one that I actually wanted. It seems a 'messy' way of doing it, I know, but I just couldn't find another way to do it. That is what I would suggest to you.

If you don't have a control on your form that precedes the one you want to set focus on, then make one. I'd make some kind of transparent (as opposed to invisible) text box or something.

Hope that points you in the right direction!

Your suggestion worked. I made a blank unbound text control and set it as next control to go to after the subform so that now when I hit the tab key it goes to the field I want while skipping over the Blank control.

I'm having a problem that is similar.
I can't get from the header of my subform to the detail, footer or anywhere else!
I just go in cirlces!
Any ideas?