I'm developing an access database and seem to be having what must be a basic problem. To use an example, my database contains a Customer table and a City table. The City table is linked to the Customer table by a CityID in the Customer table. The City Id's are linked by a one-to-many relationship (the many side being in the Customer table).
I have created a query which brings the two fields - Customer Name and City together from the two tables (just to test the theory before I do the same for all the other fields). I have also created a data entry form based on this query.
My problem is when I open the data entry form, I can view existing records OK - when I add a new record, which has a new city name not already in the city table, it won't let me add the new City to the underlying table; it says it can’t add it to the ‘one’ side of the relationship. I can understand this since I have enforced referential integrity. However, when I remove the referential integrity from the one-to-many relationship, it will add the City but duplicate any cities already in the City table – which defeats the point of having a separate City table! If I then add an index with no duplicates to the City name field, then it won’t let me add new cities and existing cities are duplicates.
The purpose of the data entry form will be to choose the city, for a new customer record, from a pick-list if it’s already in the City table and if it’s a new city then to add it to the City table, via the data entry form.
I am using the City/Customer relationship as an example and will apply the theory to the rest of the relationships with my Company, Product, Orders table etc.
I would be really grateful for any help as this is the first database I have designed from scratch. I do not have any knowledge of VBA and very limited macros experience. The text books are not very helpful for this problem, so I’m thinking that what I’m doing wrong must be very obvious!
I have created a query which brings the two fields - Customer Name and City together from the two tables (just to test the theory before I do the same for all the other fields). I have also created a data entry form based on this query.
My problem is when I open the data entry form, I can view existing records OK - when I add a new record, which has a new city name not already in the city table, it won't let me add the new City to the underlying table; it says it can’t add it to the ‘one’ side of the relationship. I can understand this since I have enforced referential integrity. However, when I remove the referential integrity from the one-to-many relationship, it will add the City but duplicate any cities already in the City table – which defeats the point of having a separate City table! If I then add an index with no duplicates to the City name field, then it won’t let me add new cities and existing cities are duplicates.
The purpose of the data entry form will be to choose the city, for a new customer record, from a pick-list if it’s already in the City table and if it’s a new city then to add it to the City table, via the data entry form.
I am using the City/Customer relationship as an example and will apply the theory to the rest of the relationships with my Company, Product, Orders table etc.
I would be really grateful for any help as this is the first database I have designed from scratch. I do not have any knowledge of VBA and very limited macros experience. The text books are not very helpful for this problem, so I’m thinking that what I’m doing wrong must be very obvious!
