Linked forms and many-to-many relationship

[jfn15]

I have 4 tables


tblParty - PartyID is PK - also has other details about each person (DOB, First and last name etc.)
tblLocation - LocID is PK - contains fields for St. No., Street, CityID (Link to tblCity) etc.
tblPartyLocation - has both PartyID and LocID
tblLocType - LocTypeID is PK, other field is LocType (postal, business or residential)

Two questions -
1. As any location may be both a postal and a residential (or postal and business in some cases), there is clearly a MTM relationship there as well. Should I include the LocTypeID in tblPartyLocation, or should there be a 2nd linking table that holds the LocID and the LocTypeID?

2. User is required to first complete the Party details, and not all parties will supply an address (this is unavoidable and must be recorded). My instruction is that we must be able to enter a new party (person) and then click on a button that will provide a search facility, i.e. user may search for a location that may already be present in the database (as some locations are common to many parties). I have no idea how to accomplish this. I do know how to enter a simple subform based on this relationship, but not a linked form.

How do I create a search form that allows a user to find a location that may be present already, and then assign that location to the party being recorded? Further, when a location is not present, we need to allow entry of location details, and then assign the new locaton to the new (or sometimes existing) party. Users must also assign a location type to each location that is recorded for any person (party).

Hope this makes sense. My experience with Access is very limited.

[June7]

1. Why is location type even pertinent?

2. Sounds like you need a form/subform arrangement. Main form for the Party (these are people?) and a subform for tblPartyLocation with a combobox to select Location. Also, explore the NotInList event of combobox.

[jfn15]

Location type is necessary. Many parties (people) will be applicants, who must nominate a postal address and a residential address, and in many cases also a business address. They are sometimes the same, and I'm instructed that reports will be required based on location type. My instructions are that a search form will be necessary, to establish whether an address is already in the database (and this will be very common). The user will be able to enter a street name or a city name (or both), and a list of all existing addresses must be generated, from which the user can select the correct one (if it exists). If not, a button is required to open the location form to a new record, that will then be linked to the party. One location can have many different contacts (parties). Similarly, one contact can be linked to more than one location.

[June7]

Here is one way to filter a form http://datapigtechnologies.com/flash...tomfilter.html

[jfn15]

Thank you for the link. It doesn't address my issue (and that, I'm certain, is because I've not explained it well). It has answered another query I had been searching the web to solve - how to clear the filter I had on 2 other forms! Many thanks!
A combo box search function won't really suit for finding locations in this case, as we have so many records to search. Since posting my first question I've played around a bit and I now have a search form that allows me to enter a street or city name (or part thereof) and the results are shown in a subform. I can double click on any of the results of the query in the subform to open the form to that record if that it what the user wants.
I guess my problem is one of inexperience. I'm very very new to databases and so the thought processes around some of the relationships have not yet become 'natural' for me. I've not had to deal with many-to-many relationships before. I suppose my question is (to dumb it down) - if I search for an address and it does exist in the database already, how do I then allow the user to select the correct address and have it link to the new record (i.e. the new person) in the database (or conversely, how to add a new address and have it relate to the new person)? To put it even more simply, I
1. Add a new person via a form (frmParty).
2. The new person has supplied a postal address, business address & residential address. I must check the database to see if any of the addresses supplied already exist in the database. If (for instance) the supplied postal address is already there, it must be applied to the new Party as his/her postal address. If any of the addresses supplied do not already exist, they must be added so that they link to the new Party, and I must also specify which is the residential address and which is the business address.
As many of the people in the database work for large organisations, many of them share a postal address, but they may work at a different faculty and will nominate this as their business address. So, any 1 person can (usually will) be linked to more than one address (location); similarly, some addresses will have several people linked.
Not sure if I've made this any easier - it must be difficult trying to understand some of the questions thrown at you by those of us just beginning to explore the world of Access!!
I'm surprised at (and at this stage, easily confused by) the amount of information and advice freely offered on sites such as this one!

[June7]

Selecting the address does sound complicated. Nothing I've ever had to deal with. I expect you could have a 'popup' form dedicated for the address search and with code pass the ID of the selected address record back to the new Party record.

[jfn15]

Exactly! However I have next to no coding experience, so I'm hoping someone can assist.

[June7]

Triggering a popup address search form is easy. Maybe a little button next to a textbox that says 'Address Search':

DoCmd.OpenForm "AddressSearch"

The tricky part is passing back selected address ID. Maybe code behind AddressSearch Close button (I always disable the X close button) to populate textbox on calling Party data entry form, something like:

Forms!Party.txtAddressID = Me.txtAddressID

[jfn15]

I'm wondering if perhaps I could place a button on the search form ('select') and place the code in the click event of this button - would that work? I'll give it a go at work tomorrow - many thanks for your continued assistance. I do appreciate it.