Refreshing Main Form Field

Hello Everyone,

Access 2000

I have a main form with a continuous subform. On the main form I have a
text field called [Status]. It gets populated based on what the user
selects in a field [CommStatus] on the sub form (code in the AfterUpdate
Event). Here's the issue. If the user deletes a subform record. The
[Status] field on the main form shows null even if there is a value in the
preceding subform record. What I'd like it to do is immediately refresh to
reflect that value in the preceding record. It will show that value only if
I close the form and open it back up.

How can I do this?

Thanks for your help

William

re: Refreshing Main Form Field

If the event is happening on the main form then try using Me.Refresh
otherwise Forms![frmName].Refresh

Mike Storr
www.veraccess.com


"William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
news:40193cad$1@news-1.oit.umass.edu...[color=blue]
> Hello Everyone,
>
> Access 2000
>
> I have a main form with a continuous subform. On the main form I have a
> text field called [Status]. It gets populated based on what the user
> selects in a field [CommStatus] on the sub form (code in the AfterUpdate
> Event). Here's the issue. If the user deletes a subform record. The
> [Status] field on the main form shows null even if there is a value in the
> preceding subform record. What I'd like it to do is immediately refresh[/color]
to[color=blue]
> reflect that value in the preceding record. It will show that value only[/color]
if[color=blue]
> I close the form and open it back up.
>
> How can I do this?
>
> Thanks for your help
>
> William
>
>[/color]

re: Refreshing Main Form Field

Thanks Mike, but no luck.

The event is happening on the subform so I put the code both in the
AfterUpdate event of the subform and in the code of the delete button on the
subform--neither of which worked. Does it make any difference that the
field on the main form is not an unbound text box, but rather bound to a
field in the table? Would another option be to put in some code to save the
main form record after I delete a subform record? Is this what happens when
I exit and open up the form?

William


"Mike Storr" <nobody@somewhere.con> wrote in message
news:NjcSb.39901$mf4.1263687@news20.bellglobal.com ...[color=blue]
> If the event is happening on the main form then try using Me.Refresh
> otherwise Forms![frmName].Refresh
>
> Mike Storr
> www.veraccess.com
>
>
> "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
> news:40193cad$1@news-1.oit.umass.edu...[color=green]
> > Hello Everyone,
> >
> > Access 2000
> >
> > I have a main form with a continuous subform. On the main form I have a
> > text field called [Status]. It gets populated based on what the user
> > selects in a field [CommStatus] on the sub form (code in the AfterUpdate
> > Event). Here's the issue. If the user deletes a subform record. The
> > [Status] field on the main form shows null even if there is a value in[/color][/color]
the[color=blue][color=green]
> > preceding subform record. What I'd like it to do is immediately refresh[/color]
> to[color=green]
> > reflect that value in the preceding record. It will show that value[/color][/color]
only[color=blue]
> if[color=green]
> > I close the form and open it back up.
> >
> > How can I do this?
> >
> > Thanks for your help
> >
> > William
> >
> >[/color]
>
>[/color]

re: Refreshing Main Form Field

Which example did you use?
If it is being done from a subform, then Me.Refresh won't work. Try
Me.Parent.Refresh instead.
Using the Refresh method will only work with controls that are bound to a
recordsource of some type.
If the control in question has it's own recordsource and is not bound to a
field in the table/query that the form uses, then you will have to use
control.Requery instead.


"William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
news:40195a34$1@news-1.oit.umass.edu...[color=blue]
> Thanks Mike, but no luck.
>
> The event is happening on the subform so I put the code both in the
> AfterUpdate event of the subform and in the code of the delete button on[/color]
the[color=blue]
> subform--neither of which worked. Does it make any difference that the
> field on the main form is not an unbound text box, but rather bound to a
> field in the table? Would another option be to put in some code to save[/color]
the[color=blue]
> main form record after I delete a subform record? Is this what happens[/color]
when[color=blue]
> I exit and open up the form?
>
> William
>
>
> "Mike Storr" <nobody@somewhere.con> wrote in message
> news:NjcSb.39901$mf4.1263687@news20.bellglobal.com ...[color=green]
> > If the event is happening on the main form then try using Me.Refresh
> > otherwise Forms![frmName].Refresh
> >
> > Mike Storr
> > www.veraccess.com
> >
> >
> > "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
> > news:40193cad$1@news-1.oit.umass.edu...[color=darkred]
> > > Hello Everyone,
> > >
> > > Access 2000
> > >
> > > I have a main form with a continuous subform. On the main form I have[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > text field called [Status]. It gets populated based on what the user
> > > selects in a field [CommStatus] on the sub form (code in the[/color][/color][/color]
AfterUpdate[color=blue][color=green][color=darkred]
> > > Event). Here's the issue. If the user deletes a subform record. The
> > > [Status] field on the main form shows null even if there is a value in[/color][/color]
> the[color=green][color=darkred]
> > > preceding subform record. What I'd like it to do is immediately[/color][/color][/color]
refresh[color=blue][color=green]
> > to[color=darkred]
> > > reflect that value in the preceding record. It will show that value[/color][/color]
> only[color=green]
> > if[color=darkred]
> > > I close the form and open it back up.
> > >
> > > How can I do this?
> > >
> > > Thanks for your help
> > >
> > > William
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Refreshing Main Form Field

I used Me.Parent.Refresh in the subform. The main form control is bound to
one of the fields in the table the form uses.

"Mike Storr" <nobody@somewhere.con> wrote in message
news:5idSb.6182$qU3.479067@news20.bellglobal.com.. .[color=blue]
> Which example did you use?
> If it is being done from a subform, then Me.Refresh won't work. Try
> Me.Parent.Refresh instead.
> Using the Refresh method will only work with controls that are bound to a
> recordsource of some type.
> If the control in question has it's own recordsource and is not bound to a
> field in the table/query that the form uses, then you will have to use
> control.Requery instead.
>
>
> "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
> news:40195a34$1@news-1.oit.umass.edu...[color=green]
> > Thanks Mike, but no luck.
> >
> > The event is happening on the subform so I put the code both in the
> > AfterUpdate event of the subform and in the code of the delete button on[/color]
> the[color=green]
> > subform--neither of which worked. Does it make any difference that the
> > field on the main form is not an unbound text box, but rather bound to a
> > field in the table? Would another option be to put in some code to save[/color]
> the[color=green]
> > main form record after I delete a subform record? Is this what happens[/color]
> when[color=green]
> > I exit and open up the form?
> >
> > William
> >
> >
> > "Mike Storr" <nobody@somewhere.con> wrote in message
> > news:NjcSb.39901$mf4.1263687@news20.bellglobal.com ...[color=darkred]
> > > If the event is happening on the main form then try using Me.Refresh
> > > otherwise Forms![frmName].Refresh
> > >
> > > Mike Storr
> > > www.veraccess.com
> > >
> > >
> > > "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
> > > news:40193cad$1@news-1.oit.umass.edu...
> > > > Hello Everyone,
> > > >
> > > > Access 2000
> > > >
> > > > I have a main form with a continuous subform. On the main form I[/color][/color][/color]
have[color=blue]
> a[color=green][color=darkred]
> > > > text field called [Status]. It gets populated based on what the[/color][/color][/color]
user[color=blue][color=green][color=darkred]
> > > > selects in a field [CommStatus] on the sub form (code in the[/color][/color]
> AfterUpdate[color=green][color=darkred]
> > > > Event). Here's the issue. If the user deletes a subform record.[/color][/color][/color]
The[color=blue][color=green][color=darkred]
> > > > [Status] field on the main form shows null even if there is a value[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > > preceding subform record. What I'd like it to do is immediately[/color][/color]
> refresh[color=green][color=darkred]
> > > to
> > > > reflect that value in the preceding record. It will show that value[/color]
> > only[color=darkred]
> > > if
> > > > I close the form and open it back up.
> > > >
> > > > How can I do this?
> > > >
> > > > Thanks for your help
> > > >
> > > > William
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Refreshing Main Form Field

On Thu, 29 Jan 2004 15:24:49 -0500, William Wisnieski wrote:
[color=blue]
> I used Me.Parent.Refresh in the subform. The main form control is bound to
> one of the fields in the table the form uses.
>
> "Mike Storr" <nobody@somewhere.con> wrote in message
> news:5idSb.6182$qU3.479067@news20.bellglobal.com.. .[/color]


I re-read your original posting and I beleive that I misunderstood it at
first. However, I'm having some trouble envisioning the way you have things
setup, so some more info about how the forms are linked to each other, and
perhaps a brief example of the table/query you are using to do this would
help?

--
Mike Storr
veraccess.com

re: Refreshing Main Form Field

William Wisnieski wrote:
[color=blue]
> Hello Everyone,
>
> Access 2000
>
> I have a main form with a continuous subform. On the main form I have a
> text field called [Status]. It gets populated based on what the user
> selects in a field [CommStatus] on the sub form (code in the AfterUpdate
> Event). Here's the issue. If the user deletes a subform record. The
> [Status] field on the main form shows null even if there is a value in the
> preceding subform record. What I'd like it to do is immediately refresh to
> reflect that value in the preceding record. It will show that value only if
> I close the form and open it back up.
>
> How can I do this?
>
> Thanks for your help
>
> William[/color]

Check out the GoToRecord, as in, Docmd.GotoRecord acPrevious. You would not
want to do that if you are on the first record so you'd need to check...see the
AbsolutePosition property. You would want to grab the value of the status in
the previous record in one off the OnDelete events.

re: Refreshing Main Form Field

Thanks Mike

Here's a more thorough explanation. I posted it under another thread based
on another approach someone told me.

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.

I'm playing around with using a DMax statement in an unbound text box on the
subform to return the highest CommID then somehow refer to it in the
OnCurrent event of the main form...but I'm still open to all suggestions!

William



"Mike Storr" <storrboy@sympatico.ca> wrote in message
news:w8hdh03sp3k3.esn962vq4yw.dlg@40tude.net...[color=blue]
> On Thu, 29 Jan 2004 15:24:49 -0500, William Wisnieski wrote:
>[color=green]
> > I used Me.Parent.Refresh in the subform. The main form control is bound[/color][/color]
to[color=blue][color=green]
> > one of the fields in the table the form uses.
> >
> > "Mike Storr" <nobody@somewhere.con> wrote in message
> > news:5idSb.6182$qU3.479067@news20.bellglobal.com.. .[/color]
>
>
> I re-read your original posting and I beleive that I misunderstood it at
> first. However, I'm having some trouble envisioning the way you have[/color]
things[color=blue]
> setup, so some more info about how the forms are linked to each other, and
> perhaps a brief example of the table/query you are using to do this would
> help?
>
> --
> Mike Storr
> veraccess.com[/color]

re: Refreshing Main Form Field

If the [Status] field is not editable, have you tried giving it a
ControlSource of Forms.MainForm.SubformControlName.Form!CommStatus.
If it is editable, then is the mainform and subform linked using
master/child linking or some other method?

Mike Storr
www.veraccess.com


"William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
news:401a9a15$1@news-1.oit.umass.edu...[color=blue]
> Thanks Mike
>
> Here's a more thorough explanation. I posted it under another thread[/color]
based[color=blue]
> on another approach someone told me.
>
> 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.
>
> I'm playing around with using a DMax statement in an unbound text box on[/color]
the[color=blue]
> subform to return the highest CommID then somehow refer to it in the
> OnCurrent event of the main form...but I'm still open to all suggestions!
>
> William
>
>
>
> "Mike Storr" <storrboy@sympatico.ca> wrote in message
> news:w8hdh03sp3k3.esn962vq4yw.dlg@40tude.net...[color=green]
> > On Thu, 29 Jan 2004 15:24:49 -0500, William Wisnieski wrote:
> >[color=darkred]
> > > I used Me.Parent.Refresh in the subform. The main form control is[/color][/color][/color]
bound[color=blue]
> to[color=green][color=darkred]
> > > one of the fields in the table the form uses.
> > >
> > > "Mike Storr" <nobody@somewhere.con> wrote in message
> > > news:5idSb.6182$qU3.479067@news20.bellglobal.com.. .[/color]
> >
> >
> > I re-read your original posting and I beleive that I misunderstood it at
> > first. However, I'm having some trouble envisioning the way you have[/color]
> things[color=green]
> > setup, so some more info about how the forms are linked to each other,[/color][/color]
and[color=blue][color=green]
> > perhaps a brief example of the table/query you are using to do this[/color][/color]
would[color=blue][color=green]
> > help?
> >
> > --
> > Mike Storr
> > veraccess.com[/color]
>
>[/color]

re: Refreshing Main Form Field

Here's the solution that seems to finally be working:

In the OnCurrent Event of the Main Form I have this code:

Private Sub Form_Current()
Dim rs As Recordset
Set rs = Me![sfrmCommunication].Form.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveLast
Me![sfrmCommunication].Form.Bookmark = rs.Bookmark
Set rs = Nothing
Else
rs.AddNew
End If
Me.Status = [Forms]![frmStudentRecord]![sfrmCommunication]!CommStatus
End Sub

So far so good....

William





"Mike Storr" <nobody@somewhere.con> wrote in message
news:95zSb.44690$mf4.1536698@news20.bellglobal.com ...[color=blue]
> If the [Status] field is not editable, have you tried giving it a
> ControlSource of Forms.MainForm.SubformControlName.Form!CommStatus.
> If it is editable, then is the mainform and subform linked using
> master/child linking or some other method?
>
> Mike Storr
> www.veraccess.com
>
>
> "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
> news:401a9a15$1@news-1.oit.umass.edu...[color=green]
> > Thanks Mike
> >
> > Here's a more thorough explanation. I posted it under another thread[/color]
> based[color=green]
> > on another approach someone told me.
> >
> > 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]
> with[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]
> recent[color=green]
> > record on the subform. The subform table has a primary key [CommID] as[/color]
> well[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]
> most[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]
> deleting[color=green]
> > subform records.
> >
> > I've tried code in the OnCurrent event of the main form to get the[/color][/color]
value.[color=blue][color=green]
> > 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]
> back[color=green]
> > it doesn't update. I've tried setting the OnCurrent event of the[/color][/color]
subform[color=blue]
> to[color=green]
> > go to the last record but this seems to freeze the new record area of[/color][/color]
the[color=blue][color=green]
> > subform. I've tried using refresh in the after update event of the[/color]
> subform[color=green]
> > and that didn't work either.
> >
> > I'm playing around with using a DMax statement in an unbound text box on[/color]
> the[color=green]
> > subform to return the highest CommID then somehow refer to it in the
> > OnCurrent event of the main form...but I'm still open to all[/color][/color]
suggestions![color=blue][color=green]
> >
> > William
> >
> >
> >
> > "Mike Storr" <storrboy@sympatico.ca> wrote in message
> > news:w8hdh03sp3k3.esn962vq4yw.dlg@40tude.net...[color=darkred]
> > > On Thu, 29 Jan 2004 15:24:49 -0500, William Wisnieski wrote:
> > >
> > > > I used Me.Parent.Refresh in the subform. The main form control is[/color][/color]
> bound[color=green]
> > to[color=darkred]
> > > > one of the fields in the table the form uses.
> > > >
> > > > "Mike Storr" <nobody@somewhere.con> wrote in message
> > > > news:5idSb.6182$qU3.479067@news20.bellglobal.com.. .
> > >
> > >
> > > I re-read your original posting and I beleive that I misunderstood it[/color][/color][/color]
at[color=blue][color=green][color=darkred]
> > > first. However, I'm having some trouble envisioning the way you have[/color]
> > things[color=darkred]
> > > setup, so some more info about how the forms are linked to each other,[/color][/color]
> and[color=green][color=darkred]
> > > perhaps a brief example of the table/query you are using to do this[/color][/color]
> would[color=green][color=darkred]
> > > help?
> > >
> > > --
> > > Mike Storr
> > > veraccess.com[/color]
> >
> >[/color]
>
>[/color]