how to update latest record in table - specific fields

[jbone]

hello everyone,

I have an application that I built that allows a user to submit Case information in a form to a table and then they would update them in another form tab. The Case number remains the same and they are allowed to submit new Cases (using the same Case number) given that the most recent one was "closed" - updated with the latest information. Right now, I have an update query that I created that updates all cases with the information in a form but I just want it to update the most recent one because each Case should have different information for each line.

here is how the SQL view looks like from my query. I hope it can shed some light on how it functions:

UPDATE table1 SET table1.[Resolution Date] = [Forms]![Main Window]![UpdateRecords]![Text23], table1.[number of inadequate response] = [Forms]![Main Window]![UpdateRecords]![Text21]
WHERE ((([table1]![ITnumber/Email])=[Forms]![Main Window]![UpdateRecords]![Text15]));

So I want it to look something like:
*my notes


UPDATE table1 SET *the most recent Case field resolution date to* table1.[Resolution Date] = [Forms]![Main Window]![UpdateRecords]![Text23], *the most recent Case field(# of inadequate response)* table1.[number of inadequate response] = [Forms]![Main Window]![UpdateRecords]![Text21]
WHERE ((([table1]![ITnumber/Email])=[Forms]![Main Window]![UpdateRecords]![Text15]));

or VBA code

I appreciate any help with this!

thank you! 

[John_G]

It appears from you description that you have multiple records in table1 with the same case number, but with no way of distinguishing between them. You need another field in the table to indicate the "line number" for each case. Your where clause would then specify case number and line number.

PS - You really should give your form controls meaningful names, not Text15, Text23 etc. It makes reading the code a lot easier.

[jbone]

Hey John G,

thanks for for your reply and suggestion. You are right but I am wondering if I can use the dlast function to update the latest case?

[John_G]

No, I don't think so. DFirst and DLast don't order according to the data you request, they use whatever order Access chooses to use for the table or query. You can use criteria to limit the record set that is searched, nothing but beyond that.