Pull latest occurence of Equipment ID - Confusing myself!

[Dirty_Head]

Background Info:

I have a table called CheckOutLog. Fields are: ID(Autonumber), Equipment ID,

Checked Out By, Date Checked Out, Condition On Checkout, Checkout Authorized By,

Date Checked In, Condition on Checkin, Checkin Authorized By.

I'm using: "001001, 001002, 001003, 002001, 002002, 003001, ..." pattern for my "Equipment IDs".

I have 2 forms for Checking Out and for Checking In equipment. The Check out form creates an entry in the CheckOutLog for all of the ID and Checkout fields - leaving the Check In fields blank.

The Check IN form I'm using a combobox with the "Find a record on my form based on the value I selected in my combo box" - I am currently using the Equipment ID to pull up the equipment that I'm trying to check back in.

The Problem:
Example: Joe Smith checked out the equipment on 5/12/18 and returned it on 5/14/18. Frank Brown checked out the equipment on 5/15/18 and returned it on 5/18/18. Jeffrey Thomas checked out the equipment on 5/20/18 and wants to check it back in. When I search for 001001 the same equipment that both Joe, Frank and Jeffrey already checked out, it's returning ONLY Joe Smith's record. I want to see the LAST person who had it containing NO check-in information. That way I'm not checking in equipment for Joe Smith over and over and over again.



Using the "Find a record on my form..." method for showing the data in the form, is there a way to show only the last/latest records?

[Micron]

Haven't used what you're describing; assuming it will operate just like the Find method, i.e. it will only find the 1st record that satisfies the criteria. You need to include the condition where the check in date is Null for an ID, so you cannot simply based this on a combo selection, unless you include the record id (unique, such as autonumber) in the combo's list, and ensure the combo only returns list items for an ID where there is no check in date.

If your names really are as posted, suggest NO spaces or special characters (save for perhaps underscore) http://access.mvps.org/access/general/gen0012.htm

and NO reserved names such as "Date" One source about how to name things http://allenbrowne.com/AppIssueBadWord.html

[Minty]

Make the forms recordsource only show units that have a null return date.
If it's already returned you don't need to see it on that form presumably ?

[Dirty_Head]

Correct. If it's already "checked in" - I don't need to see it ever again EXCEPT for when running history reports - "how many times has this equipment been checked out? Who checked it out? etc."

[Dirty_Head]

Thanks, Minty! Yeah, I had to click the expression builder next to my record source and put in the criteria/order by and it worked like a charm. Thanks so much!

[Minty]

You're welcome, sometimes the obvious isn't so obvious....