Select Max to Get Value for Field

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

re: Select Max to Get Value for Field

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" <wwisnieski@admissions.umass.edu> wrote:[color=blue]
>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
>
>
>
>
>[/color]

re: Select Max to Get Value for Field

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" <wwisnieski@admissions.umass.edu> wrote in message
news:40198114$1@news-1.oit.umass.edu...[color=blue]
> Hello Again,
>
> I'm really stuck on this one.....so I'm going to try a different approach[/color]
to[color=blue]
> 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[/color]
with[color=blue]
> 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[/color]
recent[color=blue]
> record on the subform. The subform table has a primary key [CommID] as[/color]
well[color=blue]
> 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[/color]
most[color=blue]
> recent date and time. The users move frequently from next record to
> previous record of the recordset while on the main form, adding and[/color]
deleting[color=blue]
> 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[/color]
back[color=blue]
> it doesn't update. I've tried setting the OnCurrent event of the subform[/color]
to[color=blue]
> 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[/color]
subform[color=blue]
> and that didn't work either.
>
> Someone suggested using "SELECT MAX ..." sql string for a recordset to[/color]
get[color=blue]
> 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,[/color]
except[color=blue]
> I have no idea how to begin. Could someone please show me an example that
> might work?
>
> Thanks for your help.
>
> William
>
>
>
>
>[/color]

re: Select Max to Get Value for Field

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" <fraza@clear.net.nz> wrote in message
news:40199f69$1@clear.net.nz...[color=blue]
>
> Perhaps try max([CommID]) to return the max value of the recordset the[/color]
form is using.[color=blue]
> 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[/color]
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.)[color=blue]
>
> Hope that helps!
>
> Regards,
>
> Fraser.
>
> "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote:[color=green]
> >Hello Again,
> >
> >I'm really stuck on this one.....so I'm going to try a different approach[/color][/color]
to[color=blue][color=green]
> >this problem.
> >
> >I have a query by form that returns a record set in a datasheet. The[/color][/color]
user[color=blue][color=green]
> >double clicks on a row and a main form (pop up) opens bound to a table[/color][/color]
with[color=blue][color=green]
> >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[/color][/color]
recent[color=blue][color=green]
> >record on the subform. The subform table has a primary key [CommID] as[/color][/color]
well[color=blue][color=green]
> >as a date/time stamp field. Because a user can delete subform records[/color][/color]
and[color=blue][color=green]
> >add to them, I need that main form field [Status] to always have the[/color][/color]
value[color=blue][color=green]
> >from the most recent subform record, which means the highest CommID or[/color][/color]
most[color=blue][color=green]
> >recent date and time. The users move frequently from next record to
> >previous record of the recordset while on the main form, adding and[/color][/color]
deleting[color=blue][color=green]
> >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[/color][/color]
to[color=blue][color=green]
> >go to the last record . But if the user clicks next record, then goes[/color][/color]
back[color=blue][color=green]
> >it doesn't update. I've tried setting the OnCurrent event of the subform[/color][/color]
to[color=blue][color=green]
> >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[/color][/color]
subform[color=blue][color=green]
> >and that didn't work either.
> >
> >Someone suggested using "SELECT MAX ..." sql string for a recordset to[/color][/color]
get[color=blue][color=green]
> >the value of [CommStatus] from the most recent subform record and place[/color][/color]
it[color=blue][color=green]
> >in the [Status] field of the main form. It sounds like a great idea,[/color][/color]
except[color=blue][color=green]
> >I have no idea how to begin. Could someone please show me an example[/color][/color]
that[color=blue][color=green]
> >might work?
> >
> >Thanks for your help.
> >
> >William
> >
> >
> >
> >
> >[/color]
>[/color]