CODEWITHSUNDEEP
ms-accessmany-to-manyms-access-2007
Techie_Gus
Techie_Gus

Reputation: 161

Data entry form/subform in a Many-to-Many relationship

I have an Access database with a many-to-many relation. The origin table is called Property and the destination table is called Person. A property can have more than one owner, and a person can own more than one property. I created a join table to accommodate this M-M relationship.

Here is the relationship layout:enter image description here

In order to fill these tables, I created a form for Property with a subform for the Person table. I followed several articles and posts to implement the needed functionality. They are here, here, here and here.

Here is the form: enter image description here

The PersonName is a combo box with its row source set to the following SQL query:

SELECT Person.idPerson, Person.PersonName FROM Person;

The column count is set to 2 and the width is set to 0cm;1cm

The VBA code I used for the NoInList event of the combo box is:

Private Sub PersonName_NotInList(NewData As String, Response As Integer)
strSQL = "INSERT INTO Person([PersonName]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new person has been added to the list." _
            , vbInformation, "Data Entry"
        Response = acDataErrAdded
End Sub

Everything is working fine so far but I'm faced with the case where two persons have the same name. The form won't allow this as every time you type a name that is already in the table, predictably you get the existing values associated with this person. Creating a new entry in the Person table makes this entry visible in the form's combo box, but I don't want the data entry user to edit the tables.

How can I implement the functionality to create a new entry in the Person table from the form, while asking the user to confirm the new entry?

P.S. I know the question title doesn't specifically reflect the content, but I couldn't find a better wording for it.

Edit: To keep the question simple, I trimmed down the fields in the tables. There are additional attributes like date of birth that can possibly differentiate between two persons with the same name.

Upvotes: 6

Views: 9081

Answers (2)

NXP5Z
NXP5Z

Reputation: 173

I had a similar problem recently. I second the suggestion with the popup form. My problem seems to be similar to the problem you have. Access doesn't like adding things on the one side of a relationship when the many side already has entries.

To second what acr_scout has said: You can simply add a button on your form that opens another form.

  1. Create a form that allows you to enter new persons to the persons table.

  2. In design view of your original form, add a button. In Access 2016, this should just be the one just called "button" (there are other buttons) Access will then start a wizard, and there is an option to select the on-click procedure that opens a form of your choosing.

    I just tried it out, it worked flawlessly and no VBA coding was necessary. You can also create a toggle button and add custom OnClick procedure yourself, if you so choose.

Another, less elegant approach might be to include a subform for the persons table in your original form.

EDIT: For what you are doing, no vba code should be ncessary, if I understood your problem correctly. Dr. Gerard Verschuuren has a great tutorial on YouTube, which helped me greatly when creating many-to-many forms.

Upvotes: 0

acr_scout
acr_scout

Reputation: 579

A recommended approach is to popup a form to add the new person to the person table. Your immediate problem of not being able to add the person is probably because you have the [PersonName] field either set as the primary key in the person table or you have that field set as an index duplicates not allowed.

I recommend that you consider looking up information on data normalization. This will help with understanding Primary and Foreign Keys.

Upvotes: 0

Related Questions