If I understand correctly, you just want one of the 4 tabs to have the "focus" depending on the entry in a specific text box.
Tab Controls cannot receive focus. One way you could do it, though, would be to set the visible property of all tabs to false. Then, in your code, depending on the entry in the text box, set the visible property of that particular page to true (remembering that page numbering starts at 0 and not 1).
I just tried it out real quick just on the Load Event of the form and it worked. Off the top of my head I don't know if it is best to reset the visible property back to false whenever the form loads or whether it will automatically do that because of the default settings you put in design view. But if you play around you'll see what happens.
I believe I understand what you are trying to accomplish. You want a selection on your main form to go directly to a tab on a subform of frmParent. I created a sample that I will upload here if my explanation makes no sense. Just let me know.
Because frmSub is a subform on frmParent, you must reference both frmParent and frmSub in any code that you use, even in the subform itself.
So the code will go something like this:
Private Sub btn_Click()
If Forms.frmparent.txtmodcode = "U1PS" Or Forms.frmparent.txtmodcode = "U1PST" Then
ElseIf Forms.frmparent.txtmodcode = "U1AHM" Or Forms.frmparent.txtmodcode = "U1ALM" Then
When you look at the code, you will notice that after Forms!frmParent it says subForm instead of frmSub. This is because subForm refers to the name given the subform when you put it onto the form frmParent.
One last note, I created my example using Access XP but converted to Access 97 and the code still worked. I'm not sure what version you are using.
i tried both above methods, thanks, but still no joy. on the current event of the parent form I have me!frmSub!tab.visible = false and on the after update event of the parent form i put this code:
Dim frmsubMarksUG1 As Form
Dim frmsubMarksUG2 As Form
Dim frmsubMarksUG3 As Form
Dim frmsubMarksUG4 As Form
If Me![year] = 1 Then Me![frmSub].SourceObject = "frmsubMarksUG1"
If Me![year] = 2 Then Me![frmSub].SourceObject = "frmsubMarksUG2"
If Me![year] = 3 Then Me![frmSub].SourceObject = "frmsubMarksUG3"
If Me![year] = 4 Then Me![frmSub].SourceObject = "frmsubMarksUG4"
If Me![ModuleCode] = "U1PS" Or Forms!frmDataEntry.Form![ModuleCode] = "U1PST" Or Forms!frmDataEntry.Form![ModuleCode] = "U1SPS" Then
Me![frmSub].page0.Visible = True
ElseIf Me![ModuleCode] = "U1AHM" Or Forms!frmDataEntry.Form![ModuleCode] = "U1ASS" Or Forms!frmDataEntry.Form![ModuleCode] = "U1AFT" Then
Me![frmSub].page1.Visible = True
ElseIf Me![ModuleCode] = "U1AMU" Then
Me![frmSub].Page2.Visible = True
ElseIf Me![ModuleCode] = "U1ALM" Then
Me![frmSub].Page3.Visible = True
but i get an error message saying access does not support this method or object and points to the page0. properties.
will it not work because i alternate the source object of the frmsub depending on an entry in a control on the parent form?
You are very close on the visible part of your code - just your syntax to referring to the sub-form is incorrect. You first need to refer to the Main Form, then the Sub Form, then the Sub Form Control. (pay careful attention to 'bangs' and 'dots')
I my example I have a simple Text Box (Text3) and a command button (Command5) on my main form. Depending on the value typed into the text box, I can make the pages on my tab control become visible or not. I named my two pages PageA and PageB in the properties (my comment in my earlier post about numbering starting at 0 does not apply here and I just managed to confuse myself by referring to it!)
I've used Select Case to get away from what could possibly be a lengthy if statement, but if you feel more comfortable, your ifs would do it also.
Select Case Me.Text3.Value
Forms![frmParent]![frmSub].Form![PageA].Visible = True
Forms![frmParent]![frmSub].Form![PageB].Visible = True
Forms![frmParent]![frmSub].Form![PageA].Visible = False
Forms![frmParent]![frmSub].Form![PageB].Visible = False