Solved

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

Posted on 2013-10-22
7
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 48

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 48

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

691 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