Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append function (Access 2000)

    In the thread 541296 i have been given the advice how to build an append function.
    Public Function Dummy1()
    Dim StrSQL As String
    StrSQL = "INSERT INTO Orders1 SELECT * FROM Orders2 " & _
    "WHERE Orders2.OrderID=(SELECT Max(Orders2.OrderID) " & _
    "FROM Orders2 LEFT JOIN Orders1 ON Orders2.OrderID =
    Orders1.OrderID " & _
    "WHERE Orders1.OrderID Is Null AND Orders2.SubOrder=True)"
    CurrentDb.Execute StrSQL
    End Function
    It appends a record from the table orders2 into the table orders1.My function works perfecly well, but i have forgotten however that i have to append the table [order details]2 into [order details]1 too.This table has 2 keys :
    orderid and productid.The two tables are connected with referential integrity.How can i refer to the same order that has been appended ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append function (Access 2000)

    You will have to determine the maximum OrderID first
    Then append the record in Orders2 with this OrderID to Orders1, and next append the records in Order Details2 with this OrderID to Order Details1.
    For example:

    Public Function Dummy1()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim lngMaxID As Long

    Set dbs = CurrentDb

    ' Get highest OrderID
    strSQL = "SELECT Max(Orders2.OrderID) AS MaxID " & _
    "FROM Orders2 LEFT JOIN Orders1 " & _
    "ON Orders2.OrderID = Orders1.OrderID " & _
    "WHERE Orders1.OrderID Is Null AND Orders2.SubOrder=True"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    lngMaxID = rst!MaxID
    rst.Close

    ' Insert orders
    strSQL = "INSERT INTO Orders1 " & _
    "SELECT * FROM Orders2 " & _
    "WHERE Orders2.OrderID = " & MaxID
    dbs.Execute strSQL

    ' Insert order details
    strSQL = "INSERT INTO [Order Details1] " & _
    "SELECT * FROM [Order Details2] " & _
    "WHERE [Order Details2].OrderID = " & lngMaxID
    dbs.Execute strSQL

    ' Clean up
    Set rst = Nothing
    Set dbs = Nothing
    End Function

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append function (Access 2000)

    Thank you so much for the kind attention.On trial i have met the follooiwng error :
    Variable not defined for MaxID in the folloiwng line :
    "WHERE Orders2.OrderID = " & MaxID
    i tried to put lng before it but then i get invalid use of Null.How can i correct the function

    kind regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append function (Access 2000)

    If you inspect the code, you'll see that I intended to use lngMaxID. If that doesn't work, it probably means that there are no records to append.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append function (Access 2000)

    Thank you Hans ! it is a perfect solution. I tested it and it runs smoothly. it is only when i have nothing to append that i get the message Invalid use of Null. For example when i have already appended and when i try to append a second time. Could i make a warning against such stupid actions, as to try to append a second time ? Anyway, i admire the insight you have into Access
    regards
    Keks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append function (Access 2000)

    Before the line

    lngMaxID = rst!MaxID

    insert this code:

    If IsNull(rst!MaxID) Then
    MsgBox "No records to append.", vbInformation
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub
    End If

Posting Permissions

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