By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 2,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Refreshing Main Form Field

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
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



Nov 12 '05 #4

P: n/a
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
>
>



Nov 12 '05 #5

P: n/a
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
Nov 12 '05 #6

P: n/a
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.
Nov 12 '05 #7

P: n/a
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

Nov 12 '05 #8

P: n/a
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


Nov 12 '05 #9

P: n/a
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



Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.