Select Max to Get Value for Field

[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

[4Fraza]

Perhaps try max([CommID]) to return the max value of the recordset the form is using.
If you are referring to a table try:
=dmax("[CommID]","tblNAME")

If users are adding or deleting records all the time, try
Me.Requery - This should refresh the recordset, and therefore the
max([CommID]) value.
Note: A requery will move the user back to the start of the recordset.
Perhaps try a .findfirst and .bookmark for the recordset, but make a note of the record you want to move back to. (for this code, try adding a combo box, and select "find a record based on ...." This will write similar code for this purpose.)

Hope that helps!

Regards,

Fraser.

"William Wisnieski" <ww********@admissions.umass.edu> wrote:

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

[Wayne Morgan]

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.

--
Wayne Morgan
MS Access MVP
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:40********@news-1.oit.umass.edu...

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

[William Wisnieski]

Thanks Fraser,

I feel like I'm almost there. I put an unbound text box in my subform
[sfrmCommunication] with the following code:

=DMax("[CommID]","tblCommunication","[StudentID] =
Forms!frmStudentRecord![StudentID]")

This code works fine and returns the highest CommID for that recordset.

Now in the OnCurrent event of my main form [frmStudentRecord] so far I have
the following code to try to get the value of the [CommStatus] field of the
subform:

Me.Status = [Forms]![frmStudentRecord]![sfrmCommunication]!CommStatus

That's where I don't know how to insert the value of my DMax function to
ensure I'm getting the value from the highest CommID record in the subform.

How can I complete this?

Thanks,

William
"4Fraza" <fr***@clear.net.nz> wrote in message
news:40********@clear.net.nz...

Perhaps try max([CommID]) to return the max value of the recordset the form is using. If you are referring to a table try:
=dmax("[CommID]","tblNAME")

If users are adding or deleting records all the time, try
Me.Requery - This should refresh the recordset, and therefore the
max([CommID]) value.
Note: A requery will move the user back to the start of the recordset.
Perhaps try a .findfirst and .bookmark for the recordset, but make a note of the record you want to move back to. (for this code, try adding a combo
box, and select "find a record based on ...." This will write similar code
for this purpose.)
Hope that helps!

Regards,

Fraser.

"William Wisnieski" <ww********@admissions.umass.edu> wrote:

Hello Again,

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

I have a query by form that returns a record set in a datasheet. The userdouble clicks on a row and a main form (pop up) opens bound to a table witha 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 recentrecord on the subform. The subform table has a primary key [CommID] as wellas a date/time stamp field. Because a user can delete subform records andadd to them, I need that main form field [Status] to always have the valuefrom the most recent subform record, which means the highest CommID or mostrecent date and time. The users move frequently from next record to
previous record of the recordset while on the main form, adding and deletingsubform 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 togo to the last record . But if the user clicks next record, then goes backit doesn't update. I've tried setting the OnCurrent event of the subform togo 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 subformand that didn't work either.

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

Thanks for your help.

William