LT- Although at first blush this might look like an unnecessary duplication of data, the name and address fields in InvoiceInfo are what I call "point in time" data. They are the company name and address that existed "at the point in time" that the invoice was created. If the info in CompanyInfo later changes, you probably want to keep the data that was current at the time the invoice was created.
To solve this, look at the code in the AfterUpdate event of the CustomerID combo box on the Orders form in Northwind. It copies the address info to the Orders table fields from additional hidden fields in the combo box when the user selects a new CustomerID. John Viescas, author Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of LTPortella Sent: Saturday, April 15, 2006 7:36 AM To: [email protected] Subject: RE: [ms_access] (unknown) Tom Thank you. There are two problems with this. 1. the table CompanyInfo changes and very often the record for a given company will be deleted altogether and I need to retain the info in the invoices file 2. I have set up the combo box to select the company name as you recommend but I do not know how to : "then use the company name to gather the information about the addresses from the companyinfo table" In fact the point 2 above was precisely the reason for my posting. Can you help me with this? I am NOT a programmer but I am willing to put in the time and effort to do this. I strongly suspect that this will take a little programming and an awful lot of work on my part but I am ready. Once again many thanks for your time L T Portella -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Tom McManus Sent: Saturday, April 15, 2006 1:05 AM To: [email protected] Subject: Re: [ms_access] (unknown) It is not a good idea to duplicate where information is stored, but rather have only one place and then reference to this. You could have your combo box to select the company name and then use the company name to gather the information about the addresses from the companyInfo table. ----- Original Message ----- From: "ltportellar" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Saturday, April 15, 2006 2:18 PM Subject: [ms_access] (unknown) > > 1. Table CompanyInfo has fields entitled (a) CompanyName (b) > Address1 (c)Address2 and they are populated already > 2. Table InvoiceInfo has identical blank fields entitled (a) > CompanyName (b)Address1 (c)Address2 > 3. To prepare some invoices need to populate table InvoiceInfo > and do so with a combo box by selecting the appropriate companyname > from the table CompanyInfo and the question here is how can I get the > corresponding Address1 and Address2 transferred automatically from > table companyinfo to table InvoiceInto based on the selection made for > Companyname > > Can it be done? If so, how? Thank you > > > > > > > > Yahoo! Groups Links > > > > > > > Yahoo! Groups Links Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
