Results 1 to 3 of 3

Thread: Last Number

  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Last Number

    SELECT Last(Invoices.InvoiceID) AS LastOfInvoiceID
    FROM Invoices
    ORDER BY Last(Invoices.InvoiceID);


    I have this code that updates into a table. What this is meant to do is select the last invoice entered
    into the invoice table and populate it into another table. This works 95% of the time

    But then, for no reason I can see, it gets stuck and populates the table with the same number for new invoices
    entered. And then, for no reason I can see, clears itself and starts populate the table the last invoice number.
    As it should. If I compact and Repair this also clears the problem.

    We are using terminal services and have users logging on from a variety of geographical locations.

    Any ideas?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Thanks for your reply to this. I never really found out the reason why it was doing this
    however I have found a soultion by getting the number by a different route. So for completeness
    I have put the coded solution below.

    --------------------------------------------------

    Dim DB As Database, RS As Recordset
    Dim sInvID As String

    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("Invoices", dbOpenDynaset)

    RS.MoveLast


    sInvID = RS!InvoiceID



    ' 'Add transaction details to Invoice_Cost_Allocation table for month reconciliation
    DoCmd.RunSQL "INSERT INTO Invoice_Cost_Allocation ( Last_No_Ref, CostID, Accounts_Ref, " _
    & "Accounts_Line_No, Allocated_Value, cUser ) " _
    & "SELECT Last(Invoices.InvoiceID) AS LastOfInvoiceID, " & sInvID & ", " _
    & "'" & sAccRef & "','" & sLineNo & "','" & cValue & " ','" & CurrentUser & "'" _
    & "FROM Invoices;"



    RS.Close: DB.Close

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •