Forms & Tables

[MChancellor]

I have created a small database which primarily consists of 2 main tables (Package Receipt & Personnel Information) which will be used for checking in orders in the mail/supply room and sending them on to the employee who ordered the item. <br>
<br>
At present I have used the Package Receipt table as the basis of the main form and have placed 2 subforms (one based on the junction table of the 2 main tables and the other based on an autolookup query which has the Personnel Information table underlying it.) The second subform (which uses the query to pull the information from the Personnel Information table) works great, and automatically places the information (the Employee ID) in the junction table. However, the other side of the junction table (using the control ID from the Package Receipt table) appears to update on the form (I created an expression that set the Control ID on the subform equal to the Control ID, which is an autonumber, on the main form - the Package Receipt table.) but does not update in the table, which causes an error. <br>
<br>
I would appreciate any ideas on how to resolve this situation, and hints on what to do in Access to fix it. I'm not overly familiar with Access, and this is one of the first projects of this sort I've done outside of the classroom. <br>
<br>
Thanks in advance,<br>
M. Chancellor

 

[DougP]

When you click in the sub form that dosen't work try to edit a field. Look in the lower left of the screen in the gray box dose it say &quot;Recordset not updatable&quot;?<br>
<br>
It has to do with a subform being tied to a query.<br>
One fix though cumbersome it to update the table directly using VBA code and requery the subform.<br>
I think it has to do with &quot;Relationships&quot;<br>
Try this <br>
Create a 2 simple new tables using the Table wizard and let it create a Relationship between them.<br>
then make test query based on those new tables<br>
Run the query and try changing the data.<br>
Notice the message it gives, this may help you figure out what you need to do in your case. <br>

 

[MChancellor]

Actually, the part based off the query is running beautifully. The other part isn't tied to the query at all.<br>
<br>
I have 2 fields in the junction table. One is the Control ID which is the primary key of the Package Receipt table. The other is the Employee ID and that is the one I have used the autolookup query on, which updates the junction table just fine.<br>
<br>
What I'm trying to figure out is how to update a field in a separate table from the first table. I want to update the Control ID field in the junction table (which I have named &quot;Package-Person&quot with the automatic number that is generated in the Package Receipt table each time a record is entered. Basically I want to copy whatever is in the Control ID field of the Package Receipt table on the form to the Control ID field of the Package-Person (junction) table. This is just so that it will tie together the employee information and the package information. <br>
<br>
Is there a way to do this? <br>
<br>
Thanks for any help.<br>
M. Chancellor