Code:
Dim lngPK As LongDim strWhere As Variant
Private Sub ActiveRecords_AfterUpdate()
lngPK = Me.EquipmentID
If Me.cboFilterBy = 1 Then
If Me.ActiveRecords = True Then
Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
" FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
" WHERE Equipment.Inactive = False" & _
" ORDER BY Equipment.EquipmentID"
Else
Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
" FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
" ORDER BY Equipment.EquipmentID"
End If
End If
If Me.cboFilterBy = 2 Then
If Me.ActiveRecords = True Then
Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
" FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
" WHERE EquipmentType.EquipmentTypeID = 1 AND Equipment.Inactive = False" & _
" ORDER BY Equipment.EquipNumber"
Else
Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
" FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
" WHERE EquipmentType.EquipmentTypeID = 1" & _
" ORDER BY Equipment.EquipNumber"
End If
End If
If Me.cboFilterBy = 3 Then
If Me.ActiveRecords = True Then
Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
" FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
" WHERE EquipmentType.EquipmentTypeID = 2 AND Equipment.Inactive = False" & _
" ORDER BY Equipment.EquipNumber"
Else
Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
" FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
" WHERE EquipmentType.EquipmentTypeID = 2" & _
" ORDER BY Equipment.EquipNumber"
End If
End If
If Me.cboFilterBy = 4 Then
If Me.ActiveRecords = True Then
Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
" FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
" WHERE EquipmentType.EquipmentTypeID = 3 AND Equipment.Inactive = False" & _
" ORDER BY Equipment.EquipNumber"
Else
Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
" FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
" WHERE EquipmentType.EquipmentTypeID = 3" & _
" ORDER BY Equipment.EquipNumber"
End If
End If
If Me.cboFilterBy = 5 Then
If Me.ActiveRecords = True Then
Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
" FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
" WHERE Equipment.Inactive = False" & _
" ORDER BY Equipment.EquipNumber"
Else
Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
" FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
" ORDER BY Equipment.EquipNumber"
End If
End If
'From http://www.baldyweb.com/Requery.htm
With Me.RecordsetClone
.FindFirst "EquipmentID= " & lngPK
If .NoMatch Then
MsgBox "Record not found!", vbCritical
Else
Me.Bookmark = .Bookmark
End If
End With
Me.txtFilter.SetFocus
End Sub