Solved Load table information unto 12 tabs when a combo box item is selected

[SaranPriya-7954]

I am trying to create a form based on the tab control. I have 12 tabs, each with various controls and I am trying to filter these tabs using a combo box. For example "When I click on the combo box and select " A person" I want the tabs to be filled with the associative information.

I have tried this set of codes thus far:

Private Sub cboProjectName_AfterUpdate()
Dim SQL As String

SQL = "SELECT tblProjectFramework.ProjectID, tblProjectFramework.ProjectName, tblProjectFramework.ProjectManager, tblProjectFramework.ProjectSponsor, " _ & "tblProjectFramework.BusinessAnalyst, tblProjectFramework.ProjectSummary, tblProjectFramework.CurrentState, tblProjectFramework.MissionStatement, " _ & "tblProjectFramework.FutureState, tblProjectFramework.ProjectDate, tblProjectFramework.ProjectFilePath, tblProjectFramework.A1, tblProjectFramework.DO, " _ & "tblProjectFramework.DA, tblProjectFramework.DAS, tblProjectFramework.Requisition, tblProjectFramework.PurchaseOrder, tblProjectFramework.ProjectDocInfo," _ & "tblProjectFramework.ProjectStatus, tblProjectFramework.FinancingSource, tblProjectFramework.FinancingCost FROM tblProjectFramework;"
Me.Form.ActiveControl.RecordSource
Me.Form.Requery

End Sub

Private Sub cboProjectName_GotFocus()

Me.cboProjectName.RowSource = "select ProjectName from qryProjectFramework"

End Sub

 

[June7]

I suggest not using code to set form RecordSource if can avoid it.

Use code to set form Filter and FilterOn properties or WHERE argument of DoCmd.OpenForm.

Why use code to set combobox RowSource?

Nothing in displayed code is filtering anything. Review http://allenbrowne.com/ser-62.html

 

[arnelgp]

on each Subforms, add Master/Child Link fields.

it is also better that the "joining" field is a Numeric field/value.

 

[SaranPriya-7954]

I am using the tab control.

Can I use an event to load the Tabs with information from my query. Then use an event to filter using the combo box to obtain the information.

Can you demonstrate how this can be done?

 

[The_Doc_Man]

You are new to Access so you are asking a question that appears to make an incorrect assumption. The tab control itself contains no data at all. It provides a framework within a form for selectively displaying other controls that contain the data you want. Think of a tab control as providing multiple blank slates for form-building. However (and this is important), ALL controls on a bound form, in or out of a tab, are based on the same record.

You might (repeat MIGHT) consider using that combo box to select a record to be loaded into a BOUND form in which case you would only need to make a table the form's .RecordSource - which instantly gives you all of the values in the table's record the moment you make the selection.

There is an option using a combo box that if you build it using the appropriate Access wizard, you can ask it to select a value from a table as a way of selecting a record corresponding to that value. If ALL of the controls in all of the tabs point back to the fields from the table, then when you make the selection from the combo, everything loads up almost simultaneously.

The next question is a hold-over from your previous question in another thread. If you select the tab and it was built correctly, then all of the controls become visible when you select the tab. If you change tabs, a different set of controls becomes visible. If they were ALL bound to the record selected by the combo box, then they are all loaded regardless of which tab is "up" at the moment. If you change tabs you can see a different group of controls - but they are all bound to the same record, the one that was selected by the combo box.

For most people the trick is getting the form's controls onto the tabs, because very often the control you dropped onto the tab actually goes "behind all of the tabs" - so this part is tedious. But I've used it and it works just fine.

 

[arnelgp]

click on the subform on design view.
on Property Sheet->Data, see the Master/Child Link Fields.

 

[June7]

A tab control is a way to organize many controls (including subform containers) within a smaller area than they might require otherwise. Tab control is just a 'holder' of other controls. Disregard the tab control when referencing controls in code. A tab control does not impact how form and controls are bound to data.

Now, a Navigation Form is a different animal. It can look a lot like a tab control but it is not.

 

[SaranPriya-7954]

Arnelp, it is not as simple as creating a master child relationship. I built Dynamic menus whereby each tab corresponds to the totality of information for one user. A particular user can be selected by using the combo box. What I am having trouble with in particular is trying to create the relationship with the combo box (which has users) and the tabs which have specific information for the particular user.

I would like to filter on that user and the particular information on each tab will be displayed.

I have succeeded in finding the answers to all my other questions through trial and error, but this particular challenge is difficult I tried various vb code to no avail. If someone can show an example it would be much appreciated. Anyhow thank you all for your efforts.

 

[June7]

Perhaps you should provide your db for analysis. Follow instructions at bottom of my post.

The number of users will always be 12?

Perhaps what you need to do is apply filter to form and set visibility of tab control pages.

 

[The_Doc_Man]

Do not focus on the tabs. They are getting in your way of understanding. Tabs on a form are like short room dividers in an office - they just wall off an area but nothing really happens on them.

The bound form has controls based on/corresponding to the fields in an underlying record. If you navigate the record using the navigation control, the bound controls change as the record changes. This part is pretty straight-forward.

Trick #1 - using the ComboBox wizard, you can ask Access to build the form so that selecting an entry in the combo box jump-navigates to the selected record.

Trick #2 - You can choose to put different controls on different tabs but ALL controls on the form (without regard to which tab contains them) are always bound to the current record OF THE FORM. Tabs are bound to the form - but not to the record.

The behavior you have described as what you want sounds exactly like what I just described unless there is some part of your description that I missed.

 

[arnelgp]

Arnelp, it is not as simple as creating a master child relationship.

you need to test it first (all are possible during pandemic?)

are the infos (on each tabs) on same 1 table? or they are on separated tables?

 

[Uncle Gizmo]

You might find my blog here useful, where I demonstrate how to split one table into several subforms.

Split a Large MS Access Datasheet in to 3 Smaller Datasheets - Nifty Access

Split a Large MS Access Datasheet in to 3 Smaller Datasheets You've got a large "VERTICAL" List of Data in datasheet form, you might prefer to have it spread out across the screen instead of constantly having to page up and down the list. In this set of videos I demonstrate two ways of implementing

www.niftyaccess.com

I demonstrate the process in steps from a simple setup to more advanced, using VBA.

 

[Uncle Gizmo]

This video doesn't show the records (subforms) mounted on tabs, but you could just as easily put them on tabs and it would work fine I'm sure.

 

[SaranPriya-7954]

you need to test it first (all are possible during pandemic?)

are the infos (on each tabs) on same 1 table? or they are on separated tables?

They are on separate tabs

 

[arnelgp]

They are on separate tabs

i know, but are the infor on the tabs from Separate tables?
or the info is from same table?

 

[June7]

Did you see post #9?

 

[SaranPriya-7954]

Did you see post #9?

I will clear the info and post

 

[SaranPriya-7954]

i know, but are the infor on the tabs from Separate tables?
or the info is from same table?

They are on separate tables

 

[Pat Hartman]

I think you are misunderstanding the purpose of the tab control. Each tab is not intended to show data from a different record. As the others have mentioned, ALL controls on a form are bound to the FORM's RecordSource. The tab control has no RecordSource and it is not a bound control.

You can accomplish what you want by adding twelve copies of a subform, each selecting a different record. I don't recommend this since it has no flexibility. If you add more people, you need to add more tabs and more copies of the subform and there are limits, so, don't start down this path to begin with. Show us your design idea and we'll offer alternatives. The database needs data so if you delete all the data, we won't be able to see how things should work. If the data is sensitive, obfuscate it instead. Make a backup of the database so you don't accidentally update real data and just run update queries. A simple one changes all the last names to just the first two characters. You can replace phone numbers and street addresses with one value but at least a few fields need different values as they would have in real data.

Here is a picture of a form I built to compare properties. It was a royal pain to get everything aligned. It does up to 5 properties at a time. If I wanted it to do more, I would need to modify the form and add more subforms.

 

[SaranPriya-7954]

Indeed, no more tabs for multiple tables, I acquiesce.

I've been up changing my thought process on how to address this problem. I have decided to use another alternative.
I am using the traditional method of subforms and binding the information directly. I have also changed my ERD to encompass more attributes. This process has been arduous, however, I am quite aware that I have grown technically in Access through this process . Thank you all.

P.S. I don't like using the wizards.