Updating Form Field From Another Table AMN 29.09.2006 22:51:01 Hi, I have a "ProductName" table with two fields: "Product" and "ProductCode". I have this because the product name tends to be very long and I have thousands of products. I have a form that populates fields in a "Customer" table listing the product they have. In this form I have an unbound text box named "Code". I would like to enter the ProductCode into this text box and on exit I would like it to populate my "Product" control in the form for that customer only. I currently do this by running a macro that runs a query. I would rather do this through VB. Your help is much appreciated.

Re: Updating Form Field From Another Table "Jeff Boyce" <nonsense[ at ]nonsense.com> 29.09.2006 23:42:02 You've only describe the fields in your ProductName table. It would be unusual to have a table with customer information that also contact customer-product information -- that is not a well-normalized design. A well-normalized design would have a customer table with customer-only info .... name, address, phone, ... and a CustomerProduct table that resolved the many-to-many relationship that exists between customers and products (one customer can have many products, one product can be had by many customers). Your description of "populate fields in a Customer table" sounds like a spreadsheet, with one column for each product. Or am I reading too much between the lines...? Regards Jeff Boyce Microsoft Office/Access MVP "AMN" <AMN[ at ]discussions.microsoft.com> wrote in message news:76308F0B-1975-47B5-8B18-741CD6B17D6E[ at ]microsoft.com... [Quoted Text] > Hi, > > I have a "ProductName" table with two fields: "Product" and "ProductCode". > I have this because the product name tends to be very long and I have > thousands of products. > > I have a form that populates fields in a "Customer" table listing the > product they have. > > In this form I have an unbound text box named "Code". I would like to > enter > the ProductCode into this text box and on exit I would like it to populate > my > "Product" control in the form for that customer only. I currently do this > by > running a macro that runs a query. I would rather do this through VB. > > Your help is much appreciated.

Re: Updating Form Field From Another Table AMN 30.09.2006 00:04:02 Hi Mr. Boyce, The tables have a lot more data than described and have many relationships that are crucial already exist. I was trying to keep things simple for the sake of the question. Also I used Product and Customer as examples only. This is my offices medical database I have created and in reality the "products" are the "tools" that I use during surgery in each patient. That is why I don't want to create a relationship between these two tables. So I was just hoping by me entering the abbreviation in an unbound textbox, I could get MSAccess to fill in the whole name in the appropriate control. Hope I was able to clear this up. Sorry for the confusion. "Jeff Boyce" wrote: [Quoted Text] > You've only describe the fields in your ProductName table. > > It would be unusual to have a table with customer information that also > contact customer-product information -- that is not a well-normalized > design. > > A well-normalized design would have a customer table with customer-only info > .... name, address, phone, ... and a CustomerProduct table that resolved the > many-to-many relationship that exists between customers and products (one > customer can have many products, one product can be had by many customers). > > Your description of "populate fields in a Customer table" sounds like a > spreadsheet, with one column for each product. > > Or am I reading too much between the lines...? > > Regards > > Jeff Boyce > Microsoft Office/Access MVP > > "AMN" <AMN[ at ]discussions.microsoft.com> wrote in message > news:76308F0B-1975-47B5-8B18-741CD6B17D6E[ at ]microsoft.com... > > Hi, > > > > I have a "ProductName" table with two fields: "Product" and "ProductCode". > > I have this because the product name tends to be very long and I have > > thousands of products. > > > > I have a form that populates fields in a "Customer" table listing the > > product they have. > > > > In this form I have an unbound text box named "Code". I would like to > > enter > > the ProductCode into this text box and on exit I would like it to populate > > my > > "Product" control in the form for that customer only. I currently do this > > by > > running a macro that runs a query. I would rather do this through VB. > > > > Your help is much appreciated. > > >