Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    No problem with your description, it's just sometimes difficult to grasp the meaning
    To try and alleviate that a bit, the above code you posted is actually VBA, not a Macro. This will help as not many people here develop using macro's.

    Now to your problem, can you show how you run the append query, as that will enable us to get you the ID of the newly added record.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  2. #17
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Yes Minty, it was a Macro, I've converted it to VBA to add the wait procedure.

    Code:
    Private Sub Command103_Click()
     
    DoCmd.OpenQuery "New Invoice"
    TWait = Time
    TWait = DateAdd("s", 3, TWait)
    Do Until TNow >= TWait
    TNow = Time
    DoCmd.OpenForm "New Invoice"
    Loop
    The query "new invoice" is the append query. I guess it’s not good practice to have the form and query the same name. So far, this three second wait is working fine. But no guarantee the server will always work as well as it is right now.

    And the SQL for that query is as follows:

    Code:
    INSERT INTO Invoice_Entries ( On_Issue, Order_ID, TypeOfInvoice, Doc_Code, Import_Code )
    SELECT Order_Tbl.Invoice_Issue, Order_Tbl.Order_ID, "1" AS invoice, "_INV" AS Expr2, "SI" AS Expr1
    FROM Order_Tbl
    WHERE (((Order_Tbl.Order_ID)=[forms]![Order_List]![Order_ID]));

  3. #18
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay so the best way to do what you want is probably as follows (Untested but should work)

    Code:
        Dim lastID As Long
        Dim sSql As String
        
        sSql = "INSERT INTO Invoice_Entries ( On_Issue, Order_ID, TypeOfInvoice, Doc_Code, Import_Code ) "
        sSql = sSql & "SELECT Order_Tbl.Invoice_Issue, Order_Tbl.Order_ID, '1' AS invoice, '_INV' AS Expr2, 'SI' AS Expr1 "
        sSql = sSql & "FROM Order_Tbl "
        sSql = sSql & "WHERE (((Order_Tbl.Order_ID)= " & [Forms]![Order_List]![Order_ID] & "));"
    
        Debug.Print sSql
    
        With CurrentDb
            With .CreateQueryDef("", sSql)
                .Execute dbFailOnError
                .Close
            End With
            lastID = .OpenRecordset("SELECT @@IDENTITY").Fields(0)
        End With
    
        Debug.Print lastID
    
        DoCmd.OpenForm "New Invoice", acNormal, , "[Order_ID] = " & lastID
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #19
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I've put that code in. It does append correctly but does not open the form on the appended record.

    Here is the debug.print showing correct information:
    Code:
    INSERT INTO Invoice_Entries ( On_Issue, Order_ID, TypeOfInvoice, Doc_Code, Import_Code ) SELECT Order_Tbl.Invoice_Issue, Order_Tbl.Order_ID, '1' AS invoice, '_INV' AS Expr2, 'SI' AS Expr1 FROM Order_Tbl WHERE (((Order_Tbl.Order_ID)= 121));
     1157 
    INSERT INTO Invoice_Entries ( On_Issue, Order_ID, TypeOfInvoice, Doc_Code, Import_Code ) SELECT Order_Tbl.Invoice_Issue, Order_Tbl.Order_ID, '1' AS invoice, '_INV' AS Expr2, 'SI' AS Expr1 FROM Order_Tbl WHERE (((Order_Tbl.Order_ID)= 121));
     1146 
    INSERT INTO Invoice_Entries ( On_Issue, Order_ID, TypeOfInvoice, Doc_Code, Import_Code ) SELECT Order_Tbl.Invoice_Issue, Order_Tbl.Order_ID, '1' AS invoice, '_INV' AS Expr2, 'SI' AS Expr1 FROM Order_Tbl WHERE (((Order_Tbl.Order_ID)= 121));
     1147
    Here is the one using the code provided by yourself with "gotolast" disabled.
    Click image for larger version. 

Name:	Screenshot_6.png 
Views:	6 
Size:	15.2 KB 
ID:	38336

    With the code currently in use:
    Click image for larger version. 

Name:	Screenshot_7.png 
Views:	6 
Size:	18.7 KB 
ID:	38337

    I wouldnt know where to begin with this, I shall do some reading.

    Andy.

  5. #20
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is [Order_ID] the correct field name on the New Invoice form ?
    Can you post the whole code currently used to open the form
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #21
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Hi Minty,

    Order_ID is a foreign Key, the appended record ID is Inv_ID.

    I've changed it to this and it works fine. Thanks a lot for your patience and help.

  7. #22
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Glad you solved it - good luck with the rest of your project.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 05-18-2017, 10:15 AM
  2. Replies: 11
    Last Post: 11-30-2015, 01:06 PM
  3. Access wont follow the command GoToRecord,,acNewRec
    By Stephenson in forum Programming
    Replies: 16
    Last Post: 11-06-2015, 04:14 PM
  4. Replies: 3
    Last Post: 01-02-2015, 02:06 PM
  5. Replies: 4
    Last Post: 12-12-2014, 08:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums