Today I experienced a hard lock-up with MS-Access as I was in a form typing a new query.

The new query data is checked first, if valid, then the query is processed and ends in a SELECT SQL order which I save in the Form with a simple OpenRecordset call as in:

sql = "SELECT stuff FROM thing WHERE foo LIKE 'begin*'"
Set CUSTOMER_LIST.Form.Recordset = CurrentDb.OpenRecordset(sql)

That used to work just fine in older versions of MS-Access, but we now use MS-Access 2007.

The hanging happens on the 2^nd line, when the Set occurs. Note that it generally happens the second time you run the statement. The first time it works just fine.

Apparently this is a new bug and you have to deal with it (i.e. there is nothing to fix it in MS-Access itself, you instead have to "fix" your code.) From what I understand it could very well come from the WHERE clause and filtering and the fact that it tries to keep the previous record selection and the new record selection. Anyway, that's a Microsoft bug.

I found a page that mentioned the idea of reading the entire recordset before setting it in the form, this results in the following:

sql = "SELECT stuff FROM thing WHERE foo"
Set rcd = CurrentDb.OpenRecordset(sql)
If Not rcd.EOF() Then
    rcd.MoveLast
End If
Set CUSTOMER_LIST.Form.Recordset = rcd

I'll assume you'll Dim rcd if required in your code.

The result is that the rcd recordset is fully loaded before being assigned to the CUSTOMER_LIST table in our form and that works every time. If empty, you cannot call the MoveLast function. However, it is not necessary to call that function when rcd.EOF() is True so we're good.