Strange multi select listbox error

[chad101]

I created a multi-select listbox which I populate with “distinct” furnace names that have been entered into an inspection table. The end user has the option to delete records from the inspection table. The listbox gives the end user the option to filter results on a sub-form, nothing more. When the last record relating to a specific furnace is deleted; the multi-select listbox removes that furnace name.

The problem is the row where the old furnace name existed is still highlighted, after the value is removed. So when a user selects another furnace name(s) they get an SQL error because a null value is highlighted in the listbox. Clicking on the empty selection will not deselect it. I have to close and reopen the form for this to go away. I tried requery, recalc, and refreash.

The only fix I have right now is to do an isnull() check for each selected item. If the selected item is null then it’s not placed in my query string.

I made a conditional expression which checked for null selections. If true, I deselected all the values and requeryed the listbox to see if it would remove the empty selections...nope!

 

[boblarson]

Try using

Me.YourListBoxNameHere.Requery
Me.YourListBoxNameHere = ""

You may have to reverse the order of those.

 

[chad101]

Sorry for my late response. I tried that, both ways. Same problem with listbox highlighting blank rows after the furnace is deleted.

I found a fix which I’m about 90% finished coding. When a user clicks the delete button. I run a query which gives me a count for the furnace name in the record about to be deleted. If the count is equal to 1, the code un-highlights that furnace name in the list box, then proceeds with the deletion.

The only problem now is if this furnace name was not at the bottom in the listbox. The next furnace will take its place and remain un-highlighted lol. So my solution for that is to loop through the listbox first and place each furnace id into an array. After the furnace is deleted I then re-loop through the listbox and highlight the original highlighted furnace names.

I know there is a more simple solution than this. But I’m tired of racking my head on this problem for the last 2 days lol.