Display Multiple Data Fields based on drop down selection

P

Peter

I have a subform that I want to use to track some resource-specific
information relative to a project. I have a TeamMember table that have
foreign-key fields that link to the Resource table (containing much of the
needed data) and the Project Table.
I've seen this done with a Classroom Database I downloaded from the
Microsoft Templates site but cannot get it to work for mmy database - ugh.
Basically I want the user to be able to select the Resource Name from the
Subform drop down field and have the DeskPhone, MobilePhone, SkypeName,
Location, and Company fields automatically populate in the subform with the
data stored on the resource table. I also want to allow the user to update
the data within that subform and have that updated data change reflect in the
resource table; I.E.: if the user's DeskPhone needs to be changed, the user
can change that in this subform without having to go to the Resource table or
another Resource contact form.
So how do I do this?
 
W

Wolfgang Kais

Hello Peter.

Peter said:
I have a subform that I want to use to track some resource-specific
information relative to a project. I have a TeamMember table that
have foreign-key fields that link to the Resource table (containing
much of the needed data) and the Project Table.
I've seen this done with a Classroom Database I downloaded from the
Microsoft Templates site but cannot get it to work for my database
- ugh. Basically I want the user to be able to select the Resource
Name from the Subform drop down field and have the DeskPhone,
MobilePhone, SkypeName, Location, and Company fields automatically
populate in the subform with the data stored on the resource table.
I also want to allow the user to update the data within that subform
and have that updated data change reflect in the resource table;
I.E.: if the user's DeskPhone needs to be changed, the user can
change that in this subform without having to go to the Resource
table or another Resource contact form.
So how do I do this?
Click to expand...

Base your subform on a query that joins the TeamMember table witht
the Resource table. It should contain the ProjektID, ResourceID,
other relevant data from the TeamMember table, and the fields from
the Resource table: DeskPhone, MobilePhone, SkypeName, Location and
Company.
If you enter a ResourceID (for example by selecting a Resource Name
in a combo box), Access will immediately show the data from the
Resource table and allow for editing.
 
P

Peter

Thanks! The query worked - but as a follow-up question:

Eventually I want to be able to run reports of who is assigned to which
project and what role they are playong while they are assigned to the
project. It is because of this that I thought I needed to create a
TeamMembers Table that would store data like the projectname, resourcename
and projectrole information.

Would I create the subform by using the query (which by the way you were
correct - gives me everything I need):

SELECT Resource.DeskPhone, Resource.MobilePhone, Resource.Company,
Resource.Location, Resource.SkypeName, TeamMembers.ProjectTitle,
TeamMembers.ResourceName
FROM Resource INNER JOIN TeamMembers ON Resource.ResourceID =
TeamMembers.ResourceName;

And then add a combo field to that subform that looks up the appropriate
field (projectrole) on the TeamMember Table?

OR

How do I get the data from the query into the TeamMember table?
 
P

Peter

Nevermind - I actually figured it out - by extending the query - we are all
good to go now!
 
Top