Data Macro keeps giving me error when I use forms to enter data (1 Viewer)

Db-why-not

Registered User.
Local time
Today, 15:36
Joined
Sep 17, 2019
Messages
159
I created a Data Macro with my backend table. I used the After Insert event for my New_Patients table, it triggers a new record to be created in my Patient_Status table. The two tables are linked. The Data Macro works great when I add new records directly in the New_Patients table, It will create a new record in the Patient_Status table but when I use my forms to create a new record it gives me an error message. "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

My Main form has text boxes to create records for the New_Patients table Research_ID (Primary Key) and Chron textbox. All the subforms on the mainform are linked to the Research_ID field that is on the mainform and is in the New_Patients table. Then there is a subform with textboxes for the Patient_Status table, I have that subform linked by the Research_ID field.

What might be causing this error. The data Macro works great when I'm not using the forms.
I have attached screen shots of the data macro and the error message, screenshot of my forms.
My subform for table Patient_Status includes data fields Status, Still_Admitted, Data_last_updated, Research_ID (Foreign Key)

Thanks for any help!
 

Attachments

  • Macro-Creates new record.JPG
    Macro-Creates new record.JPG
    42.7 KB · Views: 168
  • the changes requested not successfuol.JPG
    the changes requested not successfuol.JPG
    27.2 KB · Views: 156
  • form-screenshot.JPG
    form-screenshot.JPG
    65.8 KB · Views: 167

theDBguy

I’m here to help
Staff member
Local time
Today, 13:36
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a guess but if your form is also updating the Status table, then maybe you're creating a conflict between the form and the data macro. Also, I can't tell you if the problem is about a duplicate record, but that doesn't make sense unless you did set up a unique index in the Status table.
 

Db-why-not

Registered User.
Local time
Today, 15:36
Joined
Sep 17, 2019
Messages
159
I will try doing this data macro to create a new record in a different table and see if I still get the error message.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:36
Joined
Oct 29, 2018
Messages
21,358
I will try doing this data macro to create a new record in a different table and see if I still get the error message.
Remember, you were getting the error when using your form. So, if you're not getting an error when manually updating the table directly, then the problem is with your form - not the macro (I don't think).
 

Db-why-not

Registered User.
Local time
Today, 15:36
Joined
Sep 17, 2019
Messages
159
Remember, you were getting the error when using your form. So, if you're not getting an error when manually updating the table directly, then the problem is with your form - not the macro (I don't think).
My main form has buttons that open up different subforms in a subform container. I'm going to try having the data macro create a new record for one of my subforms(table) that isnt automatically open. Then I will try to create a new record on my main form. My patient_status table/subform is always open with the new_patients table/form when the form first opens.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:36
Joined
Oct 29, 2018
Messages
21,358
My main form has buttons that open up different subforms in a subform container. I'm going to try having the data macro create a new record for one of my subforms(table) that isnt automatically open. Then I will try to create a new record on my main form. My patient_status table/subform is always open with the new_patients table/form when the form first opens.
Okay, let us know how it goes.
 

Db-why-not

Registered User.
Local time
Today, 15:36
Joined
Sep 17, 2019
Messages
159
It worked. The Patient_Status form was creating a duplicate record. I can use this Data macro, just for a different table/subform now. Thanks for all your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:36
Joined
Oct 29, 2018
Messages
21,358
It worked. The Patient_Status form was creating a duplicate record. I can use this Data macro, just for a different table/subform now. Thanks for all your help.
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

zeroaccess

Active member
Local time
Today, 15:36
Joined
Jan 30, 2020
Messages
671
Can you describe what created the need for the data macro? Will others be able to figure out how your db works, since it won't be apparent from the form design?
 

Users who are viewing this thread

Top Bottom