Autopopulating subform with correct data

[sstasiak]

I've got a form(OncRegMain) that has a subform(WeightDate). The subform accepts a [WEIGHT] and [CURRENTDATE] fields into a table tblWeightDate that has an autonumber primary key [WtEntryID].

When I open the form OncRegMain to view patient info, the subform populates with the first [WtEntryID] that is associated with the patient in the main form, which may or may not be the most current one. I need it to populate with the most current date and weight record in tblWeightDate for the selected patient. Essentially what needs to happen is that the weight/date record that needs to be displayed will be the largest [WtEntryID] associated with each record.

Here's a summary of forms, tables, and fields associated with this:

form OncRegMain adds records to tblOncReg which has primary key[MEDRECNO]

form OncRegMain has subform WeightUpdate that adds records to tblWeightDate which has primary key[WtEntryID]. [MEDRECNO] has a 1 to many relationship with [ID] field in tblWeightDate.

Thanks in advance

 

[DrSnuggles]

Setup up the recordsource in subform to sortby WtEntryID Desc??

 

[neileg]

Base the subform on a query that uses the MEDRECNO as a parameter. Set this query as an aggregate query (click on the sigma key). Group By MEDRECNO and choose MAX on WtEntryID.