Referencing a field from a record in a datasheet on a tabbed control

[bentheimmigrant]

So, I have the current setup:

Main form: Main
Tabbed control: NavigationSubform
Tabs: Reports, Calculations, Drawings, Datasheets
Each tab holds a subform with the name Tab_<tab name>

The subforms are all in datasheet view.

I would like to have a control box on the main form which, when you click on a tab and select one of the records on that tab, it receives the field (Doc_Number) of the selected record in the selected tab.

Is there a generic way to do this, so that I can add more tabs in the future if they're needed (i.e. query "Doc_Number" from the currently selected tab)?

I think I want something similar to what was was discussed here regarding a combobox:
forums/showthread.php?t=212911 (can't post links yet - that's a thread on these forums)

So I started with:
[Forms]![Main]![NavigationSubform].[Form].[Doc_Number]
(I don't think brackets matter, though?)

Any help would be appreciated.

 

[Minty]

Tabs on a form are only effectively real estate extensions of the main form, you don't need to refer to them specifically.

To achieve what you want would require you to update a unbound field on the main form on the current event of the sub form(s). What you should bear in mind is that if you navigate from one tab to another you would need to requery the subform otherwise you could see the ID from the sub form you have just navigated away from.

 

[bentheimmigrant]

OK, I think I follow. So just to (hopefully!) reword what you're saying, I need to write a bit of VBA so that OnClick I send the text from the selected field and write it into a text box.

(As an aside, I specified the tab names in case the solution involved querying which tab was selected to get the name and build the query etc.)

 

[arnelgp]

On OnCurrent of each subform:

Private Sub Form_Current()
Me.Parent.Parent!yourControlbox = Me!Doc_Number & ""
End Sub

 

[Minty]

Yes - I think you have it. You probably need two bits of code;
1. OnCurrent Event in each sub form - I wouldn't use on_click as you would have to add it to any field in the datasheet that could be clicked.
2. OnFocus on the tab to requery the new current subform or maybe clear the unbound control depending on it's purpose.

 

[bentheimmigrant]

For some reason that give an "invalid use of me" error (I know how it feels!).

I've just been more explicit with it, and it works:

Public Function Select_Doc()
Forms!Main!Doc_No_Box = Forms!Main!NavigationSubform!Doc_Number.Value
End Function

 

[bentheimmigrant]

Yes - I think you have it. You probably need two bits of code;
1. OnCurrent Event in each sub form - I wouldn't use on_click as you would have to add it to any field in the datasheet that could be clicked.
2. OnFocus on the tab to requery the new current subform or maybe clear the unbound control depending on it's purpose.

OnCurrent did it... OnFocus on the tabs means that it tries to update with the currently selected record (i.e. the item on the previous tab).

Thanks guys... I shall now see if I can work out how to mark Solved!