Query to Return latest record

[Helpee]

Hi All,

I'm a novice to this (last used access 5+ years ago [97]) so please bear with me.
I'm trying to create in access2010
(1) a query that returns the latest record (newest) in a table called 'Invoices' and then
(2) places this value in a form called 'FrmInputInvoices' as the default value when the form opens. Newest record is by Autonumber and the table defaults this to top of table as views newest down to oldest.
Re (1) Query is called 'QInvoices'; the values I want to return in my query is ID (my autonumber) and Invoice_No . Must be a simple answer to put in the criteria, but I can't find this.
Re(2) What code do I use in my Form field named 'Invoice_No when the curser defaults there on opening?

Here's hoping someone can help me get back into this.

Helpee

 

[jdraw]

It think you want logic along this line
get the Max(InvoiceDate) where InvoiceId = YourInvoiceID

 

[Helpee]

Many thanks for this & taking the time to reply. Couple of questions, if I may: -

I'm assuming this relates to the query element of my question. I wasn't using a date presently in the table, just AutoNumber as Unique identifier (& highest number to identify the newest invoice). Is using date & time a better option?

 

[jdraw]

Autonumbers are only guaranteed to be unique --they may be negative or positive and should not necessarily be considered sequential. Read this

If you want the latest record from a table, I recommend you use a date of some sort that makes sense to you. There is no inherent order in a relational table. To retrieve records in some sequence, use a query with an Order By (your field).

You should make sure your tables and relationships are designed to meet your requirements before getting too involved with the details of Access (or any DBMS software).

 

[Helpee]

Thanks for the advise.

My query is only to show the absolute last record saved, using this to populate a field in a form. If I use a date this will it not return all records for that date (unless I use date & time) and may relate to a number of different invoices for that date. I was using the Autonumbering (incremental numbers) as an option to recover the last record.