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:
Code:
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.