AfterUpdate requery does not requery list box

[ittechguy]

I'm building a rather complex search form (for my understanding of access). The project is coming along nicely. One of the problems I'm having is that my list boxes are not re-querying after the values in txtBuildingID and txtRoomsID changes. You can see in the photo below, there are 19 records. I can scroll through the records or I can search for records to narrow down results. Every time I go to a different record there are different results in both text boxes. I need the list boxes below (lstFacilityMgr, lstRoomsPOC, and lstEquipmentInRoom) to requery. For some reason, this is not happening. I tried using Change instead of AfterUpdate, same results.



My code is below:

Option Compare DatabaseOption Explicit  'always set this  It will point out errors with field/vaiable names




Private Sub cboSearchLastName_AfterUpdate()
    Me.cboSearchFirstName.Requery
End Sub


Private Sub cboSearchOrganization_AfterUpdate()
    Me.cboSearchShopName.Requery
End Sub


Private Sub cboSearchShopName_AfterUpdate()
    Me.cboSearchOfficeSym.Requery
End Sub


Private Sub cmdReset_Click()
    Me.cboSearchBuildingName = ""
    Me.cboSearchRoomName = ""
    Me.cboSearchOrganization = ""
    Me.cboSearchShopName = ""
    Me.cboSearchOfficeSym = ""
    Me.cboSearchLastName = ""
    Me.cboSearchFirstName = ""
    Me.FilterOn = False
End Sub
Private Sub txtBuildingID_AfterUpdate()
    Me.lstFacilityMgr.Requery
End Sub


Private Sub txtRoomsID_AfterUpdate()
    Me.lstRoomsPOC.Requery
End Sub
Private Sub cmdSearch_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Dim startStr As String
    If Not IsNullOrEmpty(Me.cboSearchLastName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        
        strWhere = strWhere & startStr & "[LastName] ='" & Me.cboSearchLastName & "'"
    End If
    If Not IsNullOrEmpty(Me.cboSearchFirstName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        
        strWhere = strWhere & startStr & "[FirstName] ='" & Me.cboSearchFirstName & "'"
    End If
    If Not IsNullOrEmpty(Me.cboSearchOrganization) Then
        startStr = IIf(strWhere = "", "", " AND ")
        
        strWhere = strWhere & startStr & "[OrganizationFK] =" & Me.cboSearchOrganization
    End If
    If Not IsNullOrEmpty(Me.cboSearchShopName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        
        strWhere = strWhere & startStr & "[ShopNameFK] =" & Me.cboSearchShopName
    End If
    If Not IsNullOrEmpty(Me.cboSearchOfficeSym) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[OfficeSymFK] =" & Me.cboSearchOfficeSym
    End If
    If Not IsNullOrEmpty(Me.cboSearchBuildingName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[BuildingFK] =" & Me.cboSearchBuildingName
    End If
    If Not IsNullOrEmpty(Me.cboSearchRoomName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[RoomsPK] =" & Me.cboSearchRoomName
    End If
    If Not IsNullOrEmpty(Me.cboSearchEquipmentName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[EquipmentNameFK] =" & Me.cboSearchEquipmentName
    End If
    If Not IsNullOrEmpty(Me.cboSearchEquipmentSerialNo) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[SerialNoFK] =" & Me.cboSearchEquipmentSerialNo
    End If
    Call MsgBox(strWhere, vbOKOnly, "Debug")
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        'strWhere = Left$(strWhere, lngLen)
        MsgBox strWhere
         If DCount("*", "qryRecordSet", strWhere) = 0 Then
                MsgBox "No corresponding records to your search criteria." & vbCrLf & vbCrLf
                Me.FilterOn = False
                Me.cboSearchBuildingName = ""
                Me.cboSearchRoomName = ""
                Me.cboSearchOrganization = ""
                Me.cboSearchShopName = ""
                Me.cboSearchOfficeSym = ""
                Me.cboSearchLastName = ""
                Me.cboSearchFirstName = ""
         Else
            Me.Filter = strWhere
            Me.FilterOn = True
        End If
    End If
End Sub






Function IsNullOrEmpty(val As Variant) As Boolean
   'First conditional validates for Nothing
   'Second condition validates for an Empty String situation "" or "     "
   Dim ret As Boolean: ret = False
   If IsMissing(val) Then
      ret = True
   ElseIf (val Is Nothing) Then
      ret = True
   ElseIf (val & vbNullString = vbNullString) Then
      ret = True
   ElseIf (Len(Trim(val)) <= 0) Then
      ret = True
   End If
 
   IsNullOrEmpty = ret
End Function

Thank you for your help. I hope its an easy fix.

[pbaldy]

Those events only fire if the user enters something in the textbox. If you want to react to changing records, try the current event.

[ittechguy]

Those events only fire if the user enters something in the textbox. If you want to react to changing records, try the current event.

OMG you're a genius! That worked perfectly. Thanks.

[pbaldy]

Happy to help!

[Erictsang]

dear Pbaldy

I am having the similar problem

i have an input form with a combox or user to choose

Based on the choice, it triggers the query

But if we do re-enter again. the query do not change, i have either close it and reopen or click the refresh twice

How can i solve it /

Eric

[pbaldy]

If the query is used for a form, you'd have to requery the form. I wouldn't open a query directly, and I doubt there's a way to control it if that's what you're doing.