Form & Query for 1 Record

[JamesGang]

Background:

The database is to track items, and when moving the items to ensure their physical condition is acceptable. These items may move several times.

Attempting to do:

When requested to move an item, I want to pull up the record from the main table, and bring up a linked record on a form to track the condition of the item. Over time, there could be multiple records on the condition of the item. I have an "AutoNumber" field as well as a date.

What I am having trouble with is that I only want to pull up the last "condition" record of that item, and copy it to use as a base for the next "condition" record to be completed.

I can't seem to get the query to give me only one result. I've tried the orderby and MaxRecords but that is not working. All I need to do is have Access recognize the largest "AutoNumber" or latest date and pull that record, then I would just duplicate.

Any thoughts?

Thanks for anyone's help.

 

[mark curtis]

One way to do this would be to have a 2 date fields in the table that holds the condition started and condition closed. Assuming that the item can only be in one condition at anyone time. What you can do is return a query that only shows items with no condition start and condition closed OR items with a condition start date but null condition closed.