We have a form, with a sub form and field on the main form that will be virtual -  we need it to show a field from the LAST record in the sub form, When using the lookup it only shows the 1st record in the sub form, Any help ? -  Thanks Dave

Download Sample

Hi Dave.

If you use Lookup you will get the first hit of the relationship so its correct that you will get only the data from the first row in the subform so if you want to use Lookup for this you need to make a relationship that will hit the last record as the "first" of that relationship.

The way to do this is to add a field to the relationship called "recordnumber" or something like that.

You then create a relationship between the table in the subform and itself so you can use highest of to get the next "record" number.

You then create a field in you main record that use the same relationship as the subform to get the highest record number from the subform and create a new relationsip based on the original restriction and add this field as the limiter of the relationship so you will get the last row.

Ex. first relationship is based on ID=ID, then new relationship will be based on ID=ID and HighestRecNr=MyRecNumber in suform.

so when you have ID =1  in both tables you will have.

Rows 1,2,3,4,5 in your subform 

5 will be the value of the field HighestRecNr and it will match MyRecNumber=5 and you can then pick the data straight out of that record.

As this might have confused you thorougly I've included a sample to show this.