Find last entry

[nag1_uk]

How can I find the last entry that was made in the database? I have an auto number as one of the fields.

What I am wanting to do eventually is find the last 15 entries, then ouput the data to a graph. Or even better have a input box to say hw many entries are used to create the graph.

Thanks

Mike Cole

 

[Fuga]

Make a query. sort descending on your autonumber field. then put "15" in the box next to the summation sign. In design view, that is.

Fuga.

 

[Kodo]

Select top 15 BLAH from TABLE order by BLAH desc

that will get the last 15 entries. of course, replace blah with your field name(s).

if you want to get the last ID added as soon as you add the record then right after you do your APPEND query, run this

SELECT @@IDENTITY

 

[Jon K]

SELECT @@IDENTITY ?

 

[Kodo]

http://www.sqlteam.com/item.asp?ItemID=319

 

[Jon K]

Can it be used in Access too?

 

[Kodo]

yes it can..

 

[Jon K]

if you want to get the last ID added as soon as you add the record then right after you do your APPEND query, run this

SELECT @@IDENTITY

In the attached database, I created a table tblClient with two fields:
[ID] (autonumber, primary key); [Client] text

and two queries:
Query1:-
INSERT INTO tblClient ( Client )
VALUES ([forms]![frmGetIDAdded]!txtClient);

Query2:-
SELECT @@IDENTITY;


When I opened the form, entered a client name, and clicked on the command button to run Query1 and Query2,

Private Sub Command0_Click()
 
   DoCmd.OpenQuery "Query1"  ' an append query
   DoCmd.OpenQuery "Query2"  ' SELECT @@IDENTITY
   
End Sub

Query1 ran fine but Query2 always returned 0.


Could you tell us how to run SELECT @@IDENTITY in Access?

 

[Kodo]

Private Sub Command0_Click()
 
  
  Dim SQL As String, conn As Connection, LastID As Integer, RS As Recordset
  Set conn = CurrentProject.Connection
  
  SQL = "Query1 " & Me.txtClient
  conn.Execute (SQL)
  
  SQL = "Query2 " ' SELECT @@IDENTITY
  Set RS = conn.Execute(SQL)
  LastID = RS(0)
  Debug.Print LastID
 
   RS.Close
   Set RS = Nothing
   conn.Close
   Set conn = Nothing
   
End Sub

 

[Jon K]

..........
SQL = "Query2 " ' SELECT @@IDENTITY
Set RS = conn.Execute(SQL)
LastID = RS(0)
Debug.Print LastID

Thanks. Worked like a charm! Real cool!

 

[nag1_uk]

Thanks a lot will try later

Mike

 

[Kodo]

what's not clear is that I changed QUERY1. I removed the criteria that it checks the form automatically. Instead I gave it a parameter @txtName for the criteria. Then I had to pass the form var to the query.