Update (refresh) Subform#1 when a check box is selected on Subform

Hi,

I have a main form with two subforms, one linked and one not.

When I select a checkbox on Subform#1, it affects a value on Subform#2 (and
changes it green).

However, this only updates on Subform#1, after I select a new record on that
subform.

So, my question is, how can I force subform#1 to refresh after I have
clicked my check box on Subform#2?

Any help is always greatly appreciated.

Cheers,
GLT.

this is in the ballpark
in the checkbox's after update event

docmd.requery "subform#2"

the correct reference to the subform will be important for example it may
need to be Me!subform#2 or forms!mainform.subform#2 and im not the best at
that.

Barry

"GLT" wrote:

Hi,

I have a main form with two subforms, one linked and one not.

When I select a checkbox on Subform#1, it affects a value on Subform#2 (and
changes it green).

However, this only updates on Subform#1, after I select a new record on that
subform.

So, my question is, how can I force subform#1 to refresh after I have
clicked my check box on Subform#2?

Any help is always greatly appreciated.

Cheers,
GLT.

Hi Barry,

Thanks for your reply, I have tried this so far:

Me.Parent![serverlist].Requery
Forms.[Get all Details].ServerList.Requery
DoCmd.Requery Forms.Parent.serverlist

None of them work...

"Barry A&P" wrote:

this is in the ballpark
in the checkbox's after update event

docmd.requery "subform#2"

the correct reference to the subform will be important for example it may
need to be Me!subform#2 or forms!mainform.subform#2 and im not the best at
that.

Barry

"GLT" wrote:

Hi,

I have a main form with two subforms, one linked and one not.

When I select a checkbox on Subform#1, it affects a value on Subform#2 (and
changes it green).

However, this only updates on Subform#1, after I select a new record on that
subform.

So, my question is, how can I force subform#1 to refresh after I have
clicked my check box on Subform#2?

Any help is always greatly appreciated.

Cheers,
GLT.

Assuming that both subforms are on the same form

Me.Parent.[NameOfSubformControl].Form.Requery

Note that the SubformControl may have a different name from the subform
inside it.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"GLT" wrote in message
...
Hi Barry,

Thanks for your reply, I have tried this so far:

Me.Parent![serverlist].Requery
Forms.[Get all Details].ServerList.Requery
DoCmd.Requery Forms.Parent.serverlist

None of them work...

"Barry A&P" wrote:

this is in the ballpark
in the checkbox's after update event

docmd.requery "subform#2"

the correct reference to the subform will be important for example it may
need to be Me!subform#2 or forms!mainform.subform#2 and im not the best
at
that.

Barry

"GLT" wrote:

Hi,

I have a main form with two subforms, one linked and one not.

When I select a checkbox on Subform#1, it affects a value on Subform#2
(and
changes it green).

However, this only updates on Subform#1, after I select a new record on
that
subform.

So, my question is, how can I force subform#1 to refresh after I have
clicked my check box on Subform#2?

Any help is always greatly appreciated.

Cheers,
GLT.

Hi Jeanette,

Thanks for your reply,

I used your code and I beleive it works (I can see the subform flicker and
the record selector goes to the 1st record in the subform), however I am
still faced with the original problem, ie. the subform values do not update
until I select another record on that subform. I do not understand why this
is happeneing, I have tried the following, however it seems the only way to
get around this is to setfocus to the subform, move +1 records, then -1
records...


Me.Parent.[ServerList].SetFocus
Me.Parent.[ServerList].Form.Requery

Me.MoveNext
Me.MovePrevious

"Jeanette Cunningham" wrote:

Assuming that both subforms are on the same form

Me.Parent.[NameOfSubformControl].Form.Requery

Note that the SubformControl may have a different name from the subform
inside it.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"GLT" wrote in message
...
Hi Barry,

Thanks for your reply, I have tried this so far:

Me.Parent![serverlist].Requery
Forms.[Get all Details].ServerList.Requery
DoCmd.Requery Forms.Parent.serverlist

None of them work...

"Barry A&P" wrote:

this is in the ballpark
in the checkbox's after update event

docmd.requery "subform#2"

the correct reference to the subform will be important for example it may
need to be Me!subform#2 or forms!mainform.subform#2 and im not the best
at
that.

Barry

"GLT" wrote:

Hi,

I have a main form with two subforms, one linked and one not.

When I select a checkbox on Subform#1, it affects a value on Subform#2
(and
changes it green).

However, this only updates on Subform#1, after I select a new record on
that
subform.

So, my question is, how can I force subform#1 to refresh after I have
clicked my check box on Subform#2?

Any help is always greatly appreciated.

Cheers,
GLT.


.

I assumed that you were calling this from the after update of a row of your
subform?
To get the after update for the subform to fire after you make an edit, you
need to something like move to another record or click a save button or
click onto a different subform or click on the parent form.

Without testing, I assume that Me.Requery would do the same as
Me.MoveNext
Me.MovePrevious

Yes, it is trickier all round when using a subform to filter a parent form,
and I choose not to pursue this way of setting up forms and subforms.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"GLT" wrote in message
...
Hi Jeanette,

Thanks for your reply,

I used your code and I beleive it works (I can see the subform flicker and
the record selector goes to the 1st record in the subform), however I am
still faced with the original problem, ie. the subform values do not
update
until I select another record on that subform. I do not understand why
this
is happeneing, I have tried the following, however it seems the only way
to
get around this is to setfocus to the subform, move +1 records, then -1
records...


Me.Parent.[ServerList].SetFocus
Me.Parent.[ServerList].Form.Requery

Me.MoveNext
Me.MovePrevious

"Jeanette Cunningham" wrote:

Assuming that both subforms are on the same form

Me.Parent.[NameOfSubformControl].Form.Requery

Note that the SubformControl may have a different name from the subform
inside it.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"GLT" wrote in message
...
Hi Barry,

Thanks for your reply, I have tried this so far:

Me.Parent![serverlist].Requery
Forms.[Get all Details].ServerList.Requery
DoCmd.Requery Forms.Parent.serverlist

None of them work...

"Barry A&P" wrote:

this is in the ballpark
in the checkbox's after update event

docmd.requery "subform#2"

the correct reference to the subform will be important for example it
may
need to be Me!subform#2 or forms!mainform.subform#2 and im not the
best
at
that.

Barry

"GLT" wrote:

Hi,

I have a main form with two subforms, one linked and one not.

When I select a checkbox on Subform#1, it affects a value on
Subform#2
(and
changes it green).

However, this only updates on Subform#1, after I select a new record
on
that
subform.

So, my question is, how can I force subform#1 to refresh after I
have
clicked my check box on Subform#2?

Any help is always greatly appreciated.

Cheers,
GLT.


.

GLT,
I think there's some confusion on your post as to which sub
is updating what sub in your scenario, but...

When changing a value on Sub1 that would effect a value on Sub2,
try Refreshing Sub1 first, and then Requerying Sub2.
The Refresh of Sub1 forces the values in the current record to be
written to it's table. (This would be the equivalent of moving off the
current record, and back). If the Sub2 re-value is based upon that newly
updated table,
then a Requery of Sub2 should do the trick.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"GLT" wrote in message
...
Hi Jeanette,

Thanks for your reply,

I used your code and I beleive it works (I can see the subform flicker and
the record selector goes to the 1st record in the subform), however I am
still faced with the original problem, ie. the subform values do not
update
until I select another record on that subform. I do not understand why
this
is happeneing, I have tried the following, however it seems the only way
to
get around this is to setfocus to the subform, move +1 records, then -1
records...


Me.Parent.[ServerList].SetFocus
Me.Parent.[ServerList].Form.Requery

Me.MoveNext
Me.MovePrevious

"Jeanette Cunningham" wrote:

Assuming that both subforms are on the same form

Me.Parent.[NameOfSubformControl].Form.Requery

Note that the SubformControl may have a different name from the subform
inside it.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"GLT" wrote in message
...
Hi Barry,

Thanks for your reply, I have tried this so far:

Me.Parent![serverlist].Requery
Forms.[Get all Details].ServerList.Requery
DoCmd.Requery Forms.Parent.serverlist

None of them work...

"Barry A&P" wrote:

this is in the ballpark
in the checkbox's after update event

docmd.requery "subform#2"

the correct reference to the subform will be important for example it
may
need to be Me!subform#2 or forms!mainform.subform#2 and im not the
best
at
that.

Barry

"GLT" wrote:

Hi,

I have a main form with two subforms, one linked and one not.

When I select a checkbox on Subform#1, it affects a value on
Subform#2
(and
changes it green).

However, this only updates on Subform#1, after I select a new record
on
that
subform.

So, my question is, how can I force subform#1 to refresh after I
have
clicked my check box on Subform#2?

Any help is always greatly appreciated.

Cheers,
GLT.


.

Hi Al and Jeanette,

Thanks for your replies.

Al you are indeed correct (I got my subforms mixed up), and performing
requery on the initial subform, then on subform #2 fixed the problem.
However, this has created a new annoyance, when you select a record on sub
form #2, and then click the check box on the initial subform and perform the
two requeries, the record selector on sub form number 2, go back to the
beggining of the recordset. There are between anywhere 300 - 600 items in
this list so its a pain to re-scroll back down to where you were.

So now what I would like to do is take a snap shot of the record set before
the requery, and then move back to after - I have tried using this code but I
get overflow errors:

Private Sub Valid_Click()

Me.Requery

Me.Parent.[ServerList].SetFocus

Dim rs, rs1 As DAO.Recordset
Dim bkMark As Long

' Search in the clone set, and get the current RecID.
Set rs = Me.RecordsetClone
rs.FindFirst "[RecID] = '" & Me.RecID & "'"
bkMark = Me.RecID

Me.Requery

If Not rs.NoMatch Then

' Set the record selector back to where it was before the requery

Set rs1 = Me.RecordsetClone
rs1.FindFirst "[RecID] = " & bkMark

Me.Bookmark = rs1.Bookmark 'reposition the form

End If
Set rs = Nothing

End Sub

Can anyone advise how to acheive this?

Cheers,
GLT.

"Al Campagna" wrote:

GLT,
I think there's some confusion on your post as to which sub
is updating what sub in your scenario, but...

When changing a value on Sub1 that would effect a value on Sub2,
try Refreshing Sub1 first, and then Requerying Sub2.
The Refresh of Sub1 forces the values in the current record to be
written to it's table. (This would be the equivalent of moving off the
current record, and back). If the Sub2 re-value is based upon that newly
updated table,
then a Requery of Sub2 should do the trick.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"GLT" wrote in message
...
Hi Jeanette,

Thanks for your reply,

I used your code and I beleive it works (I can see the subform flicker and
the record selector goes to the 1st record in the subform), however I am
still faced with the original problem, ie. the subform values do not
update
until I select another record on that subform. I do not understand why
this
is happeneing, I have tried the following, however it seems the only way
to
get around this is to setfocus to the subform, move +1 records, then -1
records...


Me.Parent.[ServerList].SetFocus
Me.Parent.[ServerList].Form.Requery

Me.MoveNext
Me.MovePrevious

"Jeanette Cunningham" wrote:

Assuming that both subforms are on the same form

Me.Parent.[NameOfSubformControl].Form.Requery

Note that the SubformControl may have a different name from the subform
inside it.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"GLT" wrote in message
...
Hi Barry,

Thanks for your reply, I have tried this so far:

Me.Parent![serverlist].Requery
Forms.[Get all Details].ServerList.Requery
DoCmd.Requery Forms.Parent.serverlist

None of them work...

"Barry A&P" wrote:

this is in the ballpark
in the checkbox's after update event

docmd.requery "subform#2"

the correct reference to the subform will be important for example it
may
need to be Me!subform#2 or forms!mainform.subform#2 and im not the
best
at
that.

Barry

"GLT" wrote:

Hi,

I have a main form with two subforms, one linked and one not.

When I select a checkbox on Subform#1, it affects a value on
Subform#2
(and
changes it green).

However, this only updates on Subform#1, after I select a new record
on
that
subform.

So, my question is, how can I force subform#1 to refresh after I
have
clicked my check box on Subform#2?

Any help is always greatly appreciated.

Cheers,
GLT.


.



.

GLT,
Stephen Lebans has an excellent file called SetGetSB, and
within that file is a function called SeTop, which restores the subform
position after a requery. I've used it several times, and it works well.
http://www.lebans.com/setgetsb.htm
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"GLT" wrote in message
...
Hi Al and Jeanette,

Thanks for your replies.

Al you are indeed correct (I got my subforms mixed up), and performing
requery on the initial subform, then on subform #2 fixed the problem.
However, this has created a new annoyance, when you select a record on sub
form #2, and then click the check box on the initial subform and perform
the
two requeries, the record selector on sub form number 2, go back to the
beggining of the recordset. There are between anywhere 300 - 600 items in
this list so its a pain to re-scroll back down to where you were.

So now what I would like to do is take a snap shot of the record set
before
the requery, and then move back to after - I have tried using this code
but I
get overflow errors:

Private Sub Valid_Click()

Me.Requery

Me.Parent.[ServerList].SetFocus

Dim rs, rs1 As DAO.Recordset
Dim bkMark As Long

' Search in the clone set, and get the current RecID.
Set rs = Me.RecordsetClone
rs.FindFirst "[RecID] = '" & Me.RecID & "'"
bkMark = Me.RecID

Me.Requery

If Not rs.NoMatch Then

' Set the record selector back to where it was before the requery

Set rs1 = Me.RecordsetClone
rs1.FindFirst "[RecID] = " & bkMark

Me.Bookmark = rs1.Bookmark 'reposition the form

End If
Set rs = Nothing

End Sub

Can anyone advise how to acheive this?

Cheers,
GLT.

"Al Campagna" wrote:

GLT,
I think there's some confusion on your post as to which sub
is updating what sub in your scenario, but...

When changing a value on Sub1 that would effect a value on Sub2,
try Refreshing Sub1 first, and then Requerying Sub2.
The Refresh of Sub1 forces the values in the current record to be
written to it's table. (This would be the equivalent of moving off the
current record, and back). If the Sub2 re-value is based upon that
newly
updated table,
then a Requery of Sub2 should do the trick.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

"GLT" wrote in message
...
Hi Jeanette,

Thanks for your reply,

I used your code and I beleive it works (I can see the subform flicker
and
the record selector goes to the 1st record in the subform), however I
am
still faced with the original problem, ie. the subform values do not
update
until I select another record on that subform. I do not understand why
this
is happeneing, I have tried the following, however it seems the only
way
to
get around this is to setfocus to the subform, move +1 records, then -1
records...


Me.Parent.[ServerList].SetFocus
Me.Parent.[ServerList].Form.Requery

Me.MoveNext
Me.MovePrevious

"Jeanette Cunningham" wrote:

Assuming that both subforms are on the same form

Me.Parent.[NameOfSubformControl].Form.Requery

Note that the SubformControl may have a different name from the
subform
inside it.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"GLT" wrote in message
...
Hi Barry,

Thanks for your reply, I have tried this so far:

Me.Parent![serverlist].Requery
Forms.[Get all Details].ServerList.Requery
DoCmd.Requery Forms.Parent.serverlist

None of them work...

"Barry A&P" wrote:

this is in the ballpark
in the checkbox's after update event

docmd.requery "subform#2"

the correct reference to the subform will be important for example
it
may
need to be Me!subform#2 or forms!mainform.subform#2 and im not the
best
at
that.

Barry

"GLT" wrote:

Hi,

I have a main form with two subforms, one linked and one not.

When I select a checkbox on Subform#1, it affects a value on
Subform#2
(and
changes it green).

However, this only updates on Subform#1, after I select a new
record
on
that
subform.

So, my question is, how can I force subform#1 to refresh after I
have
clicked my check box on Subform#2?

Any help is always greatly appreciated.

Cheers,
GLT.


.



.