Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jan 2003
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    refering to a field on the last record (2000)

    Hi,
    I am working on a single style form, there is a field which i would like to have the value of a feild in another tabular style form. curently the source is:
    =[Forms]![CustomerActions]![Payments].[Form]![PaymentAmount]
    That does alright to give me the value for the first record. Id actually like it to give me the value for the last form.
    How do I refer to that?
    Thanks in advance

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: refering to a field on the last record (2000)

    You can only refer to the *current* (the currently selected) record in a form, either from inside the form or from outside. In other words, unlike Excel where you can refer to a specific row and column, in Access you are limited to the active record and referring to a field/control on a form always refers to the current record. In fact, if you want to look up a particular value, you don't need the continuous form open at all, you just need to query the table with the criteria to return that value. Also, remember that there is no "last" record in an Access table in any real sense. Records are stored in the order they are entered but they are handled based on the indexes that are applied, including the primary key, if any. So if a form is sorted one way, the last record will be different from what it might be with a different sort. Or did you mean the *previous* record as opposed to the last one in the table?

    If you explain what you are trying to accomplish rather than just what you want to do, I'm sure someone will be able to help you. Be as specific as you can because otherwise we're just guessing.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2003
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: refering to a field on the last record (2000)

    Thanks. Ill tell you the specifics: I have a form with customer records on it. That form has another form on it which is essentially a table of payments which that customer has made (linked by a customer id). Also customer can have several Orders. At the moment i am working on being able to print a customer copy of an order. This will also have their account balance and the most recent payment they made written on it, so that it can act as an invoice and reciept. So i guess it just has to find the AMOUNT value out of the payments table, on the record with a matching CUSTOMERID and the highest PAYMENTID (to get the last payment they made).

    Im just not sure how you would call specific data like that out of a table.

    Adrian

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: refering to a field on the last record (2000)

    What I would do is to put code in the Format section of the Detail section of the report to lookup the CustomerID that has the highest PaymentID as one query. Build another query joining the query just built with the Payments table on CustomerID and the highest PaymentID to get the Amount. Then just populate a text box in the report.

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

    Re: refering to a field on the last record (2000)

    This computation requires a query built on a query, or nested queries: you must first find the maximum PaymentID for the specified CustomerID, and then retrieve the Amount for that PaymentID. The SQL for this is

    SELECT Amount FROM Payments WHERE PaymentID=(SELECT Max(PaymentID) FROM Payments WHERE CustomerID=[Forms]![CustomerActions]![CustomerID])

    Save this query as (for example) LastPayment. Now, you can put a text box on the form with control source =DLookup("Amount","LastPayment")

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: refering to a field on the last record (2000)

    As long as PaymentID in the Payments table is an AutoNumber field Hans suggestion is spot on as usual.
    However if it is not then you will need to do it the another way.

  7. #7
    Lounger
    Join Date
    Jan 2003
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: refering to a field on the last record (2000)

    Fantastic... ill try that... i think i can follow what you are saying.
    Im not sure i know where to put that query or the syntax of writing it but ill try a few things.
    Thanks Hans and Pat and Charlotte

  8. #8
    Lounger
    Join Date
    Jan 2003
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: refering to a field on the last record (2000)

    Ive run into a bit of a problem in that the only experience i have had with querys is in the "create new query query in design view" sense of the word.
    Where should i put the SQL that you mentioned?
    Adrian

    Ive since found out how to do SQL view for the querys i am familiar with. (I didnt know that that was what the underlying code looked like)

    cool, all fixed now!
    Im try that now

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

    Re: refering to a field on the last record (2000)

    OK, so you found SQL view.

    If you want to create a new query entirely in SQL, start creating a new query in design view, but don't cancel the Add Table dialog. You will see an empty design grid, and the "view" button on the toolbar will now read SQL, since you can't switch to datasheet view now. In SQL view, you can type or paste an SQL string. You can then switch back to design view (unless you have made a mistake, or have created a query of a type that can't be displayed in design view, such as a union query.)

    SQL (Standard Query Language) is the "programming language" of queries. Dialects of it are used by Access, SQL Server, Oracle, ... Although you can create many queries in design view, it is useful to have at least a basic understanding of it. There is information about SQL in the Access online help, but it is not easy to find (Access 97 was better at this), but if you do a Google search for 'SQL tutorial' or something like that, you'll find lots of sites offering help.

  10. #10
    Lounger
    Join Date
    Jan 2003
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: refering to a field on the last record (2000)

    Ok thanks. I have the form working now.
    I will follow that up with finding out HOW it is working as per your suggestion.
    Thanks
    Adrian

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: refering to a field on the last record (2000)

    Adrian

    I found this site to be useful for Access SQL, University of Bristol Computing Service

    HTH

    Peter

Posting Permissions

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