How to display field from table that's not the form's recordsource

B

bws93222

Say I have an Access DB that stores data for 2 leagues, 10 teams, and 90
players.
Here are my 3 tables with one-to-many referential integrity respectively:
LeagueTbl
TeamTbl (w/ FK: LeagueID)
PlayerTbl (w/ FK: TeamID)

I have a form-PlayerInfo with recordsource: PlayerTbl. It has a combobox:
Player(PlayerName), and combobox: Team(TeamID/Team). I want an (uneditable)
textbox to show the League (which is associated with the specified Player's
Team). How do I display the League value on my form? ex: "BabeRuth, Yankees,
American" (I tried using the expression builder to reference the other table
but couldn't get it to work.) Thx
 
J

Jeanette Cunningham

Hi bws93222,
you can use DLookup for this.

Put the code below on the current event for the form
----------------------
Dim strCriteria As String

strCriteria = "[PlayerID] = " & Me.PlayerName

Me.NameOfTextBox = Nz(DLookup("[LeagueName]", "TheQuery", strCriteria)
-------------------------------

TheQuery needs to be a query which joins LeagueTbl with TeamTbl and
PlayerTbl and includes the ID for the player.
You can lock the textbox so that it can't be edited.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
B

bws93222

Hmm--I'm haven't advanced to VBA yet. Is there a simpler way?
--
bws93222


Jeanette Cunningham said:
Hi bws93222,
you can use DLookup for this.

Put the code below on the current event for the form
----------------------
Dim strCriteria As String

strCriteria = "[PlayerID] = " & Me.PlayerName

Me.NameOfTextBox = Nz(DLookup("[LeagueName]", "TheQuery", strCriteria)
-------------------------------

TheQuery needs to be a query which joins LeagueTbl with TeamTbl and
PlayerTbl and includes the ID for the player.
You can lock the textbox so that it can't be edited.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


bws93222 said:
Say I have an Access DB that stores data for 2 leagues, 10 teams, and 90
players.
Here are my 3 tables with one-to-many referential integrity respectively:
LeagueTbl
TeamTbl (w/ FK: LeagueID)
PlayerTbl (w/ FK: TeamID)

I have a form-PlayerInfo with recordsource: PlayerTbl. It has a combobox:
Player(PlayerName), and combobox: Team(TeamID/Team). I want an
(uneditable)
textbox to show the League (which is associated with the specified
Player's
Team). How do I display the League value on my form? ex: "BabeRuth,
Yankees,
American" (I tried using the expression builder to reference the other
table
but couldn't get it to work.) Thx
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Yes, you can include the league in the query that is the record source for
the combo with teams.

Add an extra column to your team combo.
Put an unbound text box on your form called txtLeague

In the control source for txtLeague put
=Me.Team.Column(n)

Note: combos have zero-start numbering -
the first column is called column(0), the next is column(1) etc

Put a value for n that fits your query.
The column n can have its width set to 0, to hide that column when the list
drops down.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


bws93222 said:
Hmm--I'm haven't advanced to VBA yet. Is there a simpler way?
--
bws93222


Jeanette Cunningham said:
Hi bws93222,
you can use DLookup for this.

Put the code below on the current event for the form
----------------------
Dim strCriteria As String

strCriteria = "[PlayerID] = " & Me.PlayerName

Me.NameOfTextBox = Nz(DLookup("[LeagueName]", "TheQuery", strCriteria)
-------------------------------

TheQuery needs to be a query which joins LeagueTbl with TeamTbl and
PlayerTbl and includes the ID for the player.
You can lock the textbox so that it can't be edited.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


bws93222 said:
Say I have an Access DB that stores data for 2 leagues, 10 teams, and
90
players.
Here are my 3 tables with one-to-many referential integrity
respectively:
LeagueTbl
TeamTbl (w/ FK: LeagueID)
PlayerTbl (w/ FK: TeamID)

I have a form-PlayerInfo with recordsource: PlayerTbl. It has a
combobox:
Player(PlayerName), and combobox: Team(TeamID/Team). I want an
(uneditable)
textbox to show the League (which is associated with the specified
Player's
Team). How do I display the League value on my form? ex: "BabeRuth,
Yankees,
American" (I tried using the expression builder to reference the other
table
but couldn't get it to work.) Thx
Click to expand...
Click to expand...
Click to expand...
 
B

bws93222

Thanks. That helped me solve my problem. I found it easiest to just run and
save a standalone query containing needed fields from all 3 tables; then use
form wizard to generate a form based thereon; then add combo boxes and remove
redundant textboxes as needed.
--
bws93222


Jeanette Cunningham said:
Yes, you can include the league in the query that is the record source for
the combo with teams.

Add an extra column to your team combo.
Put an unbound text box on your form called txtLeague

In the control source for txtLeague put
=Me.Team.Column(n)

Note: combos have zero-start numbering -
the first column is called column(0), the next is column(1) etc

Put a value for n that fits your query.
The column n can have its width set to 0, to hide that column when the list
drops down.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


bws93222 said:
Hmm--I'm haven't advanced to VBA yet. Is there a simpler way?
--
bws93222


Jeanette Cunningham said:
Hi bws93222,
you can use DLookup for this.

Put the code below on the current event for the form
----------------------
Dim strCriteria As String

strCriteria = "[PlayerID] = " & Me.PlayerName

Me.NameOfTextBox = Nz(DLookup("[LeagueName]", "TheQuery", strCriteria)
-------------------------------

TheQuery needs to be a query which joins LeagueTbl with TeamTbl and
PlayerTbl and includes the ID for the player.
You can lock the textbox so that it can't be edited.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Say I have an Access DB that stores data for 2 leagues, 10 teams, and
90
players.
Here are my 3 tables with one-to-many referential integrity
respectively:
LeagueTbl
TeamTbl (w/ FK: LeagueID)
PlayerTbl (w/ FK: TeamID)

I have a form-PlayerInfo with recordsource: PlayerTbl. It has a
combobox:
Player(PlayerName), and combobox: Team(TeamID/Team). I want an
(uneditable)
textbox to show the League (which is associated with the specified
Player's
Team). How do I display the League value on my form? ex: "BabeRuth,
Yankees,
American" (I tried using the expression builder to reference the other
table
but couldn't get it to work.) Thx
Click to expand...
Click to expand...
Click to expand...
 
Top