Search for previous record based on id

[cbialzik]

I'm still searching the form for something that will help me but so far I haven't quite found what I need.
I have a database that tracks patient visits. On their first visit, their patient history is entered. On their next visit, there are some fields that need to show what was entered on the last visit and the rest of the fields are blank. I can't figure out how to make it search for the last visit of that patient and show those fields. I can't attach the database zip file because it is too large but can email to anyone that is willing to help! Thanks in advance!

 

[pbaldy]

One way is a subquery. Basically (off the top of my head):

SELECT Blah
FROM TableName
WHERE PatientID = Forms!FormName.PatientID AND
DateField = (SELECT Max(DateField) FROM TableName AS Alias WHERE Alias.DateField < Date())