have field in a form populate data from a seperate table

[nwatterson]

I have a large form 'All Projects' based on a source table. This 'All Projects' form has a field where multiple selections can be made and the form currently displays 1 field from the underlying 'Applications' table with all the selections made. This creates a 1 project to many applications scenario.

Most of the data for the applications is static except for field 'L2 primary' which can change for every project. As an example, on project 1 for application LEAN the person working it could be john smith, but on project 2 for application LEAN the person working it could be mike jones. So I created another table 'Tbl_project_Review' where the application/s can be selected and the person working it can be typed in. There is another field in this table 'Tbl_project_Review' for project Manager which needs to auto populate from the source 'CBTapplication' table (static).



I need help in 2 areas. I need to know how to automatically pull in the project manager from the application table.

Then, I want to be able to dbl click 'CBT Application' field in the form 'All Projects' to open up the form 'Tbl_project_Review', where I can select all of the applications and enter the person working the application, and have the 'L2 primary' auto populate. once the data has been entered and the form closed, I need the field 'CBT Application' to display just the application names selected in the form ''Tbl_project_Review'.

Hopefully what I am looking for makes sense. From a process flow it makes sense, I am just unsure where to really get started with connecting this all together.

Thanks in advance for any guidance you can provide.

Neil

[June7]

Why duplicate the Project Manager info to another table? 'Auto population' makes me think you are duplicating data between tables - which is usually a bad practice.

[nwatterson]

Hi June, good point and not necessary when I think about it. The key here is to be able to select the multiple applications for each project (which you already helped me with), and store the L2 primary with each application for that specific project. And then with that, show the applications back in the main project form.

Thanks, neil

[June7]

So this is resolved?

[nwatterson]

No June, I still need to create what I referenced in my post excluding having the L2 manager field which already exists.

I have a large form 'All Projects' based on a source table. This 'All Projects' form has a field where multiple selections can be made and the form currently displays 1 field from the underlying 'Applications' table with all the selections made. This creates a 1 project to many applications scenario.

Most of the data for the applications is static except for field 'L2 primary' which can change for every project. As an example, on project 1 for application LEAN the person working it could be john smith, but on project 2 for application LEAN the person working it could be mike jones. So I created another table 'Tbl_project_Review' where the application/s can be selected and the person working it can be typed in.

I want to be able to dbl click 'CBT Application' field in the form 'All Projects' to open up the form 'Tbl_project_Review', where I can select all of the applications and enter the person working the application. Once the data has been entered and the form closed, I need the field 'CBT Application' to display just the application names selected in the form ''Tbl_project_Review'.

Thanks, Neil

[June7]

Sorry, none of that makes sense to me.

Maybe you need to provide pictures.

[nwatterson]

I believe you still have a copy of the database I provided. I'll try and provide a better explanation and if you still need pictures I'll try that way too.

In the form 'frm all projects', there is a field called 'CBT applications'. I want to be able to dbl click that field to open another form in datasheet view for data entry. This other form is called 'frm_project-review'.
'frm_project_review' is a new table / form that is not in the database I provided.
'frm_project_review' should have fields as below:
Field 1 - ID / primary key
Field 2 - drop down selection from 'qry CBT Application List' with AIT and Application name. Field name 'CBT_Application'
Field 3 - text entry field for 'L2 Primary'
Once the form 'frm_project_review' is closed, I want field 'CBT Applications' in 'frm All Projects' to list all of the applications Application only, not including AIT). And like before I don't want any duplicate records.

Please let me know if you need further information to provide guidance on how to accomplish this.

Thanks, Neil

[June7]

I don't remember which is your database if downloaded from another thread, there is nothing attached to this one. I don't keep everything I download, especially after a thread is resolved.

I am still confused.

I don't know what you mean by wanting a field to "list all of the applications Applications only, not including AIT". Did you mean a 'subform list all of the applications'?

[nwatterson]

Good morning June, I have uploaded a copy of the database.

For each project there could be multiple CBT applications. Projects are input through the 'Frm All Projects' form.
You helped me resolve an issue to select multiple CBT Applications without it creating duplicate records in the split form datasheet.

Now however, I have a requirement to have a unique 'L2 Primary' for each application for each project. Therefore I created a sub table / form called 'Frm_Project_Review' which has an application lookup field and text entry for L2 Primary.

How this ultimately comes together I don't know and that's where I am struggling. I am not sure how to relate this sub form / table to the main 'Frm All Projects' / Tbl Projects so the data is correctly related and reportable with a single project to many CBT applications relationship.

Once the relationship is there I want the field 'CBT Applications' in 'Frm All projects' to display all of the applications selected in 'frm_project_review.
Thanks, Neil

[June7]

Advise not to build lookups in tables. http://access.mvps.org/access/lookupfields.htm

Also advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

I am not clear on relationships.

Projects have applications, applications have reviews? Each application can associate with only one project?

You mean you want the combobox RowSource list to show applications? Why is there even a combobox - why is this field in tblProjects? If you want to associate multiple applications with each project then need a table for the applications with a field for the ProjectID as foreign key. If each application can associate with only one project then add a ProjectID field into [CBT Application Arch Diagrams]. If each application can associate with many projects (many-to-many) then you need another table - a junction table with ProjectID and ApplicationID fields.

Then use form/subform arrangement for data entry.