SELECT MAX ROW_NUMBER OVER Partition

[tkepongo]

Hi there,

I am ultimately trying to create an INSERT that will add a record to a junction table, retrieve the MAX row_number partition by Audit_ID and ID (<--identity) and store it into a variable called SortNumber. Then I will use the SortNumber variable to update the record with a sortorder.

Here's my code so far:

Dim rst As DAO.Recordset
Dim SortOrder As Integer
Dim Audit_ID As Variant
Dim Question_ID As Variant
Dim Clause_ID As Variant
Dim RefDoc_ID As Variant

Audit_ID = [Forms]![frmCreateChecklist]![subfrmPlanAudit].Form!.Audit_ID
Question_ID = Me.Question_ID
Clause_ID = Me.Clause_ID
RefDoc_ID = Me.RefDoc_ID

If IsNull(RefDoc_ID) Then
    lsql = "INSERT INTO dbo_jntbl_AuditToQuestion (Audit_ID, Question_ID, Clause_ID) VALUES (" & Audit_ID & ", " & Question_ID & "," & Clause_ID & ")"
    CurrentDb.Execute lsql, dbFailOnError
    Set rst = CurrentDb.OpenRecordset("SELECT MAX (ROW_NUMBER() OVER( Partition BY Audit_ID ORDER BY ID)) as SortOrder FROM jntbl_AuditToQuestion Where Audit_ID = " & Audit_ID & "")
    rownumber= rst!SortOrder

I am having trouble retrieving the MAX Row_Number. Can Access even run the query?

 

[vbaInet]

The search function on this forum is quite good so remember to search before asking. Have a look at the suggestion from ByteMyzer -->

http://www.access-programmers.co.uk/forums/showthread.php?t=159782&highlight=@@identity

You can also use the LastModified method of a recordset object.