Funny Listbox side effect when updating Form.RecordSource

[Calvin]

Ok, I've got a new one, but first some background: I have an ADP project in Access 2003, our business is upgrading to Office 2007, and the solution/problem I'm about to explain works fine in 2003 adp, as it always has since it was in 97 mdb, then 2000 mdb then 2000 adp, then 2003 adp. Now with exactly the same adp file in 2007, I have a problem that's not appeared before.

The Process:
On a continuous form I have a listbox in the header, this listbox is a multi-select containing various status', when the listbox on click event executes it rebuilds the sql statement for the forms.RecourdSource, or more specifically assembles the where criteria from the selected value in the list box then updates the forms recordsource, in effect filtering the data that is visible on the same form.

pseudo code:

Sub lstStatus_Click
    {loop through listbox items and read selected items, remember it's multi-select, and return strWhere}

    strSQL = "Select * From MyTable Where " & strWhere
    
    me.RecordSource = strSQL

End Sub

The Problem:
Now in 2007 when the step me.RecourdSource = strSQL is executed it is forcing a form wide refresh that clears the selected value in the listbox. Previously the value selected always stayed selected, allowing other functions on the form to also read it's value for use elsewhere.

The Question:
Has anyone else seen this issue or have similar experience? and can it be solved?

 

[Calvin]

I've written a work around for solving this, where I just put a loop near the end of the subroutine that re-selects the values in the list.

pseudo code:

Sub lstStatus_Click
    {loop through listbox items and read selected items, remember it's multi-select, and return strWhere}

    strSQL = "Select * From MyTable Where " & strWhere
    
    me.RecordSource = strSQL

[COLOR="Red"]    strWhere= Replace(strWhere, "[Status]=", "")
    strWhere= Replace(strWhere, "' OR '", ";")
    strWhere= Replace(strWhere, "')", ";)")
    strWhere= Replace(strWhere, " '", "")

    For i = 0 To Me.lstStatus.ListCount - 1
        If InStr(1, strWhere, Me.lstStatus.Column(0, i) & ";", vbTextCompare) > 0 Then
            Me.lstStatus.Selected(i) = True
        End If
    Next i[/COLOR]

End Sub

I would prefer not having to do the extra steps, but sometimes we have to do what we have to do.

I'm considering this solved for now, but if anyone knows more about this issue please post a reply.

 

[nIGHTmAYOR]

Odd , i had that once in office 2007 beta but not in final release , back then i solved that by relying on Form Filter property not Record Source then issued a Requery command on done. I'd suggest you download latest service packs but if i know ms access correctly then this wouldnt solve anything so i suggest you stick to Filters and Requeries