Select Max to Get Value for Field

  • Thread starter Thread starter William Wisnieski
  • Start date Start date
W

William Wisnieski

Hello Again,

I'm really stuck on this one.....so I'm going to try a different approach to
this problem.

I have a query by form that returns a record set in a datasheet. The user
double clicks on a row and a main form (pop up) opens bound to a table with
a continuous subform bound to another table.

On the main form is a field called [Status]. It is vital that this is
always populated with the value from a field [CommStatus] in the most recent
record on the subform. The subform table has a primary key [CommID] as well
as a date/time stamp field. Because a user can delete subform records and
add to them, I need that main form field [Status] to always have the value
from the most recent subform record, which means the highest CommID or most
recent date and time. The users move frequently from next record to
previous record of the recordset while on the main form, adding and deleting
subform records.

I've tried code in the OnCurrent event of the main form to get the value.
This works if I close the form and reopen it because the subform is set to
go to the last record . But if the user clicks next record, then goes back
it doesn't update. I've tried setting the OnCurrent event of the subform to
go to the last record but this seems to freeze the new record area of the
subform. I've tried using refresh in the after update event of the subform
and that didn't work either.

Someone suggested using "SELECT MAX ..." sql string for a recordset to get
the value of [CommStatus] from the most recent subform record and place it
in the [Status] field of the main form. It sounds like a great idea, except
I have no idea how to begin. Could someone please show me an example that
might work?

Thanks for your help.

William
 
The problem I see here, is what tells the textbox that there has been a
change that it needs to go fetch? Have you tried the Current event of the
subform? Since this is where the changes are occuring, this event should
fire, where as the main form event won't.

In the subform's Current event try:
Me.Parent.ReCalc

If all else fails, you could use the timer event on the main form to rerun
the calculation once a second or so, but try other methods first.
 
Back
Top