Solved

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

Posted on 2013-10-22
7
284 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

861 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