Updating a table with info from a form

[jgarnick]

I have a form that a user enters check information on, ie name, date, amt, purpose. I need to find out the best way to update a separate table with the check information.&nbsp;&nbsp;The trick is that the program needs to lookup the member using the name entered on the check entry form in the separate table, but only the member's id is in that table.&nbsp;&nbsp;The members name is in another table (related by id).&nbsp;&nbsp;The name needs to be verified by the user before saying ok to update with the check information.<br><br>Table 1 --name, address (includes id)<br>Table 2 --residency details, residency payment (includes id, no names)<br>Table 3 --cash receipts (does not include id--not related at all)<br><br>So while entering information in table 3, need to also update table 2 with the same information but need to verify that the names match first.<br><br>I know this is confusing and I haven't explained it very well.&nbsp;&nbsp;Currently, I am just getting a list of residency fees paid out of table 3 and looking up each person in a form based on a query of table 1/table 2 info, and manually entering the check information.&nbsp;&nbsp;Time consuming and I'm sure it can be automated.<br><br>Thanks for any help!! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[DougP]

It is easily done with VBA code<br>You have to determine which Sub on your form will write the records. Such as a On_click in on of your subform <br><br>here are the basics<br> Dim db as database, rst as recordset, SQL as string<br> Set db = CurrentDb<br> ' SQL string.<br> Set rst = db.OpenRecordset(&quot;MyTable&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.Addnew&nbsp;&nbsp;&nbsp;' Adds a new record<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.Edit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Edit and existing record<br>' note you use either &quot;addnew&quot; or &quot;edit&quot;, not both at the same time<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst!Fieldname= me!Somefield&nbsp;&nbsp;&nbsp;' Setting data in a table to a field on a main form<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst!Fieldname= Me![Employees].Form![DepartmentName]&nbsp;&nbsp;' Setting data in a table to a field on a sub form<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.update&nbsp;&nbsp;' must have for updating record to table which is used for both addnew or edit<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.close&nbsp;&nbsp;&nbsp;' Close the recordset <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;db.close&nbsp;&nbsp;&nbsp;&nbsp;' close the Database<br><br>'Other items <br> ' Set a field on main form to a field on a subform<br>Me!FieldName = Me![Employees].Form![DepartmentName]<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' set record source on a Subform <br> SQL = &quot;SELECT SALES_ORDER, CUSTOMER, CUSTOMER_PO_NUM, CustList.JobNumber FROM CustList INNER JOIN none_SO_MASTER_HDR ON CustList.DistributorPO = none_SO_MASTER_HDR.CUSTOMER_PO_NUM WHERE (((none_SO_MASTER_HDR.SALES_ORDER)= &quot; & [Forms]![frm-Release Prints]![Text0] & &quot);&quot;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me![qry-Main subform].Form.RecordSource = SQL <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Cornerstone]

If I understand this I don't see why you could not add the ID field to the third table. I would query table one and two for the main form and then have table 3 appear in a related subform. <br><br>When the form opens the query recordsource of the main form could have a parameter asking who they are and the name could be typed in to go to that persons record.

 

[elizabeth]

In general, you do <b><i>not</i></b> want to copy the same data into 2 different tables, just the ID.&nbsp;&nbsp;As Cornerstone suggests, ID probably belongs in your 3rd table. You could use a combobox displaying the name (but bound to the ID) so the person entering the check had to select a valid name or add a new name to Table1.

 

[jgarnick]

I don't think I want to add the id to the 3rd table.&nbsp;&nbsp;Only about 10% of the cash receipts (table 3 records)need to be entered in table 2.&nbsp;&nbsp;The rest do not and the info only stays in table 3.<br><br>Thanks for your help! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[elizabeth]

I don't mean to nag &nbsp;&nbsp;but you can always leave the 3rd table's ID field blank when it doesn't apply. Just set the field's Required property to No in the table design. An ID field doesn't take up much space.