Solved

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

Posted on 2013-10-22
7
263 Views
Last Modified: 2013-10-23
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
0
Comment
Question by:SteveL13
7 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39592172
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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39592264
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39592330
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...
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39592380
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;
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39592391
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.
0
 

Author Comment

by:SteveL13
ID: 39595199
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
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39595448
strSQL  = "UPDATE YourTable " _
             & "SET [AutoRenew] = "  & me.chkboxAutoRenew & ", " _
                   & " [AnniversaryDate] = #" & me.txtAnniversaryDate & "#, " _
                   & " [EmailAddress] = '" & me.txt_EmailAddress & "', " _
                   & " [CCEmployee] = " & me.cboCCEmployee & ", " _
                   & " [ccEmpEmailAddresses] = '" & me.txtccEmpEmailAddresses & "' " _
             & "WHERE [InvoiceN] = '" & me.txtInvoiceN & "'"

This assumes that you have already checked to ensure that all of these fields are filled in and that AutoRenew, CCEmployee are numeric, AnniversaryDate is a date, and the others are text fields.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now