Adding a new record to another table

[sanand]

Hi experts,
I have a form based on table1. By selecting the ProjNum from a combo box on
this form, I need to add the selected ProjNum to another table (Table2).

Table1 is the master table with all the projects and related info. the
Supervisor wants to accomplish the following:

1. Enter the newly received project in Table1
2. By selecting this new Project from the Combo Box on the frmMaster, need
to assign to another
Tech1(uses Table2), or Tech2(uses Table3), or Tech3(uses Table4)..
3. Each Tech has its own form to view the projects assigned to him/her.

How this can be accomplished?
Any assistance is appreciated.

TIA
sanand

 

[John Vinson]

Hi experts,
I have a form based on table1. By selecting the ProjNum from a combo box on
this form, I need to add the selected ProjNum to another table (Table2).

Table1 is the master table with all the projects and related info. the
Supervisor wants to accomplish the following:

1. Enter the newly received project in Table1
2. By selecting this new Project from the Combo Box on the frmMaster, need
to assign to another
Tech1(uses Table2), or Tech2(uses Table3), or Tech3(uses Table4)..
3. Each Tech has its own form to view the projects assigned to him/her.

How this can be accomplished?

Preferably by properly normalizing the tables, instead of storing data
(a tech's name?) in a tablename.

What will you do when a new tech is assigned? Create a new table,
redesign all your queries and code? What will you do when a tech
leaves?

You'ld do MUCH better to have Table2 with a Tech field; if each tech
should be able to see only their own records, they can simply use a
Form based on a Query selecting those records. Access Security can be
set up so they cannot see the raw table nor any data other than their
own query.

What you ask could be done... but it would be much more complex and
almost certainly less secure than the normalized solution.

John W. Vinson[MVP]

 

[sanand via AccessMonster.com]

Thanks for your response.

I know that the problem can be solved by simply normalizing the mdb. But in
this case, each tech is a specialist and that Tech can work on a specialised
issues.

Basically, each table has different items/types that each type of tech can
work on - Each table is not a copy of the other.

Thanks again

 

[John Vinson]

Basically, each table has different items/types that each type of tech can
work on - Each table is not a copy of the other.

Sorry! That was not obvious from the initial post.

What you may want to do is have a Subform Control on the main form. In
the AfterUpdate event of the combo box selecting the Tech, set the
subform's SourceObject property to the form appropriate for that tech.

John W. Vinson[MVP]