creating relation between combo box in ms access

Question

Hey there,

I'm developing this batabase for charity workers' demography at a church. However i can't figure out a way to link multiple combo boxes such that the option from one combobox would autofill a certain significant option from another combobox.

example; suppose you have two different comboboxes in different fields of a ms access table, ie; Box-A that has "Team Names" -barcelona,manchester,arsenal,madrid. Box-B that has "Team Captains" - messi,ronaldo,pogba,ozil. now i want to reate a relation suh that after a selection of a team from Box-A would cause an Autoselection and autofil of the teams respetive captain in Box-B even without having to select anything on box B

Answer 1

I think this should be a process of push rather than pull, Duane.  The captain of a team will change over time, so a referencing table, MatchTeams say,  should have columns for both.  In the AfterUpdate event procedure of the control bound to the Team column the current value of the Captain column in the referenced Teams table would be assigned to a Captain column in the referencing table with:

    Me.Captain = Me.cboTeam.Column(1)

It's analogous to the common example of a unit price of a product where both the Products table and OrderDetails table would have a UnitPrice column, in each case functionally determined by the table's key.

Answer 2

Everywhere, tho coding i guess. and i cant find the menu you code in

Answer 3

I think I was fairly detailed. There is only one combo box and then a text box to display the captain. 

At what part are you having an issue?

Answer 4

THANKS ALOT.. 

How ever may you Please instruct me step by step i'm quite unfamiliar with ms access and how to go about all that

Answer 5

Your Team Name combo box should have a Row Source that includes the captain as one of the columns like:

SELECT TeamName, Captain 

FROM SoccerTeams

ORDER BY TeamName;

Your team combo box:

Name: cboTeam

Row Source: [See above]

Column Count: 2

Bound Column: 1

Column Widths: 1, 0

Then add a text box to display the captain:

Control Source: =cboTeam.Column(1)