Refreshing Main Form Field

[William Wisnieski]

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

[Mike Storr]

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" <ww********@admissions.umass.edu> wrote in message
news:40********@news-1.oit.umass.edu...

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

[William Wisnieski]

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" <no****@somewhere.con> wrote in message
news:Nj*********************@news20.bellglobal.com ...

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" <ww********@admissions.umass.edu> wrote in message
news:40********@news-1.oit.umass.edu...

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

[Mike Storr]

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" <ww********@admissions.umass.edu> wrote in message
news:40********@news-1.oit.umass.edu...

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" <no****@somewhere.con> wrote in message
news:Nj*********************@news20.bellglobal.com ...

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" <ww********@admissions.umass.edu> wrote in message
news:40********@news-1.oit.umass.edu...

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

[William Wisnieski]

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" <no****@somewhere.con> wrote in message
news:5i*******************@news20.bellglobal.com.. .

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" <ww********@admissions.umass.edu> wrote in message
news:40********@news-1.oit.umass.edu...

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" <no****@somewhere.con> wrote in message
news:Nj*********************@news20.bellglobal.com ...

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" <ww********@admissions.umass.edu> wrote in message
news:40********@news-1.oit.umass.edu...
> 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
>
>

[Mike Storr]

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 bound to
one of the fields in the table the form uses.

"Mike Storr" <no****@somewhere.con> wrote in message
news:5i*******************@news20.bellglobal.com.. .

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

[Salad]

William Wisnieski wrote:

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

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.

[William Wisnieski]

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" <st******@sympatico.ca> wrote in message
news:w8**************************@40tude.net...

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 bound to one of the fields in the table the form uses.

"Mike Storr" <no****@somewhere.con> wrote in message
news:5i*******************@news20.bellglobal.com.. .

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

[Mike Storr]

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" <ww********@admissions.umass.edu> wrote in message
news:40********@news-1.oit.umass.edu...

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" <st******@sympatico.ca> wrote in message
news:w8**************************@40tude.net...

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

bound

to one of the fields in the table the form uses.

"Mike Storr" <no****@somewhere.con> wrote in message
news:5i*******************@news20.bellglobal.com.. .

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

[William Wisnieski]

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" <no****@somewhere.con> wrote in message
news:95*********************@news20.bellglobal.com ...

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" <ww********@admissions.umass.edu> wrote in message
news:40********@news-1.oit.umass.edu...

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" <st******@sympatico.ca> wrote in message
news:w8**************************@40tude.net...

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

bound

to

> one of the fields in the table the form uses.
>
> "Mike Storr" <no****@somewhere.con> wrote in message
> news:5i*******************@news20.bellglobal.com.. .
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