6

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.

Community
  • 1
  • 1
Techie_Gus
  • 161
  • 1
  • 6
  • Sounds like you need a surrogate key. What is the primary key of your Person table? – blobbles Jan 22 '17 at 22:47
  • If this is about handling data entry in the front end, you may be addressing your question at the wrong community and [SO] might be a better option. But I admit that it may not be that straightforward. In any event, the links you've provided are for you, not for us. In order for us to understand how *your* data entry is implemented, you need to explain, perhaps with illustrations, what *you* have done, and where specifically in your workflow the problem lies, because that doesn't seem to be crystal clear from your description so far. – Andriy M Jan 22 '17 at 23:56
  • @AndriyM '@blobbles' I have edited the post to add more info. I hope the problem is clearer now. If need be, I can upload a trimmed down copy of the DB. Thanks. – Techie_Gus Jan 23 '17 at 09:44
  • So the Property form allows the user to specify a list of owners for the Property item, and when the user specifies a name that isn't in the list, your NotInList handler adds that name to the Person table, and that person is also added to the linking table as an(other) owner of the Property item shown. And you want the user to be able to add a new owner that happens to have a name matching that of one of the existing people in the Person table, and the user can't do that because obviously the NotInList handler doesn't fire since the name *is* in the Person list. Is that the problem in hand? – Andriy M Jan 23 '17 at 10:39
  • @AndriyM That is exactly the problem! – Techie_Gus Jan 23 '17 at 10:41
  • All right. There's just one point I don't quite understand. You say, "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." What is that about? I mean, your data entry user *is* editing the Person table by adding new people to it. Or were you talking about a different process there? – Andriy M Jan 23 '17 at 10:44
  • @AndriyM What I meant is that I don't want the data entry user to wander away from the form to create a new entry directly in the Person table. I want to restrict the data entry to the form. – Techie_Gus Jan 23 '17 at 10:49
  • Let us [continue this discussion in chat](http://chat.stackexchange.com/rooms/52285/discussion-between-andriy-m-and-techie-gus). – Andriy M Jan 23 '17 at 10:53
  • I'm confused because it sounds like you want to add every entry made to your Owners subform to the Person table, but you're using a combo box, which is generally used to help a user select existing items. Is there any situation where you *don't* want to create a new Person record when the name is found in the combo box list? – John Mo Feb 07 '17 at 14:09
  • The situation arises when one person owns more than one building. In this case, the user will have to select from the combo box. I can add the "sex", or "date of birth" column to the drop down list, this way the user would be able to tell if the person is the same or not. – Techie_Gus Feb 07 '17 at 14:15
  • The fact that Person has additional fields and not just a name really means you shouldn't be using the "Not-In-List" handler to add items. Besides causing the problem you have (same name as another person) it also precludes users from providing the other details (sex, DOB, etc). I suggest you don't allow adding via the combo-box but instead provide a means to add persons separately (for example, a button going to another form). This keeps the sub-form/list in the Building form dealing just with adding Persons (owners) to Buildings. – andrew Nov 09 '17 at 03:04

2 Answers2

0

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.

acr_scout
  • 569
  • 1
  • 4
  • 24
0

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.

Community
  • 1
  • 1
NXP5Z
  • 173
  • 7