How to limit a search on one field based on data in another field.

[donnie.atchison]

I have a data base used for cargo tracking. In cargo tracking we use multiple gps devices on a shipment. Each GPS Device (Tracker) is identified by a 4 digit number. The tracker will be assigned to the shipment in the database. When the shipment is complete the GPS Device (Tracker) is mailed back to me and I enter it as returned in the database. What I am trying to do is build a return form that will only display the record where the device number exists with out device returned check box is false.



I am able to search for the device but do not know how to have it filter by the Device return check box.

Here is a screen shot of the return form screen.


This is the VBA that I have at this point. Once again it will search for the tracker but I need it only to display the record that contains the GPS device that has not been checked returned in the Tracker Returned check box.

Private Sub txtSearch_AfterUpdate()
Dim strFilter As String
Dim sSearch As String

If Me.txtSearch <> "" Then
sSearch = "'*" & Replace(Me.txtSearch, "'", "''") & "*'"
strFilter = "[Tracker Assigned 1] Like " & sSearch & " OR [Tracker Assigned 2] Like " & sSearch & " OR [Tracker Assigned 3] Like " & sSearch
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If

If Me.Recordset.RecordCount = 0 Then
If MsgBox("No Record Found") = vbNo Then
Me.FilterOn = False
txtSearch = Null
Exit Sub
End If
Me.FilterOn = False
Exit Sub
End If
With Me.txtSearch
.SetFocus
.SelStart = Len(Me.txtSearch)
End With
End Sub

[June7]

Each record has 3 tracker fields? This is not normalized structure. Which do you want to consider for the returned criteria?

Please post lengthy code within CODE tags to retain indentation and readability.

[donnie.atchison]

I want to find the Tracker Assigned that that do not have the Tracker returned checked. I need to be able to search all Tracker assigned fields for the tracker number.

[donnie.atchison]

Private Sub txtSearch_AfterUpdate() Dim strFilter As String
Dim sSearch As String

If Me.txtSearch <> "" Then
sSearch = "'*" & Replace(Me.txtSearch, "'", "''") & "*'"
strFilter = "[Tracker Assigned 1] Like " & sSearch & " OR [Tracker Assigned 2] Like " & sSearch & " OR [Tracker Assigned 3] Like " & sSearch
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If

If Me.Recordset.RecordCount = 0 Then
If MsgBox("No Record Found") = vbNo Then
Me.FilterOn = False
txtSearch = Null
Exit Sub
End If
Me.FilterOn = False
Exit Sub
End If
With Me.txtSearch
.SetFocus
.SelStart = Len(Me.txtSearch)
End With
End Sub

[June7]

Copying your code in another post was not useful.

Took another look at form. That is one record with 30 tracker fields? I think you have been advised to normalize this data structure. Should be another table that is dependent to the shipment. Each record will be one tracker assigned to shipment. I still think the Lending Library template could be adapted for your situation.

[donnie.atchison]

I have looked at the Lending Library and I do not think it will work for what I need to accomplish. In my situation we are tracking trucks (Shipment Number (Primary Key)) that have up to 30 pallets with each pallet containing a Global Positioning System Tracker (Tracker 1 to 30). Each tracker needs to correspond with the Shipment number. The GPS tracker 1 through 30 could be any of the 300+ GPS trackers we have on hand. Each GPS trackers name is a four digit number and any of the 300 trackers could be placed on Pallet 1(Tracker 1) or pallet 2 (tracker 2) and so on. When a GPS device (Tracker) is returned I need to be able to log it as returned in the data base. I am trying to have the report search by a drop down listing all tracker numbers and search for the tracker number being returned that has been checked out. Do you have any suggestions for me.

And thanks for your help.

[Minty]

June is saying that your table design is not ideal for the data you are trying to capture and report on.

Without knowing all the details, I would have said you need a ShipmentTable, GPSTracker table , a Truck table , a pallet table , and a junction table(s) recording the ShipmentID, TruckID, PalletID and Tracker ID's with an issued date and a return date.

This would mean you could easily search for all those records with a null return date.
If you can post up a picture of your table designs / relationships I'm sure we can point you in the right direction.

[June7]

Shipment and tracker would be synonymous with person and book. Create record in junction table for each tracker assigned to shipment. Same record is retrieved to enter return date, just like book checked out then back in. This provides history.