"Run time error 3167: Record is deleted" when unchecking a selection in multi select listbox

0
votes

I have a form that contains a multi-select list box, a user can check off as many selections as required, then hit the "save" button, which saves the form fields, and the checked off selections in their respective tables.

Everything works perfectly fine, until a user unchecks a selection that had previously been saved.

I have tried several things, including refreshing the form, and the recordset, to no avail.

While testing to find a solution, I commented out every single line of VBA code tied to the form's save button. When clicking this button, obviously nothing happens with the code commented out, but if I try and close the form with the window's "x" button, I then get a message box stating "Record has been deleted"

I have no idea where to look at this point, I've tried to compact and repair database, also with negative results.

I have no code to post, as it doesn't matter what code I try to run, I get the error, a completely empty sub still gives me the error.

Again, this only happens when a user unchecks a selection that previously had been checked off.

Here is a screen shot of the form: enter image description here

The list box in red is the culprit.

A user can edit every single field on the form and it all works fine, a user can even "add" selections from the listbox by checking items off, and it will save them, and show them the next time the record is viewed.

The problem occurs when a user unchecks one of the selections that were previously checked off.

Details of the listbox:

  • It is a multi select list box populated by a "lookup" that was created with the listbox wizard
  • The values selected are saved in a field as a comma separated list
  • The field itself is a lookup of another table, that allows multiple values

At this point I'm not even sure I'm explaining myself properly, I've gone so far down the "rabbit hole"!

If any clarifying statements are needed please ask away.

1
ms-accessvbams-access-2010
If you open the form, do nothing to it, then click close you get an error? How exactly are the form controls bound to recordsets? Have you tried a REQUERY instead of a REFRESH? What is the 'ControlSource' of the listbox? - Wayne G. Dunn
@WayneG.Dunn, I have edited my question to answer some of your questions. Yes I tried the REQUERY, I simply get an error that tells me there's no record loaded yet. The control source is a lookup field stored in the table this form edits. - David Morin
Please explain what your 'listbox' really is. Is it a 'ListView' or a 'ListBox' that is a customized solution. How is your checkbox column inserted? Any code to build it? - Wayne G. Dunn
I used the listbox wizard on the form design ribbon, when asked for values, I selected a table already created containing the values you see in the screenshot, then selected finish. Once it was created, I assigned the source of the listbox to be the corresponding field in the "tow" table. That field in the tow table, is a "lookup" field that I also created with the built in lookup wizard, allowing for multiple values. I don't have any code that creates it, just whatever access did when I used the wizard. - David Morin
Is this a 'bound' form, or unbound? If bound, then what code handles the listbox selections? You set the 'ControlSource' for the listbox to to a field in the 'Tow' table? (1) Is the 'Tow' table where all the other data on that form is stored? (2) Never saw a multi-select listbox linked to a table column - are you sure about that? No code? Finally, you have a 'lookup' field in the 'Tow' table, but what is that based off of? Same table, or different? - Wayne G. Dunn

1 Answers

1
votes

The following describes my implementation of what I think you were trying to do, but there may be some variations. The key point is I was able to reproduce your 'Deleted record' error on a regular basis, but somehow finally got it to stop.

  1. I created a table named 'res_area' with an ID field and an 'area'. I populated with rows for '1E, 1F, 1I, 1J, 3C, 3D, 3E, 3F, 3I, 3J, 3K, etc.'
  2. I created table 'res_tow' with all the fields you show on your form. I included a field named 'area' that is a LOOKUP field with the following source: "SELECT [res_Area].[TTID], [res_Area].[Area] FROM [res_Area] ORDER BY [Area];". Allow Multiple Values = yes.

  3. I created form 'frmEditTow' with the Record Source:

    SELECT res_Tow.TowID, res_Tow.TCompany, res_Tow.TPhone1, res_Tow.TPhone2, res_Tow.TPhone3, res_Tow.TType, res_Tow.TTown, res_Tow.TAddress, res_Tow.TFileName, res_Tow.TComments, res_Tow.TChecks, res_Tow.area FROM res_Tow;

  4. I added the 'Save Record' button with code to: 'DoCmd.RunCommand acCmdSaveRecord' and 'Me.Requery'

  5. I am able to add or delete any combination of list items and save the changes.

For what its worth, I think my earlier version of the rowsource for the form included field 'area' and 'area.value'. With that, the form recordcount reflected the total number of listbox items selected - not the number of rows in table 'res_Tow'.

Good luck!