Combo box bound field change

[Guest]

I've got a form field (companyname) based on a table but selected through a
query. I made it a combo box whose control source is (companyname). I use
the combo box to select the company that purchased a particular item and then
I'd run a report to show what all that company purchased. One company has
changed it's name and I changed it in the dbase table and now when i run the
report, it obviously doesn't return any info because the new company name
hasn't purchased anything yet. I can change it back and the data will show
up.

I've got a companynumber field in the same table that I should have used as
my bound column. How can I change the combo box to base it on the
companynumber field but still show the new company name on the reports with
the previously purchased items? Hope this isn't too confusing. Help

 

[Guest]

Sounds like a normalization problem. My guess is you have the company name
in more than one place. It should only be in the table where you keep
company specific information, not in your transaction tables.

The correct way to do this is to use a unique ID for the company record.
Autonumber fields are good for this purpose because they are guaranteed to be
unique. So your company table would contain at least:

Comany_ID (pk - autonumber)
Company Name (text)
etc.

Then rather than carrying the company name in the transaction tables, you
carry only the Company_ID as a foreign key to the Company table. Now, if you
change the Company name, it will not matter because the tables are related to
a unique value that never changes.

By now, you are thinking, okay, that sounds reasonable, but that does not
solve my immediate problem. The solution to that is to create an update
query to update your transaction tables with the new company name.

Avoiding data redundancy is the first rule of database normalization.

 

[Guest]

I've got a unique ID in my companytable called companynbr that is assigned to
each company so I'm covered there. Now, do I need to add a companynbr field
to my transaction table called companypurchases and remove the companyname
field from it or just use the query I'm already running that is linked to the
form and add the companynbr and companyname from my companytable table to
that query and remove the companynbr and companyname fields entirely from the
companypurchase table?

My next questions are do I need to run an update query before doing any of
the above items and would you tell me how to run the update query? Sorry,
I've never used this type of query before.

 

[Guest]

You are getting it. You do want the companynbr field rather than the company
name field. Your query should join the transaction table and the company
table on companynbr and include the company name field from the company
table. TADA, now you can change the company name any time and your query
will return the correct value.

Here's how you fix it.

Make a copy of your transaction table, but copy only the structure, not the
data.

Modify the New transaction table and change the company name field to
companynbr and change the data type if necessary.

Create an Append query. When asked what table to append to, select the New
Transaction table. For the source of the query (where the data comes from),
you will need the Old Transaction table joined on company name to the company
table.
In the Update To: row of the query builder in the companynbr column put the
company number from the company table.

Now, you should have an exact copy of your Old Transaction table, except the
company name has been replaced by the company number.

Delete the old transaction table

Rename thew new transaction table to the old name.

 

[Guest]

It worked. Thanks alot! I guess now I need to go back in my form and modify
my combo box and have the companynbr as my selection. Can I make the combo
box show the companynbr and name when I select a company in the form?

 

[Guest]

yes you can. Modify the row source to include both fields. Set the number
of columns to 2. Make the bound column the company name column. You can
also change the width of the companynbr column to 0 and the user will only
see the name. To get the values in a multi column combo, you address them as
Me.MyCombo.Column(0) is the first column
Me.MyCombo.Columns(1) is the second column

Then you can change the FindFirst to use the column that contains the number.