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?

 

[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

 

[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.

 

[The_Doc_Man]

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

At first, I didn't either. However, the wizards are not the end of the story. They might be just the beginning. They pre-fabricate the basics - underline BASICS - of common but tedious tasks you want to do. So they build a little bit of code. But just because a wizard built it doesn't mean you cannot come back and customize it. Treat a wizard like any other tool in the tool kit. Just learn WHEN to use it - and wnen not to do so - and you will find you have another useful and quick way to get started on something that is often rather tedious.

 

[SaranPriya-7954]

At first, I didn't either. However, the wizards are not the end of the story. They might be just the beginning. They pre-fabricate the basics - underline BASICS - of common but tedious tasks you want to do. So they build a little bit of code. But just because a wizard built it doesn't mean you cannot come back and customize it. Treat a wizard like any other tool in the tool kit. Just learn WHEN to use it - and wnen not to do so - and you will find you have another useful and quick way to get started on something that is often rather tedious.

I understand what you are saying. I just used it to create a list from a table. It was completed within seconds. Now I am trying to see if I can use colors to distinguish if a task is new (active), pending or completed off of a datasheet view. Wish me luck!

I am thinking once information is tagged as Active, the whole row of data, either background or text color should change to red.

Pending status = Yellow
Completed = Green

 

[The_Doc_Man]

For this selective tagging or a whole row, you might wish to consider the topic "Conditional Formatting" - and you should be able to look that up in this forum. If looking outside the forum, make the lookup "Conditional Formatting in Access" since other presentation programs might use that term differently.

Highlight data with conditional formatting - Microsoft Support

You can set conditional formatting rules for controls on a form or report in Access desktop databases to highlight important data.

support.microsoft.com

If you do the search I told you, this one pops up pretty quickly but there are more such references than just this one.