I made an intricate quotes form that I prefer not to have to make again.  After I made the form, I decided I thought it would be a good idea to change the primary key in the customers form to a CustomerID, instead of using the name for the primary key.  I deleted all relationships, added the new CustomerID to all tables, created relationships and found that things just weren't working like they were before (all of my buttons were no longer working, etc).  So, I changed it back and deleted all references to CustomerID.  Now, even though all has been deleted, when I try to open my quotes form it asks me to input the CustomerID - Access is still looking for it.  I tried to Compact & Repair the database, and that did not help?  Is there anywhere else that I can do besides making the form all over again? Please help :)  Thanks,Alyssa

---

Reply from Richard Rost:

Alyssa, it's impossible for me to tell if you "ruined" your database without looking at it. You generally do want to use an Autonumber as a primary key field - however if you already have relationships set up based on some other value, it may be difficult to change it. Let's say, for example, that you already have two tables set up like this:

CustomerT: MyCustomerCode, FN, LN, etc.
JOESMITH01, Joe, Smith
DANPETERS44, Dan, Peters

OrderT: OrderID, MyCustomerCode, OrderDate, etc.
1, JOESMITH01, 1/1/03
2, JOESMITH01, 1/2/03
3, DANPETERS44, 1/4/03

This will work JUST FINE, except you have to assign a TEXT MyCustomerCode to new customers and that's the field that will be used for the relationships. I don't recommend it, but if that's how your database is already built, you can live with it.

Now, if you want to CHANGE THIS and you already have records in the related table (OrderT) then you have to work some magic:

1. ADD an Autonumber to the CustomerT called CustomerID - This will assign all new autonumbers to your customers

2. ADD CustomerID to your OrderT table as a NUMBER of type Long Integer - this is your foreign key

3. JOIN the two tables together in a query OrderT -> CustomerT so you can see the blank CustomerID, the Autonumber CustomerID, and the old MyCustomerCode together

4. Turn that query into an UPDATE QUERY and set the CustomerID field in the ORDER TABLE to be equal to the CustomerID field in the CustomerT Table.

5. Now you can delete your MyCustomerCode field.

That's a lot of work, but it's the only way to keep all of your relationships intact - otherwise you're going to scramble your data.

Now as far as the BUTTONS on your form not working, I don't know what that's all about. If they depended on the data being valid to work, then that could be the problem... again, I'd have to see it to tell you for sure what the problem is.

Hope this helps.