please HELP!!!

[supaflychick]

I'm working a Maintenance Database at work. It's been all set up and has been used for a few years now. I was asked to update a few things, like add new store locations to the database.

Stupid me, when ahead and made changes on the original database without saving a copy. Anyways I added the store locations tot he orginial table, then went into forms, and changed the store location field to the new one, so that the new locations would show up....Well now all the fields have blank store fields, where originally there was a store location associated to each record. Is there any way of undoing this??

Going back through inputting each store location, is going to be difficult!!

[jzwp11]

Do you have separate fields in the table for each store location or 1 field? When you say that the fields are blank, do you mean the controls on the form? If so, what is in the table? I'm hoping your data is still in the table but just not showing up on the form. If it is not in the table, then you have no recourse but to re-input it.

[supaflychick]

Ok - I was able to get the information back by contacting my IT department. Thank god.

So now my problem is. I've changed the Store Location field in the table to include in the drop down menu the 2 new locations. These locations now don't show up in the Information Entry Form that has been created. How do I go about updating the field to include the other 2 locations, without losing the information, like what happened before?

[jzwp11]

First, it is not recommended to have dropdowns (combo/list boxes) at the table level. Check out this site for more details on the problems it can cause.

What I would highly recommend is to have a table that holds all of the locations as records

tblLocations
-pkLocationID primary key, autonumber
-txtLocationName

Then in your main table, just use the primary key of the locations table as a foreign key.

YourMainTableName
-pkRecordID primary key, autonumber
-fkLocationID foreign key to tblLocations


In your form, use a combo box to select the appropriate location. The combo box will be based on tblLocations but bound the fkLocationID in the main table.


You will need to add a new, long number datatype field to your main table (do not delete the location field you currently have yet). Create and populate the tblLocations. Make sure the location names in tblLocation match those your currently have in your main table.

Now create a query that joins the main table to the tblLocations via the fields that hold the name. Select the fkLocationID field. Now change the query type to an update query and set fkLocationID=pkLocationID. Once you have successfully populated the fkLocationID field in your main table then you can get rid of the text field that hold the location name in your main table. Of course, make a backup before doing the update query.


I can help with this if you can post your database (with any sensitive data removed).