combo box to update multiple fields

Question

I have spent the last 2 days reading post, books & trying to get this to work but with no luck. I must be missing something or maybe this can’t be done. So before I give up I thought I would post.

I have a access 2003 DB in Access 2007 that works fine, I added a new combo box on the form bound to table

PM_Firm_Dist and field name CompanyName1

that gets data from table

Firm_Dist with the following

SELECT Firm_Dist.* FROM Firm_Dist WHERE (((Firm_Dist.PM)="PM")) ORDER BY Firm_Dist.CompanyName1;

It works and will place selected company name in table

PM_Firm_Dist

however I need the rest of the row data (address, phone, ect.) to be placed in the table

Answer 1

I guess I stated my problem worng.

No, I understood your question, and my answer still applies.  If we assume you want the address data etc to be the current values if a company should change its address etc then, if you store any values from the Firm_Dist table other than the foreign key, which in your case is CompanyName, you are introducing redundancy.  It's a question of normalization to Second Normal Form (2NF).   The formal definition of 2NF is:

Second Normal Form:  A relvar is in second normal form if and only if it is in 1NF and every non-key attribute is irreducibly dependent on the primary key.

Loosely speaking, in the language of the relational model, a relvar (relation variable) equates to a table, and an attribute to a column (field).

What this means in plain English is that for any column in a table we must be able to deduce its value solely from the whole the primary key column(s).  In your table the key is a two column one, comprising ProjectID and CompanyName.  From CompanyName alone we can deduce the address etc of the company, all of which are non-key attributes, so these are not determined by the whole of the key, but by one part only.  This means that update anomalies are possible, i.e. it would be possible for address etc values to be inserted into this table which are different from those for the company in question in the Firm_Dist table.  You may think you have this covered by virtue of the interface design, but that's not really relevant; update anomalies are nevertheless possible, and Murphy's Law states than what can go wrong, sooner or later will go wrong.  I have personal experience which testifies to this; I once found there versions of my own name as author in a table of references to technical articles in my own field of work.  The table should have included only an AuthorID foreign key column referencing the primary key of an Authors table in which I was represented by a single row, thus avoiding any possibility of the anomalies.  As far as the database was concerned I was three separate people of course, so anyone looking for articles by K Sheridan would miss those by K W Sheridan, and (incorrectly) K V Sheridan.

As well as creating this risk of update anomalies having  the additional address etc columns in the PM_Firm_Dist table serves no purpose as all that's necessary to see these columns, whether ina form or report, is to join the tables om CompanyName in a query.

As I said in my earlier reply, the only exception to this is where you want to preserve 'historic' values in PM_Firm_Dist table if those in  Firm_Dist should change over time.  I gave the example of an InvoiceDetails table where this is normal, but it's unusual with address data in a context such as yours.  It can't be ruled out, however, and if it is the case then the non-key columns in PM_Firm_Dist would no longer be determined by CompanyBame, so the table would be in 2NF.  In which case you'd need to 'push' the values in one of the ways I described, not 'pull' them into the form via a query.  You'll find an example of data being 'pushed' in the AterUpdate event procedure of the ProductID control in the 'Order Subform for Order Details' form in the sample Northwind database which ships with Access.

Answer 2

thanks and I'm reviewing Database Normalization Basics as It seems thing have changed since I took access class & programing in the 80's.

Answer 3

Thank you but I guess I stated my problem worng. We have a main projects table to store our projects. We use diffent firms or the same firm for projects. the Firm_Dist table holds all the different firms we deal with and the PM column is the type of firm they are. here is were the problem comes in I need my PM sub form to get firm info & populate the fields on sub form & store in the PM_Firm_Dist table. Right now all the PM sub form will do is find all PM Firms, let me choose Firm name from the combo box, store name and project ID in PM_Firm_Dist table. It will not populate fields on sub form or store Firms info in PM_Firm_Dist table.

Answer 4

I need the rest of the row data (address, phone, ect.) to be placed in the table

Think carefully about the need for that.  You'd only do it if, in the event of the company data other than its name changing in the future, e.g. if the company moves to another address, you would want the original address, phone number etc to remain fixed in the record which you are currently inserting into the form's underlying table.  This is analogous to when raising an invoice, for example, where you'd clearly want the unit price of a product to remain fixed at that when the invoice was raised, and not to reflect subsequent changes in price.  In this case you would assign the value to a bound control in the form, using code in the combo box's AfterUpdate event procedure, either looking each value up by means of the DLookup function, or by including the relevant other columns from Firm_Dist in the combo box's RowSource and referencing each by the control's Column property.  With the latter approach you need to remember than the Column property is zero-based, so Column(1)  would be the second column, Column(2) the third and so on.

With address data and the like, however, its more usual in my experience to have this reflect the current values in Firm_Dist.  For this base the form on a query which joins its current table to Firm_Dist on the company name columns, and return the relevant address etc. columns in the query.  You would not have columns in the form's underlying table for this data; that introduces redundancy and the risk of update anomalies.  Instead, add text box controls to your form binding each to the relevant columns from Firm_Dist.  Set the Locked property of each to True(Yes) and their Enabled property to False (No) to make them read-only.   When you select a company by name in the combo box its address etc data will automatically be entered into the text box controls.

One other point you should consider, however, is the current use of company names as a key.  This does require all companies to have distinct names, which is likely, but cannot be absolutely guaranteed.  Normally the Firm_Dist table would have an autonumber as its primary key to guarantee uniqueness, and the form's underlying table would have a long integer number column, CompanyID say, as the foreign key rather than the company name.  The combo box would then be set up so that while bound to the CompanyID column it shows the name:

ControlSource:    CompanyID

RowSource:     SELECT CompanyID, CompanyName1 FROM Firm_Dist WHERE PM="PM" ORDER BY CompanyName1;

BoundColumn:   1

ColumnCount:   2

ColumnWidths:  0cm;8cm

If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box.

Answer 5

Sorry I am a little lost with this.

You have a combo (called PM_Firm_Dist) that is filled with entries from a table ( table name being  Firm_Dist  is that right ?)  It looks like you are referencing/filtering by a field in the table called PM and you are looking for records containg the text "PM" within that table field ?

You want to select a row in the combo and ....... do what.

Can you explain in simple terms what it is you're trying to do and what you want the end result to be