Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

How update fields in a table based on data in a form

I have a form which shows records in a table.  But the table can have more than one record that has the same InvoiceN.  InvoiceN is a text field.

When the user has entered data into the following fields on the form that is displaying the first record of records with the same InvoiceN, I want all the records with that same InvoiceN to have the fields updated to match the first record.

The fields are:

chkbxAutoRenew
txtAnniversaryDate
txtEmailAddress
cboCCEmployee
txtccEmpEmailAddresses

Should the code be in the afterupdate event of the form or the beforeupdate event of the form?  Somehow it has to be done before the user navigates to another record.  And what should the code look like?

--Steve
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

needing to store the same information in multiple records of a table is a good sign that your data is not properly normalized.  The first thing you should look at is what is it that makes these records different from one another.  If you have fields that contain the same information, then that data probably is directly associated with the InvoiceN and should reside in your "Invoices" table, where there should only be one record per invoice.  Once in your Invoices table, you can link the two tables and display the information for each of those other records in your form, without having to duplicate the information.
Try: Usually you have two tables, one for invoices, the other for invoice items where you have a master table and a child table.
If talking about just one table, you may produce the effect as follows.

Main form with a combo box named cboInvoice with row source: Select Invoice number from theTable;

Subform with recordsource as the myTable.
Link master fields: cboInvoice
Link child fields: txtInvoiceInSubform

selecting an invoice number from combo, displays the records witht the same invoice number.
Link subform to mainform using txtInvoiceNumber and subform invoiceNumber.

Adding a new value in combo, creates a new record in subform.
SteveL13,

If I have this correct, this is a classic One To Many relationship.
If a Customer (that has has the fields you listed) can have many Orders/Invoices
And one invoice can have many "Line items", then you can pull in the Customer info in a query, Thus there is no need to store this data in the InvoiceLineItems table...
The query would be something like this to pull the Customer fields into the InvoiceItems:

SELECT tblCustomers.CustID, tblCustomers.CustName, tblCustomers.CustEmail, tblInvoices.InvoiceID, tblInvoices.InvoiceDate, tblInvoiceItems.InvoiceDetailID, tblInvoiceItems.ProductID, tblInvoiceItems.Quantity
FROM (tblCustomers INNER JOIN tblInvoices ON tblCustomers.CustID = tblInvoices.CustID) INNER JOIN tblInvoiceItems ON tblInvoices.InvoiceID = tblInvoiceItems.InvoiceID;
Now again, if you create a Main/subform (linked on the key field)
...then as soon as you create a subform record, the linking field will be brought into the subform by default.
Avatar of SteveL13

ASKER

I realize this should be normalized and I'll get to that.  But for now, I need to:

I have a form which shows records in a table.  But the table can have more than one record that has the same InvoiceN.  InvoiceN is a text field.

When the user has entered data into the following fields on the form that is displaying the first record of records with the same InvoiceN, I want all the records with that same InvoiceN to have the fields updated to match the first record.

The fields are:

chkbxAutoRenew
txtAnniversaryDate
txtEmailAddress
cboCCEmployee
txtccEmpEmailAddresses
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial