Attached is an example of the functions I'm trying to accomplish.
Basically, the two tables (tblApplicants, tblInstructors) are one-to-one related to tblContacts, (just a subset of data), however, tblInstructors and tblApplicants are also part of a many-to-many relationship not shown. The purpose of the macros (mcrAddApplicant and mcrAddInstructor) are to add a record from tblContacts to one of the others if they are not there already. The macro is triggered from a module in the frmContacts when one of the checkboxes is checked (i.e. AIAQTP Applicant triggers a module that calls the mcrAddApplicant).
The mcrAddApplicant does its job perfectly. Searches for existing record and if not found, adds a new one and displays the record contents in the form.
The mcrAddInstructor appears to add the record to the form, and in fact does add it to the underlying table (tblInstructors), but gets stuck in two places:
1. does not display record information in the form as it should
2. if you search the form (custom search form, not included in example), the record appears (as it does in the tblInstructors), but when you try to open that record, there is nothing in the form itself
3. Also, when you try to go back to frmContacts and re-add the instructor by unchecking then checking the box again, the macro gets stuck on the Requery action stating that the record already exists
It shouldn't come up with this error message because the module/macro should be double-checking for the existing record and just "finding" it instead of going to a new record and adding it.
I hope I've provided enough information for someone to come to my rescue. I'm stumped because as far as I can tell, all modules, macros, forms and queries are basically configured the same way (between Applicants and Instructors), so I'm confused why one works and one doesn't...
By the way, after running this macro, the frmContacts also seems to be adding a blank record to tblContacts. I'm not sure if it's related to the macro or to the code behind frmContacts...
Basically, the two tables (tblApplicants, tblInstructors) are one-to-one related to tblContacts, (just a subset of data), however, tblInstructors and tblApplicants are also part of a many-to-many relationship not shown. The purpose of the macros (mcrAddApplicant and mcrAddInstructor) are to add a record from tblContacts to one of the others if they are not there already. The macro is triggered from a module in the frmContacts when one of the checkboxes is checked (i.e. AIAQTP Applicant triggers a module that calls the mcrAddApplicant).
The mcrAddApplicant does its job perfectly. Searches for existing record and if not found, adds a new one and displays the record contents in the form.
The mcrAddInstructor appears to add the record to the form, and in fact does add it to the underlying table (tblInstructors), but gets stuck in two places:
1. does not display record information in the form as it should
2. if you search the form (custom search form, not included in example), the record appears (as it does in the tblInstructors), but when you try to open that record, there is nothing in the form itself
3. Also, when you try to go back to frmContacts and re-add the instructor by unchecking then checking the box again, the macro gets stuck on the Requery action stating that the record already exists
It shouldn't come up with this error message because the module/macro should be double-checking for the existing record and just "finding" it instead of going to a new record and adding it.
I hope I've provided enough information for someone to come to my rescue. I'm stumped because as far as I can tell, all modules, macros, forms and queries are basically configured the same way (between Applicants and Instructors), so I'm confused why one works and one doesn't...
By the way, after running this macro, the frmContacts also seems to be adding a blank record to tblContacts. I'm not sure if it's related to the macro or to the code behind frmContacts...
Last edited:
