Write conflict in very simple Access form with subform

1
votes

This one is stumping me because what I'm doing seems so straightforward.

I have an unbound form (frmAdmin) with a listbox (lstUsers) and a subform that also contains a listbox (lstUsergroups).

lstUsers is unbound and shows a list of usernames (row source is the users table left joined with the contacts table on users.fk__ContactID = contacts.pk__ContactID so I can get the names to display)

The subform is bound to the users table and has the filter pk__UserID = Forms!frmAdmin!lstUsers

lstUsergroups shows a list of usergroups as is bound to the fk__Usergroup field in the users table.

The only VBA involved is a click event on lstUsers that requerys the subform.

When I select a user from lstUsers the subform listbox shows the user's usergroup selected, but if I select a different usergroup and then select a different user or close the form I get the write conflict "This record has been changed by another user" etc.

This seems so simple! What could cause a write conflict? There are no other forms open, the users table is a linked SQL Server table but there are no triggers anything that should be writing to it. It's just a form, a subform, and a click event.

1
ms-accessvba
Why are you using listboxes instead of forms/subforms? I would never model what you've described with a listbox on the subform (though certainly a combo box is likely). – David-W-Fenton
I am using a subform, and it makes sense for how I want the UI to work. There should be a list of users, and a list of usergroups. When I select a user their usergroup should appear selected, and then I should be able to change their usergroup by selecting another item in the usergroups list. As far as I can tell this isn't an issue with the control type, but tell me if I'm wrong. – jasongetsdown
Forms and subforms work very well for lists, and the data in them is editable, which is not the case with listboxes. It all depends on what you want to do, but a continuous form as the parent (with perhaps a listbox for navigating), and a datasheet subform (no listbox, but a combo box for choosing users) would be the normal UI, seems to me. – David-W-Fenton

1 Answers

1
votes

Make sure the SQL table has a primary key, then update your linked table. If that doesn't seem to resolve it, try adding a timestamp field to the SQL table that's getting the write conflict, then update the linked table again. This happens to me all the time. Access seems finicky about what it actually uses as unique identifiers sometimes.