Hello. I've recently started using Access for my job and have encountered an issue when registering new records in a form.
---- Background ----
To describe my problem I'll use an example of a many to many relationship between Person and Activity:
tbl_Person, tbl_Activity, tbl_PersonHasActivity (junction table).
I successfully created a split form (source: tbl_Person) with a subform (source: tbl_PersonHasActivity) for registering a person with activities, where the user had to select each individual activity using a drop down list for each item. But it turns out that the user wants a checklist to make activity selection faster and more "visible".
In order to achieve this I removed the subform and added a multi select listbox ("ActivityList", source: tbl_Activity) to the person registration form. I then wrote some vba code in the ActivityList_AfterUpdate() event to add and delete records in tbl_PersonHasActivity based on selected items in ActivityList. In this code i make a reference to Forms![PersonRegistration].RecordSet("PersonID") which causes problems when adding new records.
---- Problems ----
1. Accessing the PersonRegistration forms' recordset in ActivityList_AfterUpdate() works fine when working on existing records, but as soon as I add a new record Access seems to completely lose track of what record it is working on, and the only fix is to close the form and reopen it. Note that this only happens if I previously have clicked in the ActivityList of an existing record before creating the new record. After deleting some vba code, I have figured out that the reference to the forms recordset causes the problem.
2. Updating records. When i edit the tbl_Person.Address field and then click a different record in the splitform, the new address is stored. If i edit the tbl_Person.Address field and then click in the ActivityList and then on a different record, the new address is not stored.
I hope someone can help me understand why this happens, and if someone knows of a better way of making the checklist solution work that would be great
. I would prefer not having to lock the form to using a specific set of activities, as i want changes in tbl_Activity to be reflected in the PersonRegistration form without further work.
Edit: I'm using ODBC linked tables. Maybe that is relevant?
---- Background ----
To describe my problem I'll use an example of a many to many relationship between Person and Activity:
tbl_Person, tbl_Activity, tbl_PersonHasActivity (junction table).
I successfully created a split form (source: tbl_Person) with a subform (source: tbl_PersonHasActivity) for registering a person with activities, where the user had to select each individual activity using a drop down list for each item. But it turns out that the user wants a checklist to make activity selection faster and more "visible".
In order to achieve this I removed the subform and added a multi select listbox ("ActivityList", source: tbl_Activity) to the person registration form. I then wrote some vba code in the ActivityList_AfterUpdate() event to add and delete records in tbl_PersonHasActivity based on selected items in ActivityList. In this code i make a reference to Forms![PersonRegistration].RecordSet("PersonID") which causes problems when adding new records.
---- Problems ----
1. Accessing the PersonRegistration forms' recordset in ActivityList_AfterUpdate() works fine when working on existing records, but as soon as I add a new record Access seems to completely lose track of what record it is working on, and the only fix is to close the form and reopen it. Note that this only happens if I previously have clicked in the ActivityList of an existing record before creating the new record. After deleting some vba code, I have figured out that the reference to the forms recordset causes the problem.
2. Updating records. When i edit the tbl_Person.Address field and then click a different record in the splitform, the new address is stored. If i edit the tbl_Person.Address field and then click in the ActivityList and then on a different record, the new address is not stored.
I hope someone can help me understand why this happens, and if someone knows of a better way of making the checklist solution work that would be great
. I would prefer not having to lock the form to using a specific set of activities, as i want changes in tbl_Activity to be reflected in the PersonRegistration form without further work.Edit: I'm using ODBC linked tables. Maybe that is relevant?
Last edited: