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

    Append question (Access 2000)

    'can i append 2 fields from a table into fields with other names into another table? I want to make a new order, and append into it all the products
    'where branch0 > 0 or items > 0
    'The table orders has field cartons and items..Into the field cartons i want to append the field branch and into the field quantity i want to append the field items.My general idea taken mainly from Hans, looks like this :

    Public Function FncSubSub()
    target Table: order details
    Source Table: products

    Dim dbs As DAO.Database
    Dim rstorders As DAO.Recordset
    Dim rstproducts As DAO.Recordset
    Dim varOldID As Variant
    Dim lngNewID As Long
    Dim strSQL As String
    Dim varCustomer As Variant

    ' Get highest OrderID
    varOldID = DMax("OrderID", "orders", "SubOrder=True", "Audit = True")

    If IsNull(varOldID) Then
    Exit Function
    Else
    Set dbs = CurrentDb
    ' Get the last record from orders
    strSQL = "SELECT SubOrder, CustomerID " & _
    "FROM orders WHERE OrderID=" & varOldID
    Set rstproducts = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    Set rstorders = dbs.OpenRecordset("orders", dbOpenDynaset)
    rstorders!Customerid = rstorders!Customerid
    ' Get the new OrderID
    lngNewID = rstorders!orderid
    varCustomer = 402
    rstorders!Customerid = varCustomer


    ' Save the new record
    rstorders.Update
    rstorders.Close
    rstproducts.Close

    ' Append order details
    strSQL = "INSERT INTO [Order Details] (OrderID, ProductID,UnitPrice,cartons,quantity,) " & _
    "SELECT " & lngNewID & ", ProductID, ,branch,items FROM [products] " & _
    "WHERE ProductID = ?"
    dbs.Execute strSQL, dbFailOnError


    ' Clean up
    Set dbs = Nothing
    End If
    End Function

    Up to now , after the audit , we used to write again each items in the warehouse. We could have saved us a lot of time if i could convert the stock in the warehouse into a new order, which is the target of my question.

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

    Re: Append question (Access 2000)

    The code you post here does not apply to the database you attached.

    It is not clear to me what you want. What is the warehouse?

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

    Re: Append question (Access 2000)

    Thank you, Hans ! My target is to make a new order out of the quantities in the table products. It is true that the code is not contained in the database. Because i am not sure how to write it and it wont work in the form I have written. I know that in order to help me I will have to explain what I want to do. It is easier for me to explain with a concrte example from my attachement.
    The fields branch0 and items0 in the table products denote the availability of the products in the warehouse.
    In my example they are :
    Branch0 items0
    1. First Product 6 17
    2. Second Product 50 24
    3. Thid product 1 24
    4. Fourth product 1 24
    What I want is to convert this availability into a new order.The new order has also a table order details.The table order details should contain the following products
    Cartons Quantity
    1. First Product 6 17
    2. Second Product 50 24
    3. Thid product 1 24
    4. Fourth product 1 24
    So somehow I will have a new order, for client 402 and this order will contain the products that are available, which means that branch0 > 0 and itemso > 0.
    So I presume I must use the INSERT into statement to insert the branch0 into the cartons and Items0 into the quantity of the order details table

    Kind regards

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

    Re: Append question (Access 2000)

    Where does client 402 come from? The Orders table has a CustomerID field that is linked to the CustomerID field in the Customers table. The only CustomerIDs present in this table are 1 and 3. So I don't see how I could create an order for "client 402".

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

    Re: Append question (Access 2000)

    Sorry, this is because i stripped the database. Please forget the name client. Please read customer 1

    kind regards

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

    Re: Append question (Access 2000)

    In the first place, please read the general tips I gave in <post:=552,985>post 552,985</post:>.

    The attached zip file contains a cleaned up version of your database, and the module basCode contains the code you asked for.

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

    Re: Append question (Access 2000)

    Dear Hans !! Thank you for clever assistance! This is EXACTLY what i wanted ! You are my hero !

Posting Permissions

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