?
Solved

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

Posted on 2013-10-22
7
Medium Priority
?
307 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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 earned 2000 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 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