Refreshing Main Form Field

  • Thread starter Thread starter William Wisnieski
  • Start date Start date
W

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
 
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 said:
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 said:
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
Click to expand...
only
if
I close the form and open it back up.

How can I do this?

Thanks for your help

William
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
refresh
to
reflect that value in the preceding record. It will show that value
Click to expand...
only
if
I close the form and open it back up.

How can I do this?

Thanks for your help

William
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
If the event is happening on the main form then try using Me.Refresh
otherwise Forms![frmName].Refresh

Mike Storr
www.veraccess.com


Hello Everyone,

Access 2000

I have a main form with a continuous subform. On the main form I
Click to expand...
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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.

news:[email protected]...
Click to expand...


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?
 
William said:
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
Click to expand...

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.
 
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
 
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 said:
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



bound
Click to expand...
 
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 said:
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 said:
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
Click to expand...
subform
Click to expand...
 
Back
Top