Autofill inside if/then statement

[Devon]

Hello

I have created a scheduling a resource allocation dbase for my team. The
database contains the following tables: tblData, tblLocation, tblVersion,
tblDate, tblActivity, tblProject, and tblSchedule. This database is for
software development.

tblData contains data about the Project. tblLocation contains information
about the various locations around the world where we have employees working
on these Projects. tblVersion contains what version of software the various
projects are using. tblDate contains the date range (Mon - Friday each week)
that the employees are working. tblActivity contains the employee name, the
activity they are working on that week, and the amount of time spent on that
activity.

Everything appears to be working the way it is intended, however the team
leads that are entering the data would like some more automation. They would
like to be able to get into the project, choose a name from the activity
table, and have it populate the activity and amount of time spent on the
activity, as most weeks should be the same.

I have created a couple of new tables (tblProject and tblSchedule) to hold
this 'automated' data. tblProject contains a field named Project that pulls
it data from the tblData above so that the Project names are exactly the
same. tblSchedule contains the employee name, as well as their activity and
the amount of time spent on the activity. Team leads would populate this
data to save them time in repeating the data on the tblActivity from week to
week.

My thought is that I would then run a query on the data from tblProject and
tblSchedule to populate a combo box on tblActivity for the employees name. I
then have under After Update Me.[Activity_Name] = Me.EName.Column(1),
Me.[Sub_Activity_Name] = Me.EName.Column(2), Me.[Level_Of_Effort] =
Me.EName.Column(3). This works great, but I would like to create an If/Then
statement to filter so that it would only pull data from tblProject and
tblSchedule depending on which project is being used in tblData. When
writing my boolean statement in the If/Then statement, it is unable to see
the tblProject and tblSchedule.

I know this might be confusing. I hope it makes sense. Please let me know
if you need any clarifications.

Thanks

Devon

 

[Piet Linden]

Hello

I have created a scheduling a resource allocation dbase for my team.  The
database contains the following tables: tblData, tblLocation, tblVersion,
tblDate, tblActivity, tblProject, and tblSchedule.  This database is for
software development.

tblData contains data about the Project.  tblLocation contains information
about the various locations around the world where we have employees working
on these Projects.  tblVersion contains what version of software the various
projects are using.  tblDate contains the date range (Mon - Friday eachweek)
that the employees are working.   tblActivity contains the employee name, the
activity they are working on that week, and the amount of time spent on that
activity.

Everything appears to be working the way it is intended, however the team
leads that are entering the data would like some more automation.  Theywould
like to be able to get into the project, choose a name from the activity
table, and have it populate the activity and amount of time spent on the
activity, as most weeks should be the same.

I have created a couple of new tables (tblProject and tblSchedule) to hold
this 'automated' data.  tblProject contains a field named Project that pulls
it data from the tblData above so that the Project names are exactly the
same.  tblSchedule contains the employee name, as well as their activity and
the amount of time spent on the activity.  Team leads would populate this
data to save them time in repeating the data on the tblActivity from weekto
week.

My thought is that I would then run a query on the data from tblProject and
tblSchedule to populate a combo box on tblActivity for the employees name..  I
then have under After Update  Me.[Activity_Name] = Me.EName.Column(1),
Me.[Sub_Activity_Name] = Me.EName.Column(2), Me.[Level_Of_Effort] =
Me.EName.Column(3).  This works great, but I would like to create an If/Then
statement to filter so that it would only pull data from tblProject and
tblSchedule depending on which project is being used in tblData.  When
writing my boolean statement in the If/Then statement, it is unable to see
the tblProject and tblSchedule.

I know this might be confusing.  I hope it makes sense.  Please let me know
if you need any clarifications.

Thanks

Devon

You could use an unbound form to gather the information from
tblProject and tblData and then create a deliberate Cartesian product
(a query where you do not join the two tables) and then filter the
result. Once you get the result you want, turn that query into an
append query and run it using CurrentDB.Execute "QueryName"

HTH
Pieter

 

[Devon]

Thanks Pieter. I will give it a try.

Devon

Hello

I have created a scheduling a resource allocation dbase for my team. The
database contains the following tables: tblData, tblLocation, tblVersion,
tblDate, tblActivity, tblProject, and tblSchedule. This database is for
software development.

tblData contains data about the Project. tblLocation contains information
about the various locations around the world where we have employees working
on these Projects. tblVersion contains what version of software the various
projects are using. tblDate contains the date range (Mon - Friday each week)
that the employees are working. tblActivity contains the employee name, the
activity they are working on that week, and the amount of time spent on that
activity.

Everything appears to be working the way it is intended, however the team
leads that are entering the data would like some more automation. They would
like to be able to get into the project, choose a name from the activity
table, and have it populate the activity and amount of time spent on the
activity, as most weeks should be the same.

I have created a couple of new tables (tblProject and tblSchedule) to hold
this 'automated' data. tblProject contains a field named Project that pulls
it data from the tblData above so that the Project names are exactly the
same. tblSchedule contains the employee name, as well as their activity and
the amount of time spent on the activity. Team leads would populate this
data to save them time in repeating the data on the tblActivity from week to
week.

My thought is that I would then run a query on the data from tblProject and
tblSchedule to populate a combo box on tblActivity for the employees name.. I
then have under After Update Me.[Activity_Name] = Me.EName.Column(1),
Me.[Sub_Activity_Name] = Me.EName.Column(2), Me.[Level_Of_Effort] =
Me.EName.Column(3). This works great, but I would like to create an If/Then
statement to filter so that it would only pull data from tblProject and
tblSchedule depending on which project is being used in tblData. When
writing my boolean statement in the If/Then statement, it is unable to see
the tblProject and tblSchedule.

I know this might be confusing. I hope it makes sense. Please let me know
if you need any clarifications.

Thanks

Devon

You could use an unbound form to gather the information from
tblProject and tblData and then create a deliberate Cartesian product
(a query where you do not join the two tables) and then filter the
result. Once you get the result you want, turn that query into an
append query and run it using CurrentDB.Execute "QueryName"

HTH
Pieter