Requery not working?

Question

Hi:

I have a form based on a query. I have a combobox which can be used to modify the query's criteria. This is done in code (below...).

I have confirmed that the code is correctly rebuilding the query. However, it does not appear to requery the form; in other words, the query *is* changed, but "requerying" the form does not change the records displayed. Why?

Note: I have made this work by changing the recordsource in code (me.frmIn.recordsource="qryTransactionWith Criteria", so, I guess that works, but I am puzzled about why (me.requery) doesn't?

Thanks!

Fred

Code (runs on AfterUpdate event of cboEquipmentID):

Option Compare Database

Option Explicit

Function BuildSQLString(sSQL As String) As Boolean

On Error GoTo ErrorHandler

Dim sSELECT As String

Dim sFROM As String

Dim sWHERE As String

sSELECT = "qryTransactionReturnedOnIsNull.* "

sFROM = "qryTransactionReturnedOnIsNull"

If Len(cboEquipmentID) > 0 Then

       sWHERE = " AND EquipmentID = " & cboEquipmentID

End If

sSQL = "SELECT " & sSELECT

sSQL = sSQL & "FROM " & sFROM

If sWHERE <> "" Then sSQL = sSQL & " WHERE " & Mid$(sWHERE, 6)

BuildSQLString = True

ExitPoint:

    Exit Function

ErrorHandler:

    MsgBox Err.Number, Err.Description

    Resume ExitPoint

End Function

Private Sub cboEquipmentID_AfterUpdate()

On Error GoTo ErrorHandler

Dim sSQL As String

Dim lRecordsAffected As Long

If Not BuildSQLString(sSQL) Then

    MsgBox "There was a problem building the SQL string"

    Exit Sub

End If

'Recreate the query called qry1 with the new criteria

CurrentDb.QueryDefs("qryTransactionWithCriteria").SQL = sSQL

If DCount("*", "qryTransactionWithCriteria") <> 0 Then

Else

MsgBox "There are no matches for these search terms. Please try again.", _

    vbOKOnly + vbInformation, "VRS Equipment"

End If

Me.Form.RecordSource = "qrytransactionwithcriteria"

ExitPoint:

    Exit Sub

ErrorHandler:

    MsgBox Err.Number, Err.Description

    Resume ExitPoint

End Sub

Private Sub Form_Activate()

Me.Requery

End Sub

Answer 1

I can't quite figure out what you are trying to do here.  The fact that you include a Boolean AND operator in the sWHERE variable suggests that you might be trying to select multiple EquipmentID values successively, but the code doesn't allow that as (a) the sWHERE variable is declared locally in the function, and is not declared Static, so will be reinitialised to a zero length string at each function call, and (b) the Boolean operator would need to be OR, not AND.

If you are trying to select multiple EquipmentID values, then a much simpler solution would be to use a multi-select list box rather than a combo box, and then filter the form on the basis of the selections, for which the code would be:

    Dim ctrl As Control

    Dim varItem As Variant

    Dim strEquipmentList As String

    Set ctrl = Me.lstEquipment

    If ctrl.ItemsSelected.Count = 0 Then

        ' remove filter if no items selected

        Me.FilterOn = False

    Else

        For Each varItem In ctrl.ItemsSelected

            strEquipmentList = strEquipmentList & "," & ctrl.ItemData(varItem)

        Next varItem

        ' remove leading comma

        strEquipmentList = Mid(strEquipmentList, 2)

        ' apply filter

        Me.Filter = "EquipmentID IN(" & strEquipmentList & ")"

        Me.FilterOn = True

    End If

This could go either in the list box's AfterUpdate event to progressively filter the form at each selection, or, more efficiently, in a separate button's event procedure.

If, on the other hand, you are, trying to restrict the form's record to one equipment ID then a combo box with the following code in its AfterUpdate event procedure would suffice:

    Dim strFilter As String

    strFilter = "EquipmentID = " & Me.cboEquipment_ID

    If Not IsNull(Me.cboEquipment_ID) Then

        Me.Filter = strFilter

        Me.FilterOn = True

    Else

        Me.FilterOn = False

    End If

Answer 2

Chuckle... Obviously I was having even more trouble figuring out what I was trying to do than you were! What you were looking at was an attempt to rework code from another application I did many years ago. I haven't worked with Access for 4 or 5 years - obviously, I've forgotten just about everything I once knew!

In simple terms I was actually trying to be able to filter a form based on two different criteria (EquipmentID, and ConsultantID). I wanted to use 2 comboboxes in the header of the form to filter by either a single EquipmentID or a single ConsultantID.

Setting the form filter is, of course, effective and simple. Thank you!

Fred

Answer 3

Whoops! This is the latest version of the code where I reset the recordsource... Assume that the line "

Me.Form.RecordSource = "qrytransactionwithcriteria"

actually reads

Me.requery