0

I have three tables 

tblContact - ContactID(Primary Key), Last_Name, First_Name, MailingID,
             Phone, Mail_List
tblAddress - AddressID(primary Key), Address
tblMailingAddress - MailingID(Primary Key),ContactID, AddressID

each contact can have more than one address, and each address can be shared by another contact

When I create an entry form for a contact in Access I want to include their name and address on one form, but I am worried about when "Jane Smith" gets married to "John Smith" whose address is already stored in tblAddress, "Jane Smith" is a new contact, but her address already exists, so I don't want the user to be able to add the address in the table again.

I am using bound forms, so I am worried I may not be able to search tblAddress for the address before saving it as another address.

On my frmEditContact I use an SQL statement to populate a combo box for each of the available address' for each contact, but when adding an address in frmAddContact it is a bound txtbox....

Can I proceed this way? How can I stop a user from adding the address again?

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Odesmere
  • 33
  • 3
  • Do your addresses really need to be atomic? Is this a real-estate database or something similar, where the address really is meaningful in that way? – Robert Harvey Aug 14 '12 at 20:36
  • That is going to be difficult, I reckon. You will need to check something central to the address, steet name for example, but only the central word to avoid problems with Street and St, the user can then see a list of streets containing that word. – Fionnuala Aug 14 '12 at 20:37
  • You'd have to do an exists check, that's going to be slow and painful and way more expensive than a few duplicated addresses. Have you considered ContactGroups and attaching Address to that and then contacts join and leave the group? – Tony Hopkinson Aug 14 '12 at 21:12
  • @Tony I like the idea of a contact group, that is basically where I run into trouble. The database stores donor and volunteer information for a charitable organization, so I have a lot of contacts that donate through their business as well as personal donations, sometimes they donate as husband and wife as well. Or a house full of roomates/different family members. I may be looking beyond the scope of the problem here, but the database they were using is full of duplication, and the end user (a volunteer somedays) sometimes has little to no computer skills, so I am trying to limit their use – Odesmere Aug 15 '12 at 13:15
  • @Remou are you suggesting I search &like "user input" and then say use a msgBox to prompt "this already exists? did you enter it correctly?"yes/no... and then ignore the addnew? I am not sure how to search the address because I'm using a bound form, can I search before it updates even though it is bound? – Odesmere Aug 15 '12 at 13:38
  • It would be best not to use a bound form for additions. If you must use a bound form, you can use the Before Update event. I hope your country is not mine, because here you can get the same address for a several houses, they are distinguished by the name of the occupant. With volunteers entering data, I am not sure you will achieve your aims, but you may be able to cut back a bit. – Fionnuala Aug 15 '12 at 13:41
  • thanks, seems like a problem that could arise in many databases, I'm surprised I couldn't find this question any where else – Odesmere Aug 15 '12 at 15:15

0 Answers0