Problems Creating Combo Box Based On Combined Primary Keys

[Marcia]

I do not understand how to create a combo box to select an option and
display the result in ONE field when the table that I am drawing the
information from is based on TWO concatenated primary keys.

My tables are:

tblBallFields:
fldSchoolID Combined Primary Key
fldBallFieldNumber Combined Primary Key

tblSchools
fldSchoolID Primary Key
fldSchoolName

The above two tables are joined on the fldSchoolID field.

I want the user to be able to choose the "BallField" through a
drop-down list that displays the School NAME (not the SchoolID), so I
created a query based on the fldSchoolID and fldBallFieldNumber from
the tblBallFields, and the fldSchoolName from tblSchools).

I want the chosen result to be stored in the fldPracticeFieldID field
in my tblTeams table.

I have set the combo boxes' Control Source to fldPracticeFieldID.
The Record Source is:
SELECT qryFrmTeam_PracticeField.fldSchoolID,
qryFrmTeam_PracticeField.fldSchoolName & " - Field " &
qryFrmTeam_PracticeField.fldBallFieldNumber FROM
qryFrmTeam_PracticeField;
The bound column is set to "1"

My problem is: When I select a field, it only stores and displays
the result based on the first combined primary key (fldSchoolID). In
other words, even though School "A" has four ballfields, it only
stores and displays "School A - field number 1"… it ignores fields
2-4.

What am I doing wrong?

Thanks!

Jessi

 

[chris]

Your problem is that you want the combo box to store a
value that it does not "know" about. That is, the value is
not a separate item in the combo's record source.
By setting the bound column to 1, you are storing the
first column of the record source query (fldSchoolId)

Change your query to ...

SELECT
qryFrmTeam_PracticeField.fldBallFieldNumber,
qryFrmTeam_PracticeField.fldSchoolID,
qryFrmTeam_PracticeField.fldSchoolName & " - Field " &
qryFrmTeam_PracticeField.fldBallFieldNumber FROM
qryFrmTeam_PracticeField;

 

[John Vinson]

I have set the combo boxes' Control Source to fldPracticeFieldID.
The Record Source is:
SELECT qryFrmTeam_PracticeField.fldSchoolID,
qryFrmTeam_PracticeField.fldSchoolName & " - Field " &
qryFrmTeam_PracticeField.fldBallFieldNumber FROM
qryFrmTeam_PracticeField;
The bound column is set to "1"

My problem is: When I select a field, it only stores and displays
the result based on the first combined primary key (fldSchoolID). In
other words, even though School "A" has four ballfields, it only
stores and displays "School A - field number 1"… it ignores fields
2-4.

What am I doing wrong?

Storing fldSchoolID in the Practice Field ID, instead of storing
fldBallFieldNumber!

Change the first field to fldBallFieldNumber and you should be OK.

 

[Marcia]

Thanks, people! I get it now.

Jessi