Workgroups, 2 tables with different permissions in 1 form 1

[rotschreck]

Hey there,<br>
<br>
1st question:<br>
<br>
My boss needs a new database using Access. Problem is that one of the worksheets the company uses can only be filled half by one department, half by another. The boss wants permissions for each department.<br>
<br>
My answer so far:<br>
<br>
I've created 2 tables and seperated the worksheet in half on both with 1 mutual field &quot;File Number&quot;. I've placed a primary key on this field in table 1 and linked a relationship to the same-name field in table 2. After placing the proper permissions to each department workgroup, I created a form using the information on both tables. The finished product is a form that allows one department to write in some fields and another department to write in the rest.<br>
<br>
Major bug:<br>
<br>
department 1 is able to write to the form, but as soon as we close the form, the info vanishes. It's still on table one, but doesn't show on the form. I've figured that if there is something written in the &quot;File Number&quot; on table 2, department 2 can continue to write in their own fields.<br>
<br>
Needed:<br>
<br>
a way to copy automatically information in table 1 to table 2....<br>
<br>
Thanks.

 

[DougP]

A better way is use one table and just put half on one form and half on the other.<br>
So each dept is just filling out what they need only on their own form without knowing or caring about the rest of the table.<br>
I've doen this with 6-10 people, depts etc.<br>
then the report or query is looking at one table.<br>
Each person who needs to find something is looking at the same number Say Invoice 12345<br>

 

[rotschreck]

I would except department 2 can't fill in their data until department 1 has finished with theirs. And when department 2 is done, dept 1 has to be able to see so that they can take the data and rework it again. <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/siltharin>Maitredudonjon's</a><br>

 

[elizabeth]

Don't copy data from one table to the other. Except for temp (throwaway) tables, you never want to do this; it violates basic relational db design principles, which means it will cause you plenty of problems. <br>
<br>
Did you give Read permissions to all tables to the User Group?<br>
<br>

 

[rotschreck]

Yes I did.<br>
<br>
but it is possible to copy data from one table to another?<br>
<br>
I've looked at other possibilities and it seems the most logical. <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/siltharin>Maitredudonjon's</a><br>

 

[elizabeth]

OK, on rereading I think you mean when you add a record to table 1, you want to copy it's primary key to use it as the primary key of a new related record in table 2. Is that correct? If so, the easiest way to handle this is to make the fields from table 2 a subform on the form for table 1. use the subform wizard for this and it will insert the correct code you need. The bound field for the primary key can be visible or not, depending on your needs. Make sure you set corresponding properties of Locked, Enabled, and TabStop. Finally set the Default property to something like: Forms!frmMainForm!PrimaryKey.