DLast Method

[Fi]

Hi..I'm trying to show the last figure entered in a cell, on a continuous subform. Using DLast I can get the expected figure to appear but it is the last one entered over all records whereas I would like to see the last one for that particular record, the DB has a main form Clients and a subform Deals, the client can have many deals and I would like to show, in the form footer of the subform, the last interest rate used for that particular clients deals....make can anyone help
thanks in advance

 

[Jack Cowley]

Something like this:

X =DLast("[FieldName]", "TableName", "[ClientID] = " & Me![ClientID])

 

[Pat Hartman]

Last() refers to the final record of a recordset not to any permanent physical or temporal sequence. Each time that recordset is produced, the rows could potentially be in a different order UNLESS you specifically sort them on a UNIQUE identifier. Therefore, in order for the DLast() function to return the correct record to you, it needs to reference a query rather than a table and that query needs to include an order by clause that refers to a unique identifier such as an autonumber primary key.

 

[Fi]

thanks for the replies Pat and Jack, I have tried your suggestion Jack in the control source for the cell but it gives me an error message, have I put it in the correct place, should it be an event ? I'm a bit lost about the X, sorry still a bit of a novice.
For the query Pat, should the subforms control source be based on this ? or just the cell ? any more help would be greatly appreciated...thanks

 

[Pat Hartman]

I was expanding on Jack's answer. Rather than using the tablename in the DLookUp(), use a query that has an OrderBy clause. The X in Jack's answer was simply a reference to some form control. Add a control to the form and in its controlsource place the DLookup():

=DLast("[FieldName]", "QueryName", "[ClientID] = " & Me![ClientID])

 

[Fi]

Thanks for your reply Pat, I am trying to get this to work but can't seem to get it, I have created a query based on the table that the subform is based on, in properties of the query should I use 'Interest Rate' or 'ClientID' for Order By ? should I base the subform on the query also, or should it still be the table, the control on the form where I want the calculation to appear is in the form footer of the subform, does this make a differance, I have tried help but it was no clearer, if there is anything you can suggest I'd be most grateful, thanks

 

[Pat Hartman]

I suspect that ClientId does not uniquely identify a row in the table that you are querying. If that is the case, the DLast() function will NEVER work reliabley for you. You need a UNIQUE identifier to ensure that you always get the record you are looking for. If the table has a unique multi-field key, you can sort by all of the fields to accomplish what you need. There is no need to change the form's recordsource. I am only talking here about the query used as the domain for the DLookup().