Populating a report

[NycNessyness]

Hello Everyone! I'm running into a bit of a pinch and have tried using macros and queries, which I know now don't work for pulling data within a field. It looks like I'll have to use VBA to accomplish what I would like to do but have no experience in using it.

I have a table called Main. It has 5 columns in this order. ID(Autonumber), Employee(Text), Date of Hire(Date/Time), Job Title(Number), Manager(Number), Tasks(Text). The Job Title and Manager columns are actually both lookups. I have a table called Manager that the Manager look up pulls from and a table called JobTitles that the Job Titles look up pulls from. I also have two additional tables. One called Analyst Tasks and the other Manager Tasks. Each of these tables have different tasks depending on the job selected.

What I'm trying to do is create a form with all the columns of the main table, excluding the ID, so that when a user selects a Job Title from the look up, such as Analyst, it auto populates the Tasks column, within the Main table with all the fields in the Analyst Task table(Want all the tasks to be within one scroll able field).

So in all I want to be able to link the lookup column(Job Title) based on what is selected in the field, to populate the Tasks column in the Main table with the entire Tasks column from the Analyst or Manager Task table. If anyone has any insight on the possibilities of this, please let me know. I'm still new to using Access and want to incorporate this within a Sharepoint site. Thanks in advance!

Additional: I named this report but what I'm trying to do is have a form update the table I mentioned then in the end create a new report for each employee added. Sorry for the confusion.

 

[Bryan]

Create a subform in the datasheet view for the tasks field and then drag it into your main form.

 

[NycNessyness]

Thanks a lot for the information Bryan. I created a Job Title Subform, which pulls from one of my Task Subforms currently. For this example I used the ManagerTasks Subform. The problem I'm having now is that if I place both my ManagerTasks Subform and AnalystTasks Subform within the JobTitles subform, it only pulls data from one and links the id with the corresponding id of the other form.

The Job Title Task Form is like this:
ID|Job Title
1|Analyst
2|Manager

The Analyst Task Form is like this:
ID|Task
1|Welcome
2|Meet Team
3|Lunch

The Manager Task Form is like this:
ID|Task
1|Set up Office
2|Meet the VP

The Job Title ID is linking the fields by the ID and only allowing one task. It looks like this:
ID|Job Title
1|Analyst
ID|Task
1|Welcome

2|Manager
ID|Task
2|Meet Team

What I want it to look like is like this:
1|Analyst
ID|Task
1|Welcome
2|Meet Team
3|Lunch
2|Manager
ID|Task
1|Set up Office
2|Meet the VP

An alternative I could think of is keeping both the analyst and manager task on the same table and same form and sort by the job title, which would look like below:
ID|Job Title|Task
1|Analyst|Welcome
2|Analyst|Meet Team
3|Analyst|Lunch
4|Manager|Set up Office
5|Manager|Meet the VP

Which way would you all suggest I go with. I was thinking for editing purposes, in case we may want to update the tasks later on, it would be neater to have two separate tables but if Access can't connect the two in the way I'm trying to do it, then I'll go with the latter. Sorry for the long explanation. I just want you all to know I'm trying and understand what I'm trying to accomplish but don't know how to go about it. Thanks again!

 

[Bryan]

I got a bit lost on your explanation about what is not working, so I'm going to try this by explaining what I think you need.

You have a tblMain which contains identifying information about tasks. The job title and manager are identified by a code and have a lookup, which is fine. The tasks field is on the ONE side of a O to M relationship with tblAnalystTasks and tblManagerTasks. You'll establish relationships with fields such as AnalystTaskID and ManagerTaskID

Your frmMain contains fields from tblMain. What you want is to select a task and then have the corresponding data relating to the data in both tblAnalystTasks and tblManagerTasks displayed.

Create a frmAnalystTasks_sub in the datasheet view that contains the fields you want displayed. Drag it into frmMain and see if it works.

I THINK you can do the same for the other table so you have two subforms on the same main form. I would NOT combine the two into one table.

Is it the 2nd subform that isn't working?

 

[NycNessyness]

I've attached the tables. I have a form called New User, which allows someone to enter in the data of a new employee that is joining. This form contains the employee name, date of hire, job title and manager assigned to the new employee. Once this information is entered, based on the job title selected, I want a full list of tasks to then be automatically generated and transferred to the Task field in the Main form. An example would be as below.

Main|Employee Name|Date of Hire|Job Title|Manager|Tasks
1|John Adams|5/25/2012|Manager|Megan Payne|1. Set up Office
                                           2. Meet the VP
2|Mary Poppins|5/25/2012|Analyst|Larry Johnson|1. Welcome
                                               2. Meet Team
                                               3. Lunch

So the user enters the information in the New user form, which contains only the four fields I mentioned but based on the job title that is selected, it automatically populates the Tasks field, within the Main table with a list of tasks. Thanks for helping me again. Greatly appreciated.

 

[Bryan]

I am on an iPad this weekend, so I can't look at your attachment.

So if you want JobTitle to determine what is displayed in the subform, why is there a tasks field in tblMain?

So let's try this again:

You have a tblMain which contains identifying information about an employee. The job title and manager are identified by a code and have a lookup, which is fine. The JobTitle field is on the ONE side of a O to M relationship with tblAnalystTasks and tblManagerTasks. You'll establish relationships with fields such as AnalystTaskID and ManagerTaskID

Your frmMain contains fields from tblMain. What you want is to select a JobTitle and then have the corresponding data relating to the data in both tblAnalystTasks and tblManagerTasks displayed.

Create a frmAnalystTasks_sub in the datasheet view that contains the fields you want displayed. Drag it into frmMain and see if it works.

I THINK you can do the same for the other table so you have two subforms on the same main form. I would NOT combine the two into one table.

In your previous post you taled about transferring data to your main form. What you really meant is to have the data displayed in your subform. You cannot transfer data to a form and you SHOULD NOT be appending task data to tblMain. If that is what the tasks field is for in tblMain, you are taking the wrong approach, as that would violate the rules of normalization.

 

[NycNessyness]

Thanks again Bryan. I am able to insert both forms within the Job Title sub form. I never had my tables linked correctly. I had to create a Job Title column in both the AnalystTasks and ManagerTasks Table and setting the primary key to the Job Title column in the Job Title Table. So Job Title(one) has a link to AnalystTasks(Many) and ManagerTasks(Many). The problem I'm running into now is that the Job Title table now prompts me to add a subdatasheet to the table and only allows me to pick one table instead of two. Do you know of any solution to have this pull from both tables successfully? Thanks!

 

[Bryan]

. . . I am able to insert both forms within the Job Title sub form. . . . . The problem I'm running into now is that the Job Title table now prompts me to add a subdatasheet to the table and only allows me to pick one table instead of two.

I don't understand what you are doing.

1. frmJobTitle should not be a subform. It should contain two subforms, frmAnalystTasks_ sub and frmManagerTasks_sub.

2. What do you mean by the "Job Title table now prompts"? What are you doing when you get the prompt?