Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    5

    Unanswered: Table with only the last record

    I need to copy the tables orders and orderdetails containng only the last order.I am copying them to another database using the formula
    DoCmd.CopyObject db.Name, "orders", acTable, "orders"
    DoCmd.CopyObject db.Name, "orderdetails", acTable, "orderdetails"

    I want to use the criteria (SELECT Max([orderid]) FROM orders) in order to send the tables with only the last order,but i do not know how to build the code.Can you help me ?
    I guess that i should build a make table query that contains only the last order.To this end i have tried to build the following function:
    Public Function Alan()
    Dim SQL As String
    SQL = "SELECT * INTO orders1 FROM orders WHERE orders1.orderid = DMax(orderid,orders)"
    CurrentDb.Execute SQL
    End Function

    However i get the error "too few parameters".Where am i wrong and i can i do in that way ?
    Last edited by canett; 02-26-06 at 12:44.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Ther is always more than one way to skin a cat with MS-Access. Here in one of them. It's not as serious as it looks once you get rid of all the comments.

    The supplied routine below will create a copy of the Orders table and the OrderDetails table from your Database to a Destination database of your choice. Only the structure of each table is copied (since you only want to store the last Record saved).

    We retrieve and store the record number of the last record saved in each table (Orders and OrderDetails).

    We then run SQL statements which contains the name of our Destination database and the record information we want to Insert in our newly created tables located in that Destination database. We do this for both the Orders and the OrderDetails table.

    Keep in mind...you will most likely be required to change some field names. Look at the code closely and make the neccessary changes to reflect your needs.

    Code:
       Dim SQLStrg As String
       Dim DBName As String
       Dim StructureOnly As Boolean
       Dim OLastRecord As Long
       Dim ODLastRecord As Long
     
       ' This variable is used in the TransferDatabase method
       ' below. See comments below.
       StructureOnly = True
     
       ' Path and File Name of the Destination Database.
       DBName = "C:\Your Database\Lives Here\YouDatabaseName.mdb"
     
       ' Copy The Table (structure only) from Source Database to
       ' Destination Database. Because we have our StructureOnly
       ' Boolean variable set to True, only the table structure
       ' will be copied. We will do this for both our Orders table
       ' And the OrderDetails table.
       ' Note:  If StructureOnly is set to False, then the table
       '        and all data is copied. In your case, we don't
       '        want this.
       DoCmd.TransferDatabase acExport, "Microsoft Access", DBName, acTable, _
                              "Orders", "Orders", StructureOnly
       DoCmd.TransferDatabase acExport, "Microsoft Access", DBName, acTable, _
                              "OrderDetails", "OrderDetails", StructureOnly
     
       ' The DLast method used below will retrieve the Record ID
       ' number of the last record saved to its specified Table.
       ' This of course depends on whether or not your Record
       ' AutoNumber field is named either OrderID and OrderDetailsID.
       ' You will need to make sure these names are correct. The
       ' OLastRecord variable will hold the record number for the
       ' last record saved in the Orders Table. And the ODLastRecord
       ' variable will hold the record number for the last record
       ' saved in the OrderDetails Table.
       OLastRecord = DLast("[OrderID]", "[Orders]", "[OrderID] > 0")
       ODLastRecord = DLast("[OrderDetailsID]", "[OrderDetails]", _
                            "[OrderDetailsID] > 0")
     
       ' Suppress any Warnings of what we're doing just in case
       ' Access wants to blurb something we already know or,
       ' don't want someone else to know.
       DoCmd.SetWarnings False
     
       ' Be sure to provide the password (if any) in the string below
       ' right after PWD=  (if there is none, then leave as is)
      SQLStrg = "INSERT INTO [;DATABASE=" & DBName & ";PWD=''].Orders SELECT " & _
                 "Orders.* FROM Orders WHERE (((Orders.[OrdersID])=" & _
                 OLastRecord & "));"
       CurrentDb.Execute SQLStrg
     
       ' Be sure to provide the password (if any) in the string below
       ' right after PWD=  (if there is none, then leave as is)
      SQLStrg = "INSERT INTO [;DATABASE=" & DBName & ";PWD=''].OrderDetails SELECT " & _
                 "OrderDetails.* FROM OrderDetails WHERE (((OrderDetails.[OrderDetailsID])=" & _
                 ODLastRecord & "));"
       CurrentDb.Execute SQLStrg
     
       ' Re-Enable Warnings again
       DoCmd.SetWarnings True

    .
    Last edited by CyberLynx; 02-27-06 at 22:20.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    CyberLynx,

    Saw the post tried your code, worked fine. Just one little error at the bottom;
    Code:
    ' Re-Enable Warnings again
       DoCmd.SetWarnings False
    I think you ment to put;

    Code:
    ' Re-Enable Warnings again
       DoCmd.SetWarnings True 
    Regards,



    John A

  4. #4
    Join Date
    Feb 2006
    Posts
    5
    Thank you very much indeed.It worked fine

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Quote Originally Posted by ansentry
    CyberLynx,

    Saw the post tried your code, worked fine. Just one little error at the bottom....
    Good eye ansentry... how true that is...and you're right. It should be:

    ' Re-Enable Warnings again
    DoCmd.SetWarnings True

    See what happens when you use copy/paste

    Changed the comment but not the thingy-ma-bobber that does da work.
    Thanks for spotting that one.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    Changed the comment but not the thingy-ma-bobber that does da work.
    Don't do it again or your access licence will be suspended to 1 week.
    Regards,



    John A

Posting Permissions

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