Duplicate Record Command Button Not Working For One Form But Is Working For Other Form

[mgrube]

I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are OrganizationsEntryForm and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization. See screenshots attached. On my OrganizationsEntryForm, I created a command button to duplicate a record using the wizard. It works fine.



I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?

Please respond at a 3rd grade level, I am learning Access as I go and often the answers given are well beyond my ability to understand. Thanks for your patience!

[June7]

Why would you duplicate record in either table?

What is the RecordSource of People form - table, query, SQL statement?

[mgrube]

Why would you duplicate record in either table?
It is possible that I will have a new record that will contain some of the same information as an existing record. To reduce the possibility of typing errors, I want to duplicate the existing record and change only the fields that are different.

What is the RecordSource of People form - table, query, SQL statement?
The source for the people form is both the people table and the organization table. On the people form, when selecting the organization for the person, it uses the Organization link that I show on the relationship chart attached to my question. I also include some other information from the organization table on the people form in a read-only format so they can verify they have indeed selected the correct organization.

By the way, I am able to copy and paste append a record when in the table view. It is only in the people form view that I cannot create a new record with the same information. None of my fields are defined as forbidding duplicates, so that shouldn't be the problem.

[June7]

Okay, it's a query joining both tables - then does the query use INNER JOIN? Should be LEFT or RIGHT (I forget which) - "Include all records from People and only those records from Organizations where the joined fields are equal."

[mgrube]

I am not sure how to answer your question. I am attaching screen shots of the relationship editing dialog boxes. If this does not answer your question, please provide directions on where to find the answer.
Thank you again for your patience!

[June7]

If this is the form's RecordSource query, need to select option 3 in the Join Properties.

[mgrube]

No, I am sorry I misunderstood your question. I had provided the relationship between the tables. I now went to the People form and open the recordsource for the form, I get the attached. I do have 3 selected.

[June7]

Then I don't understand why this fails.

If you want to provide db, follow instructions at bottom of my post.

[mgrube]

Here is a copy of the file with the confidential data removed and fake data entered. Any help would be greatly appreciated.

[June7]

No problem manually creating a new record with same data as another.

Organizations form RecordSource is the table, not a query.

I think the query including Organizations table in RecordSource is issue with People form. Your macro does a copy/paste of a record from the form. The record includes Organizations fields. This macro method will not work for this arrangement. I removed Organizations table and unbound the controls then macro works.

I don't use macros, only VBA.

Alternatives for displaying the organization info:

1. combobox with filter parameter referencing the organization ID from People table. Can make not visible. Then textboxes can have expressions referencing columns of combobox. Will need code to requery the combobox when navigating records - use form Current event: Me.comboboxname.Requery

2. DLookup

[mgrube]

Thank you for the guidance. I will need to do more research to learn how to implement your alternative #1 and play with it to figure out how to make it work. Regarding alternative #2, I have tried using DLookup for another task and was unsuccessful. Learning more about DLookup was already on my list of things to do, now I have two reasons to learn it.

Thank you for the time you took to answer my questions. Learning as I am doing is a challenge, but also a lot of fun. I appreciate your straight forward approach to helping me find the cause for my problem and providing a direction for correcting it.

Have a great day!

[mgrube]

Hello again! Well after some research and trials, I was able to figure out how to use DLookup to display the Organization information. I kept them as text boxes but changed the control source to be a DLookup. I am happy I learned how to use the DLookup. However, the duplicate record still isn't working. I am attaching my test file with the revised form. Did I misunderstand your direction regarding DLookup? I also tried to go into the code and use VBA, but that is beyond my abilities right now.

Thanks again for the time you spend helping out novices like me!

[June7]

See post 10 comment: I think the query including Organizations table in RecordSource is issue with People form. Your macro does a copy/paste of a record from the form. The record includes Organizations fields. This macro method will not work for this arrangement. I removed Organizations table and unbound the controls then macro works.

[mgrube]

Ahhhh!! I didn't realize that removing the Org table reference for each box didn't clear the record source for the form!! I fixed that and now the button appears to work!! Thank you again for your patience!