autofill based on if/then statement...

[Devon]

Hello

I have been working on a database for the past few weeks and I am stumped by
the final step.

I am creating a project resource allocation database and need some help.

The database has gotten quite complicated, and all is working well, but the
users would like a little more automation. So, in an effort to make it less
complicated, I will describe only those areas that are necessary.

I have a form (Main_Form) that contains data relating to the project,
including the Project Name (Project_Name). This form has a couple of
subforms, one of which is for entering the Activities (what each person
working on the project is working on). The users would like to have the
Activites subform be automated where they would choose the person's name from
a combo box and it would populate the remaining fields on this subform.

I then created a tblProject and tblSchedule tables to store this data. So
for example, the User would be able to go into a form and subform linked to
tblProject and tblSchedule and enter all the possible schedule activites by
person for each Project. For example, for Project A, Devon would work on
Activity A for 20 hours and Activity B for 20 hours.

I would like to set up an If/Then statement in the combo box for the
Activites Subform that says
If Project = Project_Name Then
Me.[Activity_Name] = Me.EName.Column(1)
Me.[Sub_Activity_Name] = Me.EName.Column(2)
Me.[Level_Of_Effort] = Me.EName.Column(3)

It is possible that a person could work on multiple projects at the same
time. So if there are multiple projects in tblProject and tblSchedule for a
person, I want the combo box on Activites_Subform to only show activities for
the project where the data is currently being entered.

I hope this makes sense. My problem really is should an If/Then statement
be used or some sort of DLookup? If I use the If/Then statement, how do I
compare the data to a form/table that isn't being used?

Any assistance would be greatly appreciated.

Thanks

Devon