Report with Subform Data....

[dodgyone]

I have a one-to-many relationship and I'm producing a report with the main table. The other table has unlimited entries for that ID but I just want to be able to use the mosat recent record entered in my main report.

So...

Find the most recent record for that ID

If the record = "item1" then
display this info in this field
Else
do something else
End If

Thanks,
Marcus

 

[JoeMiller]

You may be able use a DMax function to find the most recent record. You failed to tell us how you determine what is the most recent record. Is it a date? A time? An ID number? Without this I can't tell you if DMax will work or not in your situation. Look it up in help and maybe you can determine if it will.

HTH Joe Miller
joe.miller@flotech.net

 

[dodgyone]

Thanks for getting back to me... I had thought about using the ID or Date.

The thing that confuses me is...

The main report (data taken from the main table) is going to be displayed. For each unique main record I have to find the most recent record relating to it in the linked table (I'm using ID to link the tables in relationship).

I'n not too sure how to code this... I know the basics but is there any syntax that I should be made aware of to make the job easier?

Cheers,
Marcus

 

[JoeMiller]

What are you trying to return? Just the last time the ID from the Main table was purchased/used/sold most recently? Or are you trying to return the ID from the sub table that was purchased/used/sold most recently?

Here's a sample DMax statement that will return the most recent date (SellDate) from your subtable (SubTable) based on a main ID (MainID) that is contained in a text box (txtMainID) on your report.

=DMax("[SellDate]","SubTable","[MainID]=[txtMainID]&quot

Set that statement in the control source of an unbound text box on your report to get the most recent sell date and place the value of SellDate in the control box.

HTH Joe Miller
joe.miller@flotech.net

 

[zink421]

I need to be able to know with code which record in an access subform is selected. Once I find it I want to delete it?