I have 3 tables that are related on a many to many basis. I deal with large organisations, and within those, with individuals. Each client must nominate 3 address types - postal, residential and business. Often, clients share an address (usually business, but not always). Therefore, one address can have more than one client associated with it.
My CLIENTS table and ADDRESS table have many records (all this is coming from an excel sheet). I know how to create a main form (which needs to be based on clients), and how to add a subform based on the junction table. My problem though is one of data entry. I've been requested to set up a search form (I think I can handle that part), but the user needs to be able to open the search form, ascertain whether an address exists - if does, then add it for the relevant client. If an address does not exist it needs to be added, and then the AddressID passed back to the client (or rather to the junction table, with the correct AddressID and ClientID).
I'm wondering how best to handle this using VB as I don't have any programming experience. Ideally, I'd like the user to be able to specifiy what type of address they are adding, before selecting or adding that address. So perhaps a button to 'add a postal address' or 'add a business address'? Or is there a better way, say a combo box that lists the three address types so the user could select an address type, search for it (or add it), and then pass the relevant values to the underlying junction table.
I'm fairly new to Access so I hope my questions don't sound too idiotic!
Is anyone able to assist me here? Many thanks!