How can I populate the third table in a many-to-many relationship in an Access form? (MySQL backend)

Question

Firstly, here is the relationship between the relevant tables.

I'm developing an Access frontend to serve as a CRUD for an underlying MySQL database, and I'm most of the way there for properly representing and interacting with the tables in a many-to-many relationship. I followed the instructions in this tutorial and my form is mostly functional. But the tutorial example is limited in that the third table in the relationship (in my case, Addresses) is static, and cannot be changed from the form.

Here's how my form looks currently. When adding a new entry in the people_has_addresses subform, if I wish to add an address which already exists in the addresses table by entering its ID into the addresses_addressID column, everything works. The rest of the columns automatically load in the address, and people_has_addresses automatically populates a new row with this unique combination of PersonID and AddressID. The problem comes when I want to create a new address on the fly, add it to the Addresses table, and populate people_has_addresses with this new combination. If I free-form type a new address into the subform and hit enter, I get the following error: "The Microsoft Access database engine cannot find a record in the table 'addresses' with key matching fields 'addresses_AddressID'."

My question is, what do I need to do in order to allow the creation of new rows in Addresses? Ideally working the way I describe above. I feel that I'm close to getting this right, but don't know what to do from here.

Answer 1

Ok, so the combo box in that row lets you select a given address. You not really described that if you have 10 address,which one you want to appear say in a mailing list or what not. What you could do is add an extra column to the table where you shove in (save) the address id, and add a "default" check box. Then you can have a query with the default Address column = true to only pull the one address for mailing etc. So in this example, I do have a check box to select which out of possible many address are to be used by default. And we could add another column for home, business, holiday location or whatever. That way you can categorize the address to what it is.

However, the issue and your question is of course how to add a new address?

There is as many ways to do this as their are flavors of ice cream. In other words, it up to your creative mind. One way would be to use the combo box not in list event. So if you typed in a address id that don't exist, you could be prompted to enter it. However, users should never have to see, know, or use some silly "id" during data entry. Users in fact should never even see PK or FK id's during use of the application.

I would suggest you next to the combo box that lets you select a address is place a button to add a new address. You could use a graphic image like a + sign. So, they can use the combo box to select an existing address, or click on on the + button to add a new address. What the code behind the button would do is launch a form to edit the address, but launch the form in add mode. And when you close/save the form, then the code could/would stuff in the PK id of the new address you just added into the FK Address_ID column for the address.

And in the interest of cruelty to animals and your users? They should not need to know or see or type in some "number". The combo box can hide the first PK column. So when you select a given address (in this example hotel, but it could be just a address) you display the name. So, users in place of expanding the combo box can just start typing in the hotel name. To be fair, with address, you really don't have a "name" to type in, so your case is somewhat different. In most cases the thing you are selecting has some name, or part name/number, or in our case a Hotel Name.

Note that the sub form is based on a query that joins in the other columns. So, when they crack open the combo box, they are in fact selecting a PK. So like magic (and without code), then the city,province, description columns automatic display from that other table.

So the form would look like this:

In above, we selected several hotels. However, note the check box as to which address to use, since we might have several, but for printing a letter or envelope, then the check box will determine which address will appear.

And in the above, if we don't find or have a Hotel (or address) we want to use, then we can hit the + sign beside the combo box. That would launch up a nice single form layout to enter a new Hotel (or address). As noted, how you setup your UI to work is quite much a open canvas. One great way to get ideas is to look at other software you use. So, how do you add multiple address to outlook users? So, a gazilion ways and ideas exist as to how you want to do this. It not really how to do this, but HOW YOU want this to work, since you can design this to your hearts content to work quite much anyway you want.

So, the PK id should not be exposed here. Users can't care or know about such things. I mean, as I am typing this response, no doubt the SQL server that drives stack overflow generated a new number for this post - but it not a user thing, only a developer thing to care about.

Ok, so we build that sub form. The table driving that sub form is only

ID  (PK) 
Customer_ID   (FK to parent form driving this)
Hotel_ID      (FK to hotel - or in your case address)
DefaultAddress (true/false column)

And as noted, we should add ONE MORE column that sets the address as the default address to use. eg: default address.

. All of the other columns display as a result of a left join. (a left join for this sub form is VERY important).

Now, if a hotel is not in our list of choices, then the user can simply hit the + sign beside the combo box, and we pop up a nice form to edit/add a new hotel. And take well note that when you use outlook, an accounting system, or just about any software? You not seeing and typing in PK id values - that only really for you the developer to see, and end users should never see or have to deal with actual PK numbers.