How do I us VBA for a form to find ra field in a record using a combo box.

[donnie.atchison]

I have a Transportation tracking data base. The database is used for transportation GPS tracking. Each record is a shipment, Each shipment has multiple GPS trackers on the shipment. When the GPS tracker gets to its destination the tracker is returned to its start point. The tracker is then marked at returned in the database. What I am trying to do is use a form with a combo box that contains all the tracker numbers to find shipments where the tracker number has not been returned. The table that contains the data is called Master. it has the following fields. Shipment Number (Primary Key), Tracker Assigned 1 (contain the number of the GPS tracker on the shipment), Tracker Returned 1 (Check box that shows a tracker as being returned when it is checked), Tracker Assigned 2, Tracker Returned 2. Tracker Assigned and Tracker Returned field go up to 30. Thus we can assigned up to 30 GPS trackers per shipment.



How can I get the form to search all 30 Tracker Assigned and Tracker returned fields and display the record of the selected tracker number in the Tracker List Combo box that has not been returned. There should only be one record for the tracker number that has not been returned in the database since we can only use the tracker once per shipment and cannot add it to another shipment until it has been marked as returned in the database.

Here is a snap of the form in design view. I hope you can help. Unfortunately I am new to Access and have not had any training, but I am learning, thanks to those on this forum.

[June7]

You have 30 fields that hold same kind of data and want to search all of them for the same info? This is not a normalized data structure and therefore your task will be harder to accomplish. Options:

1. VBA code that cycles through all 50 fields and compares data

2. dynamic parameterized query with same criteria under each of the 30 fields with OR operator

3. a UNION query that rearranges data into normalized structure and use that query to apply filter for the desired record


Suggest a DateReturned date/time field would be more informative than just a yes/no field.

This db reminds of a library checking books out/in. Each shipment and assigned trackers would equate to a person checking out books. You might look at the MS LendingLibrary template database.