Hello,

Here is an analogous example of my project (I thought a baseball leage analogy would be simple to setup and explain):

There are two tables:
Teams
Players

The 'Teams' table has the following (10) fields:
TeamName
Player1
...
...
Player9

The 'Players' table has player stats:
Name,Weight,height,etc.

I have two input forms - one for each table:
frmPlayers, frmTeams

Here is my dilemma ( I have been going round and round, but have been unable to arrive at what must be a simple solution!):

On my frmPlayers I am trying to create a (read-only) textbox (txtTeam) that displays - from the 'Teams' table - the team to which the player has been assigned.

Should I set the 'Record Source' of my frmPlayers to the 'Players' table directly? or should I be referencing a select query based on the players table?

What is frustrating is that I can run a query by itself, and it appears to work. but when I try to set a query as the control source for txtTeam, It shows either #Name? - or it is blank.

Any help is appreciated..

Jon

The way you have described your tables is an incorrect way to structure tables for a database application. You have "repeating groups" which violates normalization standards. Here are some links that will help you with proper table design:

:

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Naming Conventions - Hungarian Notation

So ... applying some of the above priciciples ... you tables should look like this:

The 'Teams' table (minimum):
TeamID (Autonumber - PrimaryKey {PK for short})
TeamName

The 'Players' table:
PlayerID (Autonumber - PK)
TeamID (Number/Long - ForeignKey {FK for short}) ... the FK is used to create a relationship between which players are on which team)
FirstName
LastName
Weight
Height


HTH ...

Brent,

Thanks for the quick reply. I've gotta run now - but I'll take a look over the weekend. I think I already see the error of my ways...

Jon

You can not set the control source of a text box to an SQL statemetnt or query name.


What will work is usoing the DLOOKUP as the control source to reteive the info yoo need.

=Dlookup("[TeamName]", "[tblTeams]","TeamID="&Me.TeamID)

Is it a given then that if I move or delete a player from the 'teams' table that I must also go to the 'players' table and modify the TeamID?

Thanks,
Jon

Jon,

Have not looked at this for quite some time did not even finish but maybe get you on the right track, Names,Games, etc. can be adapted to your circunstances.

That works.

What would be helpful of course is when player changes teams - from the team form - that the TeamId is updated at the same time. I've struggled with this now - but can't seem to get my arms around it.

If you take a look at the attachment you will see that I've tried to create a public variable on any of the cbo_change events and then pass that to an update query - no luck tho...

Any help is appreciated!

Jon

PS. Please keep in mind that this DB is just a model - my actual DB is not related in any way to baseball...

Your structure is still way off! If you had it set up the right way, then it would be easy to move a player to a different team with writing any code!

tblTeams
TeamID
TeamName

tblPlayers
PlayerID
TeamID
PlayerFName
PlayerLName
etc...

This was suggested to you earlier...you should have implement this structure!

Here, I attached an example of how it should work!
Check out the Players Form, and also the Teams form.

Are you suggesting that if I have a dozen other fields in tblTeams that I should move them out into another table?

I.e. let's say I also have the following tblTeam fields: MascotName, US(yes/no), HomeOfficeAddress1, HomeOfficeAddress2, HasCheerleaders(yes/no), etc, etc.

Sorry for the confusion...

No. You had Player1, Player2, Player3, etc...in the Teams table and that breaks normalization rules! You can put anything about the team in the Teams table, such as Team Mascot, and the address...
Maybe you should read up on normalization before going further...

Sorry again, I should have been more clear. Yes, absolutely, if I was the one creating this database from scratch, there would be no issue.

Unfortunately, what I am trying to work with is a number of backend DB's that are linked to a front end with an extensive amount of VB already built-in.

Instead of trying to explain this kluge, I thought I would try using my fictitious baseball DB as an example of what I'm attempting to do - for this one instance - instead of restructing 2 DB's and alot of VB code...

.....later...

In the end I settled for the following in frmPlayers:

Function GetTeam()
Dim Db As DAO.Database
Dim RS As DAO.Recordset
Dim i As Integer, strTeam As String

Set Db = CurrentDb
Set RS = Db.OpenRecordset("Teams")

Do Until RS.EOF
For i = 1 To 10
strTeams = "player" & i
If RS.Fields(strTeams) = Player Then
strSource = RS!TeamName
Exit Do
End If
Next i
strSource = "Not Assigned"
RS.MoveNext
Loop
GetStation = strSource
RS.Close
Db.Close
End Function

Thanks for your input.
Jon