Appointments Planner Database

[cdg]

Hi
I am no access expert and somewhat of a novice butI have an issue with a database I have, to book appointments, this was a pre prepaired database and I have been playing around with it and customising it to my own needs. The problem I have is that it uses the customer name field as the primary key and does not use a customer ID field which I would use as the primary key. This is causing me no end of problems, the obvious one being I cannot have two customers with the same name and I don't know if i'm not seeing the obvious or it is complicated but I am drawing a blank on how to mod the code to allow me to have a Customer ID as the primary key and allow duplicate names. I have no ploblems somodding my forms and sorting the rest of the database out but I am not sure if if is a matter of a few simple changes here and there or a major re write of the VBA etc.
Not sure if I can attach the database for someone to cast their eye over it and give me some pointers. :banghead:

Kind Regards Chris

 

[plog]

It's going to be as complex as the system you currently have. The more objects and code that is in use, the more work.

First, let's define some terms and the fields they represent. Primary key will be the new autonumber field you add to your customers table, let give that fiedl the name "Customer_ID". Foreign key will be the field in all the other tables that you use to link back to Customer_ID, let's call all these fields "ID_Customer". Then just to keep things straight let's use "Old_Key" as the customer's name in all the other tables.

My advice is to create a new, blank database, copy in your customer table, add Customer_ID to it. Then, one at a time, for every other table you do this: copy them in to the new database, add the ID_Customer field to them, create an UPDATE query based on them and the customer table linking them by the Old_Key field and update ID_Customer in them to Customer_ID from the customer table. By doing that you have changed the connection to the autonumber. They key though, is to not delete Old_Key until you are completely finished with all objects, not just tables.

So now you have all your tables in your new database. Now you copy in the queries one at a time and change all the joins from the Old_Key to Customer_ID. Once the queries are done you copy in the Forms and see if any code in them uses the Old_Key, then the same for Reports and any Macros/Modules. Most likely you will be 90% done once the queries are reconfigured.

Once done, make a copy of this new database, then delete all the Old_Key fields. Then test it to see if you missed anything.