refresh an open form using records from another open form?

[Graham Mandeno]

Hi Eric

OK, so let's focus on this "rogue" form. Is it a linked subform? Is there
anything unusual about it? If it's not too complex, can you recreate it and
see if the problem goes away?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

this is a weird one. 2 scenarios:

If I remove the rouge form on the other tab, then the frm_Waypoints and
frm_Road_Junctions work just fine.


I I leave the form on the tab, then the forms in question don't work.
Except
if i move the mouse off the main tab page (which contains a number of
tabbed
pages/forms), and just move it over either one of two text controls, then
the
forms synch together.

I am pretty sure this is not a database corrupt situation, because I have
done this experiment with a new DB, but I do think it is some kind of wird
replicable bug. The problem is consistent and not intemittent. Any
thoughts?

Hi Eric

Yes, I check my threads regularly, although next week I'm going off for a
couple of weeks' holiday.

Good luck with your detective work!
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I think I have found the problem, though not the solution... yet...


I created a new DB and imported the 3 files frm_Runs, frm_Waypoints,
frm_Road_Junctions. and together they worked perfectly. So I began to
import
the other forms, one-by-one... until I think I found the rouge form
causing
the problem, this form is on another tab. I will now investigate that
form
and try to discover what it is that is affecting the other forms. I
will
come
back to you when I have something tangible to tell you. In the
meantime,
do
you check your threads or have them on autonotify? I value your help
and
advice immensely and do not want to lose the connection on this
particualr
problem.

regards

Eric


:

Hi Eric

I have a hunch it may have something to do with the properties of the
'frm_Road_Junctions' form, but I have no idea what properties could be
causing it. Does that form have any filters or any other unusual
settings?

As a test, could you please try creating a new form based on your
Road_Junctions table and insert that into the subform control instead?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I removed the old subform, and created a new one. I even used the
wizard
(this time) to creat the form. It defaulted to datasheet (which is
fine
for
now), I didn't change any of the forms settings, apart from
master/child
relationship, and this form behaves the exact same way, it only
shows
the
record change when I click on it. I don't think I have a corrupt
database,
everything else works fine, and it's worth pointing out that I have
another
very similar/identical form setup on another tab in the same
database
using
master/child links to another form control and that works just fine.

:

Hi Eric

Are you saying that you see the value in the textbox changing, but
the
record in the subform does not change????

If so, this is most irregular! Can you please try doing a
compact/repair
on
the database, and if it still is not working, try creating a new,
unbound
form and adding the two subforms and the textbox, as I described in
my
NorthWind example.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

reading through your northwind example. I checked at this end and
what
I
have reflects what you have at your end.

Yes, I made the linking textbox visible, and yes the record ID's
match
exactly. As I mentioned, it works like clockwork in terms of
linking
the
correct records; they match perfectly, but I have to click into
the
frm_Road_Junctions before it will update.

It's as if the subform only wakes up when I click on it?...




:

Hmmmm... that's a puzzle.

Just to prove it for myself, I set up the following in the
NorthWind
database:

1. Create a continuous form frmOrderList, bound to Orders, with
fields
bound
to OrderID, CustomerID and OrderDate.

2. Create a standard form frmCustomers, bound to Customers
showing
all
fields.

3. Create a new, unbound form in design view. Drag onto it
frmOrderList
and
frmCustomers.

4. Rename the subform controls sbfOrderList and
sbfCurrentCustomer,
respectively.

5. Add a textbox named txtCurrentCustomer, with ControlSource
set
to:
=[sbfOrderList].[Form]![CustomerID]

6. Set the link fields for sbfCurrentCustomer as follows:
LinkMasterFields: txtCurrentCustomer
LinkChildFields: CustomerID

Switch to form view and it all works like clockwork. As I move
through
the
records in the list of orders, the matching CustomerID appears
in
the
textbox, and the matching customer record is displayed in the
subform.

Have you made your linking textbox visible to verify that the
value
in
it
is
changing correctly?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

yes, I did set the master/child setup in the
frm_Road_Junctions,
and
the
ID
numbers to correspond correctly; it's just that the
'frm_Road_Junctions'
doesn't refresh to reflect the new chosen record on the
frm_Waypoints
unless
I click a control on the 'frm_Road_Junctions' form. I even
removed
the
form,
and placed it again using the subform wizard hoping that some
unseen
voodoo
would kick in and resolve it...

to summarise, the link/engine works exactly, just no
refresh?...



:

Hi Eric

Did you set up the master/child link for the subform control
containing
frm_Road_Junctions?

I suggest you make the linking control (txtCurrentWaypoint)
visible
in
the
meantime, for debugging purposes. As you navigate the
records
in
frm_Waypoints, you should see the value in that textbox
changing
to
match
the waypoint ID for the current record. As this value
changes,
the
master/child link should automatically refilter
frm_Road_Junctions.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

Yes and No the question relating to the two 'subforms'...

The form: frm_Waypoints is a proper subform, and its
Master/Child
are
linked
to the overall Master form: frm_Runs with [Run_No] on both
settings.

The form: frm_Road_Junctions is not a dependent subform.
It
has
no
master/child relationship set. But both forms do exist
on/within
the
master/main form: frm_Runs.

Anyway, despite the two forms having different definitions,
I
tried
your
suggestion, but first I removed your initial 'On Current'
code. I
then
carried out you suggestions. Which kind of works, in so
much
as
when
I
go
into the form: frm_Road_Junctions it now neatly jumps to
the
correct
corresponding record. But what I need it to do is go to the
correct
record,
when I select the 'source' record from the frm_Waypoints
without
having
to
resort to actually clicking on anything within the
frm_Road_Junctions
or
even
the form itself. That way I can remain in the 'important'
form
which
is
frm_Waypoints and as I select various records, the
corresponding
records
within frm_Road_Junctions reveal themselves and refresh
automatically.


:

Hi Eric

The code I gave you was intended to work for two standard
forms
(no
subforms).

Now, the following statement has me intrigued:
(both forms in question are on a main/master form
called:
frm_Runs)

Do you mean that frm_Road_Junctions and frm_Waypoints are
*both*
in
subform
controls on the same main form (frm_Runs)?

If so, then you can do this with no code at all. All you
need
is a

 

[efandango]

Hi Graham,

well... it's a bit complex, but...

The form is a subform called frm_Links and is on its own tab page and is
linked to the main form (frm_Runs) using master/child [Run_No] for both
settings.

On this form are two other forms. frm_Link_Waypoints and
frm_Road_Restrictions_Links. The first one is linked to frm_Links using
master/child, and the second is linked to frm_Link_Waypoints using a filter
in the forms Filter properties. As this little collection of forms stand,
they work just fine. But it is only when I remove them do things work ok
elsewhere. The key form seems to be the 'sub main' form, frm_Links. What I
mean to say is, I tried the process of removing them one-by-one and testing
as I went along, but it is only when I remove the form 'frm_Links ' form do
things work ok elsewhere.

here's is a summary breakdown of the form heirachy:

frm_Runs (overall master form)

frm_Links (linked to frm_Runs) (Main subform and shell for the 2 forms below)

frm_Link_Waypoints (linked to frm_Links)
frm_Road_Restrictions_Links (linked to frm_Link_Waypoints - via filter)

this is the filter for the 'frm_Road_Restrictions_Links'

Run_waypoint_ID_From=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID
Or
Run_waypoint_ID_To=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID


I could try and rebuild it, but I get the feeling that I will simply
replicate the same problem.

Hi Eric

OK, so let's focus on this "rogue" form. Is it a linked subform? Is there
anything unusual about it? If it's not too complex, can you recreate it and
see if the problem goes away?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

this is a weird one. 2 scenarios:

If I remove the rouge form on the other tab, then the frm_Waypoints and
frm_Road_Junctions work just fine.


I I leave the form on the tab, then the forms in question don't work.
Except
if i move the mouse off the main tab page (which contains a number of
tabbed
pages/forms), and just move it over either one of two text controls, then
the
forms synch together.

I am pretty sure this is not a database corrupt situation, because I have
done this experiment with a new DB, but I do think it is some kind of wird
replicable bug. The problem is consistent and not intemittent. Any
thoughts?

Hi Eric

Yes, I check my threads regularly, although next week I'm going off for a
couple of weeks' holiday.

Good luck with your detective work!
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I think I have found the problem, though not the solution... yet...


I created a new DB and imported the 3 files frm_Runs, frm_Waypoints,
frm_Road_Junctions. and together they worked perfectly. So I began to
import
the other forms, one-by-one... until I think I found the rouge form
causing
the problem, this form is on another tab. I will now investigate that
form
and try to discover what it is that is affecting the other forms. I
will
come
back to you when I have something tangible to tell you. In the
meantime,
do
you check your threads or have them on autonotify? I value your help
and
advice immensely and do not want to lose the connection on this
particualr
problem.

regards

Eric


:

Hi Eric

I have a hunch it may have something to do with the properties of the
'frm_Road_Junctions' form, but I have no idea what properties could be
causing it. Does that form have any filters or any other unusual
settings?

As a test, could you please try creating a new form based on your
Road_Junctions table and insert that into the subform control instead?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I removed the old subform, and created a new one. I even used the
wizard
(this time) to creat the form. It defaulted to datasheet (which is
fine
for
now), I didn't change any of the forms settings, apart from
master/child
relationship, and this form behaves the exact same way, it only
shows
the
record change when I click on it. I don't think I have a corrupt
database,
everything else works fine, and it's worth pointing out that I have
another
very similar/identical form setup on another tab in the same
database
using
master/child links to another form control and that works just fine.

:

Hi Eric

Are you saying that you see the value in the textbox changing, but
the
record in the subform does not change????

If so, this is most irregular! Can you please try doing a
compact/repair
on
the database, and if it still is not working, try creating a new,
unbound
form and adding the two subforms and the textbox, as I described in
my
NorthWind example.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

reading through your northwind example. I checked at this end and
what
I
have reflects what you have at your end.

Yes, I made the linking textbox visible, and yes the record ID's
match
exactly. As I mentioned, it works like clockwork in terms of
linking
the
correct records; they match perfectly, but I have to click into
the
frm_Road_Junctions before it will update.

It's as if the subform only wakes up when I click on it?...




:

Hmmmm... that's a puzzle.

Just to prove it for myself, I set up the following in the
NorthWind
database:

1. Create a continuous form frmOrderList, bound to Orders, with
fields
bound
to OrderID, CustomerID and OrderDate.

2. Create a standard form frmCustomers, bound to Customers
showing
all
fields.

3. Create a new, unbound form in design view. Drag onto it
frmOrderList
and
frmCustomers.

4. Rename the subform controls sbfOrderList and
sbfCurrentCustomer,
respectively.

5. Add a textbox named txtCurrentCustomer, with ControlSource
set
to:
=[sbfOrderList].[Form]![CustomerID]

6. Set the link fields for sbfCurrentCustomer as follows:
LinkMasterFields: txtCurrentCustomer
LinkChildFields: CustomerID

Switch to form view and it all works like clockwork. As I move
through
the
records in the list of orders, the matching CustomerID appears
in
the
textbox, and the matching customer record is displayed in the
subform.

Have you made your linking textbox visible to verify that the
value
in
it
is
changing correctly?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

yes, I did set the master/child setup in the
frm_Road_Junctions,
and
the
ID
numbers to correspond correctly; it's just that the
'frm_Road_Junctions'
doesn't refresh to reflect the new chosen record on the
frm_Waypoints
unless
I click a control on the 'frm_Road_Junctions' form. I even
removed
the
form,
and placed it again using the subform wizard hoping that some
unseen
voodoo
would kick in and resolve it...

to summarise, the link/engine works exactly, just no
refresh?...



:

Hi Eric

Did you set up the master/child link for the subform control
containing
frm_Road_Junctions?

I suggest you make the linking control (txtCurrentWaypoint)
visible
in
the
meantime, for debugging purposes. As you navigate the
records
in
frm_Waypoints, you should see the value in that textbox
changing
to
match
the waypoint ID for the current record. As this value
changes,
the
master/child link should automatically refilter
frm_Road_Junctions.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

Yes and No the question relating to the two 'subforms'...

The form: frm_Waypoints is a proper subform, and its
Master/Child
are
linked
to the overall Master form: frm_Runs with [Run_No] on both
settings.

The form: frm_Road_Junctions is not a dependent subform.
It
has

 

[Graham Mandeno]

Hi Eric

I wish I hadn't asked! ;-)

OK, I suspect that the complexity of the interaction between the nested
subforms is causing some sort of timing problem that is screwing up the
normal updating of frm_Road_Junctions.

As a workaround (and this is a complete stab in the dark!) try adding this
in Form_Current for frm_Waypoints:

DoEvents
Me.Parent!sbf_Road_Junctions.Form.Requery


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

well... it's a bit complex, but...

The form is a subform called frm_Links and is on its own tab page and is
linked to the main form (frm_Runs) using master/child [Run_No] for both
settings.

On this form are two other forms. frm_Link_Waypoints and
frm_Road_Restrictions_Links. The first one is linked to frm_Links using
master/child, and the second is linked to frm_Link_Waypoints using a
filter
in the forms Filter properties. As this little collection of forms stand,
they work just fine. But it is only when I remove them do things work ok
elsewhere. The key form seems to be the 'sub main' form, frm_Links. What I
mean to say is, I tried the process of removing them one-by-one and
testing
as I went along, but it is only when I remove the form 'frm_Links ' form
do
things work ok elsewhere.

here's is a summary breakdown of the form heirachy:

frm_Runs (overall master form)

frm_Links (linked to frm_Runs) (Main subform and shell for the 2 forms
below)

frm_Link_Waypoints (linked to frm_Links)
frm_Road_Restrictions_Links (linked to frm_Link_Waypoints - via filter)

this is the filter for the 'frm_Road_Restrictions_Links'

Run_waypoint_ID_From=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID
Or
Run_waypoint_ID_To=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID


I could try and rebuild it, but I get the feeling that I will simply
replicate the same problem.

Hi Eric

OK, so let's focus on this "rogue" form. Is it a linked subform? Is
there
anything unusual about it? If it's not too complex, can you recreate it
and
see if the problem goes away?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

this is a weird one. 2 scenarios:

If I remove the rouge form on the other tab, then the frm_Waypoints and
frm_Road_Junctions work just fine.


I I leave the form on the tab, then the forms in question don't work.
Except
if i move the mouse off the main tab page (which contains a number of
tabbed
pages/forms), and just move it over either one of two text controls,
then
the
forms synch together.

I am pretty sure this is not a database corrupt situation, because I
have
done this experiment with a new DB, but I do think it is some kind of
wird
replicable bug. The problem is consistent and not intemittent. Any
thoughts?



:

Hi Eric

Yes, I check my threads regularly, although next week I'm going off
for a
couple of weeks' holiday.

Good luck with your detective work!
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I think I have found the problem, though not the solution... yet...


I created a new DB and imported the 3 files frm_Runs, frm_Waypoints,
frm_Road_Junctions. and together they worked perfectly. So I began
to
import
the other forms, one-by-one... until I think I found the rouge form
causing
the problem, this form is on another tab. I will now investigate
that
form
and try to discover what it is that is affecting the other forms. I
will
come
back to you when I have something tangible to tell you. In the
meantime,
do
you check your threads or have them on autonotify? I value your help
and
advice immensely and do not want to lose the connection on this
particualr
problem.

regards

Eric


:

Hi Eric

I have a hunch it may have something to do with the properties of
the
'frm_Road_Junctions' form, but I have no idea what properties could
be
causing it. Does that form have any filters or any other unusual
settings?

As a test, could you please try creating a new form based on your
Road_Junctions table and insert that into the subform control
instead?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I removed the old subform, and created a new one. I even used the
wizard
(this time) to creat the form. It defaulted to datasheet (which
is
fine
for
now), I didn't change any of the forms settings, apart from
master/child
relationship, and this form behaves the exact same way, it only
shows
the
record change when I click on it. I don't think I have a corrupt
database,
everything else works fine, and it's worth pointing out that I
have
another
very similar/identical form setup on another tab in the same
database
using
master/child links to another form control and that works just
fine.

:

Hi Eric

Are you saying that you see the value in the textbox changing,
but
the
record in the subform does not change????

If so, this is most irregular! Can you please try doing a
compact/repair
on
the database, and if it still is not working, try creating a
new,
unbound
form and adding the two subforms and the textbox, as I described
in
my
NorthWind example.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

reading through your northwind example. I checked at this end
and
what
I
have reflects what you have at your end.

Yes, I made the linking textbox visible, and yes the record
ID's
match
exactly. As I mentioned, it works like clockwork in terms of
linking
the
correct records; they match perfectly, but I have to click
into
the
frm_Road_Junctions before it will update.

It's as if the subform only wakes up when I click on it?...




:

Hmmmm... that's a puzzle.

Just to prove it for myself, I set up the following in the
NorthWind
database:

1. Create a continuous form frmOrderList, bound to Orders,
with
fields
bound
to OrderID, CustomerID and OrderDate.

2. Create a standard form frmCustomers, bound to Customers
showing
all
fields.

3. Create a new, unbound form in design view. Drag onto it
frmOrderList
and
frmCustomers.

4. Rename the subform controls sbfOrderList and
sbfCurrentCustomer,
respectively.

5. Add a textbox named txtCurrentCustomer, with ControlSource
set
to:
=[sbfOrderList].[Form]![CustomerID]

6. Set the link fields for sbfCurrentCustomer as follows:
LinkMasterFields: txtCurrentCustomer
LinkChildFields: CustomerID

Switch to form view and it all works like clockwork. As I
move
through
the
records in the list of orders, the matching CustomerID
appears
in
the
textbox, and the matching customer record is displayed in the
subform.

Have you made your linking textbox visible to verify that the
value
in
it
is
changing correctly?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

yes, I did set the master/child setup in the
frm_Road_Junctions,
and
the
ID
numbers to correspond correctly; it's just that the
'frm_Road_Junctions'
doesn't refresh to reflect the new chosen record on the
frm_Waypoints
unless
I click a control on the 'frm_Road_Junctions' form. I even
removed
the
form,
and placed it again using the subform wizard hoping that
some
unseen
voodoo
would kick in and resolve it...

to summarise, the link/engine works exactly, just no
refresh?...



:

Hi Eric

Did you set up the master/child link for the subform
control
containing
frm_Road_Junctions?

I suggest you make the linking control
(txtCurrentWaypoint)
visible
in
the
meantime, for debugging purposes. As you navigate the
records
in
frm_Waypoints, you should see the value in that textbox
changing
to
match
the waypoint ID for the current record. As this value
changes,
the
master/child link should automatically refilter
frm_Road_Junctions.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

Yes and No the question relating to the two
'subforms'...

The form: frm_Waypoints is a proper subform, and its
Master/Child
are
linked
to the overall Master form: frm_Runs with [Run_No] on
both
settings.

The form: frm_Road_Junctions is not a dependent
subform.
It
has

 

[efandango]

Hi Graham,

I placed this in the form's on current property:

Private Sub Form_Current()
DoEvents
Me.Parent!frm_Road_Junctions.Form.Requery
End Sub

Run-time error 2455

"You entered an expression that has an invalid reference to the property
Form/Report"


and get this error message:

Hi Eric

I wish I hadn't asked! ;-)

OK, I suspect that the complexity of the interaction between the nested
subforms is causing some sort of timing problem that is screwing up the
normal updating of frm_Road_Junctions.

As a workaround (and this is a complete stab in the dark!) try adding this
in Form_Current for frm_Waypoints:

DoEvents
Me.Parent!sbf_Road_Junctions.Form.Requery


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

well... it's a bit complex, but...

The form is a subform called frm_Links and is on its own tab page and is
linked to the main form (frm_Runs) using master/child [Run_No] for both
settings.

On this form are two other forms. frm_Link_Waypoints and
frm_Road_Restrictions_Links. The first one is linked to frm_Links using
master/child, and the second is linked to frm_Link_Waypoints using a
filter
in the forms Filter properties. As this little collection of forms stand,
they work just fine. But it is only when I remove them do things work ok
elsewhere. The key form seems to be the 'sub main' form, frm_Links. What I
mean to say is, I tried the process of removing them one-by-one and
testing
as I went along, but it is only when I remove the form 'frm_Links ' form
do
things work ok elsewhere.

here's is a summary breakdown of the form heirachy:

frm_Runs (overall master form)

frm_Links (linked to frm_Runs) (Main subform and shell for the 2 forms
below)

frm_Link_Waypoints (linked to frm_Links)
frm_Road_Restrictions_Links (linked to frm_Link_Waypoints - via filter)

this is the filter for the 'frm_Road_Restrictions_Links'

Run_waypoint_ID_From=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID
Or
Run_waypoint_ID_To=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID


I could try and rebuild it, but I get the feeling that I will simply
replicate the same problem.

Hi Eric

OK, so let's focus on this "rogue" form. Is it a linked subform? Is
there
anything unusual about it? If it's not too complex, can you recreate it
and
see if the problem goes away?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

this is a weird one. 2 scenarios:

If I remove the rouge form on the other tab, then the frm_Waypoints and
frm_Road_Junctions work just fine.


I I leave the form on the tab, then the forms in question don't work.
Except
if i move the mouse off the main tab page (which contains a number of
tabbed
pages/forms), and just move it over either one of two text controls,
then
the
forms synch together.

I am pretty sure this is not a database corrupt situation, because I
have
done this experiment with a new DB, but I do think it is some kind of
wird
replicable bug. The problem is consistent and not intemittent. Any
thoughts?



:

Hi Eric

Yes, I check my threads regularly, although next week I'm going off
for a
couple of weeks' holiday.

Good luck with your detective work!
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I think I have found the problem, though not the solution... yet...


I created a new DB and imported the 3 files frm_Runs, frm_Waypoints,
frm_Road_Junctions. and together they worked perfectly. So I began
to
import
the other forms, one-by-one... until I think I found the rouge form
causing
the problem, this form is on another tab. I will now investigate
that
form
and try to discover what it is that is affecting the other forms. I
will
come
back to you when I have something tangible to tell you. In the
meantime,
do
you check your threads or have them on autonotify? I value your help
and
advice immensely and do not want to lose the connection on this
particualr
problem.

regards

Eric


:

Hi Eric

I have a hunch it may have something to do with the properties of
the
'frm_Road_Junctions' form, but I have no idea what properties could
be
causing it. Does that form have any filters or any other unusual
settings?

As a test, could you please try creating a new form based on your
Road_Junctions table and insert that into the subform control
instead?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I removed the old subform, and created a new one. I even used the
wizard
(this time) to creat the form. It defaulted to datasheet (which
is
fine
for
now), I didn't change any of the forms settings, apart from
master/child
relationship, and this form behaves the exact same way, it only
shows
the
record change when I click on it. I don't think I have a corrupt
database,
everything else works fine, and it's worth pointing out that I
have
another
very similar/identical form setup on another tab in the same
database
using
master/child links to another form control and that works just
fine.

:

Hi Eric

Are you saying that you see the value in the textbox changing,
but
the
record in the subform does not change????

If so, this is most irregular! Can you please try doing a
compact/repair
on
the database, and if it still is not working, try creating a
new,
unbound
form and adding the two subforms and the textbox, as I described
in
my
NorthWind example.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

reading through your northwind example. I checked at this end
and
what
I
have reflects what you have at your end.

Yes, I made the linking textbox visible, and yes the record
ID's
match
exactly. As I mentioned, it works like clockwork in terms of
linking
the
correct records; they match perfectly, but I have to click
into
the
frm_Road_Junctions before it will update.

It's as if the subform only wakes up when I click on it?...




:

Hmmmm... that's a puzzle.

Just to prove it for myself, I set up the following in the
NorthWind
database:

1. Create a continuous form frmOrderList, bound to Orders,
with
fields
bound
to OrderID, CustomerID and OrderDate.

2. Create a standard form frmCustomers, bound to Customers
showing
all
fields.

3. Create a new, unbound form in design view. Drag onto it
frmOrderList
and
frmCustomers.

4. Rename the subform controls sbfOrderList and
sbfCurrentCustomer,
respectively.

5. Add a textbox named txtCurrentCustomer, with ControlSource
set
to:
=[sbfOrderList].[Form]![CustomerID]

6. Set the link fields for sbfCurrentCustomer as follows:
LinkMasterFields: txtCurrentCustomer
LinkChildFields: CustomerID

Switch to form view and it all works like clockwork. As I
move
through
the
records in the list of orders, the matching CustomerID
appears
in
the

 

[efandango]

When I ok the error message, I get another saying:

The expression you entered refers to an object that is closed or doesn't
exist.

regards

Eric

Hi Graham,

I placed this in the form's on current property:

Private Sub Form_Current()
DoEvents
Me.Parent!frm_Road_Junctions.Form.Requery
End Sub

Run-time error 2455

"You entered an expression that has an invalid reference to the property
Form/Report"


and get this error message:

Hi Eric

I wish I hadn't asked! ;-)

OK, I suspect that the complexity of the interaction between the nested
subforms is causing some sort of timing problem that is screwing up the
normal updating of frm_Road_Junctions.

As a workaround (and this is a complete stab in the dark!) try adding this
in Form_Current for frm_Waypoints:

DoEvents
Me.Parent!sbf_Road_Junctions.Form.Requery


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

well... it's a bit complex, but...

The form is a subform called frm_Links and is on its own tab page and is
linked to the main form (frm_Runs) using master/child [Run_No] for both
settings.

On this form are two other forms. frm_Link_Waypoints and
frm_Road_Restrictions_Links. The first one is linked to frm_Links using
master/child, and the second is linked to frm_Link_Waypoints using a
filter
in the forms Filter properties. As this little collection of forms stand,
they work just fine. But it is only when I remove them do things work ok
elsewhere. The key form seems to be the 'sub main' form, frm_Links. What I
mean to say is, I tried the process of removing them one-by-one and
testing
as I went along, but it is only when I remove the form 'frm_Links ' form
do
things work ok elsewhere.

here's is a summary breakdown of the form heirachy:

frm_Runs (overall master form)

frm_Links (linked to frm_Runs) (Main subform and shell for the 2 forms
below)

frm_Link_Waypoints (linked to frm_Links)
frm_Road_Restrictions_Links (linked to frm_Link_Waypoints - via filter)

this is the filter for the 'frm_Road_Restrictions_Links'

Run_waypoint_ID_From=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID
Or
Run_waypoint_ID_To=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID


I could try and rebuild it, but I get the feeling that I will simply
replicate the same problem.


:

Hi Eric

OK, so let's focus on this "rogue" form. Is it a linked subform? Is
there
anything unusual about it? If it's not too complex, can you recreate it
and
see if the problem goes away?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

this is a weird one. 2 scenarios:

If I remove the rouge form on the other tab, then the frm_Waypoints and
frm_Road_Junctions work just fine.


I I leave the form on the tab, then the forms in question don't work.
Except
if i move the mouse off the main tab page (which contains a number of
tabbed
pages/forms), and just move it over either one of two text controls,
then
the
forms synch together.

I am pretty sure this is not a database corrupt situation, because I
have
done this experiment with a new DB, but I do think it is some kind of
wird
replicable bug. The problem is consistent and not intemittent. Any
thoughts?



:

Hi Eric

Yes, I check my threads regularly, although next week I'm going off
for a
couple of weeks' holiday.

Good luck with your detective work!
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I think I have found the problem, though not the solution... yet...


I created a new DB and imported the 3 files frm_Runs, frm_Waypoints,
frm_Road_Junctions. and together they worked perfectly. So I began
to
import
the other forms, one-by-one... until I think I found the rouge form
causing
the problem, this form is on another tab. I will now investigate
that
form
and try to discover what it is that is affecting the other forms. I
will
come
back to you when I have something tangible to tell you. In the
meantime,
do
you check your threads or have them on autonotify? I value your help
and
advice immensely and do not want to lose the connection on this
particualr
problem.

regards

Eric


:

Hi Eric

I have a hunch it may have something to do with the properties of
the
'frm_Road_Junctions' form, but I have no idea what properties could
be
causing it. Does that form have any filters or any other unusual
settings?

As a test, could you please try creating a new form based on your
Road_Junctions table and insert that into the subform control
instead?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I removed the old subform, and created a new one. I even used the
wizard
(this time) to creat the form. It defaulted to datasheet (which
is
fine
for
now), I didn't change any of the forms settings, apart from
master/child
relationship, and this form behaves the exact same way, it only
shows
the
record change when I click on it. I don't think I have a corrupt
database,
everything else works fine, and it's worth pointing out that I
have
another
very similar/identical form setup on another tab in the same
database
using
master/child links to another form control and that works just
fine.

:

Hi Eric

Are you saying that you see the value in the textbox changing,
but
the
record in the subform does not change????

If so, this is most irregular! Can you please try doing a
compact/repair
on
the database, and if it still is not working, try creating a
new,
unbound
form and adding the two subforms and the textbox, as I described
in
my
NorthWind example.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

reading through your northwind example. I checked at this end
and
what
I
have reflects what you have at your end.

Yes, I made the linking textbox visible, and yes the record
ID's
match
exactly. As I mentioned, it works like clockwork in terms of
linking
the
correct records; they match perfectly, but I have to click
into
the
frm_Road_Junctions before it will update.

It's as if the subform only wakes up when I click on it?...




:

Hmmmm... that's a puzzle.

Just to prove it for myself, I set up the following in the
NorthWind
database:

1. Create a continuous form frmOrderList, bound to Orders,
with
fields
bound
to OrderID, CustomerID and OrderDate.

2. Create a standard form frmCustomers, bound to Customers
showing
all
fields.

3. Create a new, unbound form in design view. Drag onto it
frmOrderList
and
frmCustomers.

 

[efandango]

Hi Graham,

for now, ignore my last two posts about 'bad form references/form's not
existing', because I think I have nailed the specific cause of the problem.
It is related to the nested subforms, specifically the 'shell form' for the
two sub forms on that tab. This shell form has a master/child link to the
overall master runs table, using the 'Run_No' field.

I know this is the problem, but because of it's complexity, I am finding it
difficult to explain it lucidly, so please bear with me for now, and I will
come back to you with a proper explanation that will hopefully enable you to
give me guidance on how to solve it.

Suffice it to say, it will involve a query, where I take/tranpose one field
value from one record and another field value from the next record (which is
essentially what the form in question has been doing).

regards

Eric

Hi Eric

I wish I hadn't asked! ;-)

OK, I suspect that the complexity of the interaction between the nested
subforms is causing some sort of timing problem that is screwing up the
normal updating of frm_Road_Junctions.

As a workaround (and this is a complete stab in the dark!) try adding this
in Form_Current for frm_Waypoints:

DoEvents
Me.Parent!sbf_Road_Junctions.Form.Requery


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

well... it's a bit complex, but...

The form is a subform called frm_Links and is on its own tab page and is
linked to the main form (frm_Runs) using master/child [Run_No] for both
settings.

On this form are two other forms. frm_Link_Waypoints and
frm_Road_Restrictions_Links. The first one is linked to frm_Links using
master/child, and the second is linked to frm_Link_Waypoints using a
filter
in the forms Filter properties. As this little collection of forms stand,
they work just fine. But it is only when I remove them do things work ok
elsewhere. The key form seems to be the 'sub main' form, frm_Links. What I
mean to say is, I tried the process of removing them one-by-one and
testing
as I went along, but it is only when I remove the form 'frm_Links ' form
do
things work ok elsewhere.

here's is a summary breakdown of the form heirachy:

frm_Runs (overall master form)

frm_Links (linked to frm_Runs) (Main subform and shell for the 2 forms
below)

frm_Link_Waypoints (linked to frm_Links)
frm_Road_Restrictions_Links (linked to frm_Link_Waypoints - via filter)

this is the filter for the 'frm_Road_Restrictions_Links'

Run_waypoint_ID_From=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID
Or
Run_waypoint_ID_To=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID


I could try and rebuild it, but I get the feeling that I will simply
replicate the same problem.

Hi Eric

OK, so let's focus on this "rogue" form. Is it a linked subform? Is
there
anything unusual about it? If it's not too complex, can you recreate it
and
see if the problem goes away?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

this is a weird one. 2 scenarios:

If I remove the rouge form on the other tab, then the frm_Waypoints and
frm_Road_Junctions work just fine.


I I leave the form on the tab, then the forms in question don't work.
Except
if i move the mouse off the main tab page (which contains a number of
tabbed
pages/forms), and just move it over either one of two text controls,
then
the
forms synch together.

I am pretty sure this is not a database corrupt situation, because I
have
done this experiment with a new DB, but I do think it is some kind of
wird
replicable bug. The problem is consistent and not intemittent. Any
thoughts?



:

Hi Eric

Yes, I check my threads regularly, although next week I'm going off
for a
couple of weeks' holiday.

Good luck with your detective work!
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I think I have found the problem, though not the solution... yet...


I created a new DB and imported the 3 files frm_Runs, frm_Waypoints,
frm_Road_Junctions. and together they worked perfectly. So I began
to
import
the other forms, one-by-one... until I think I found the rouge form
causing
the problem, this form is on another tab. I will now investigate
that
form
and try to discover what it is that is affecting the other forms. I
will
come
back to you when I have something tangible to tell you. In the
meantime,
do
you check your threads or have them on autonotify? I value your help
and
advice immensely and do not want to lose the connection on this
particualr
problem.

regards

Eric


:

Hi Eric

I have a hunch it may have something to do with the properties of
the
'frm_Road_Junctions' form, but I have no idea what properties could
be
causing it. Does that form have any filters or any other unusual
settings?

As a test, could you please try creating a new form based on your
Road_Junctions table and insert that into the subform control
instead?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I removed the old subform, and created a new one. I even used the
wizard
(this time) to creat the form. It defaulted to datasheet (which
is
fine
for
now), I didn't change any of the forms settings, apart from
master/child
relationship, and this form behaves the exact same way, it only
shows
the
record change when I click on it. I don't think I have a corrupt
database,
everything else works fine, and it's worth pointing out that I
have
another
very similar/identical form setup on another tab in the same
database
using
master/child links to another form control and that works just
fine.

:

Hi Eric

Are you saying that you see the value in the textbox changing,
but
the
record in the subform does not change????

If so, this is most irregular! Can you please try doing a
compact/repair
on
the database, and if it still is not working, try creating a
new,
unbound
form and adding the two subforms and the textbox, as I described
in
my
NorthWind example.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

reading through your northwind example. I checked at this end
and
what
I
have reflects what you have at your end.

Yes, I made the linking textbox visible, and yes the record
ID's
match
exactly. As I mentioned, it works like clockwork in terms of
linking
the
correct records; they match perfectly, but I have to click
into
the
frm_Road_Junctions before it will update.

It's as if the subform only wakes up when I click on it?...




:

Hmmmm... that's a puzzle.

Just to prove it for myself, I set up the following in the
NorthWind
database:

1. Create a continuous form frmOrderList, bound to Orders,
with
fields
bound
to OrderID, CustomerID and OrderDate.

2. Create a standard form frmCustomers, bound to Customers
showing
all
fields.

3. Create a new, unbound form in design view. Drag onto it
frmOrderList
and
frmCustomers.

4. Rename the subform controls sbfOrderList and
sbfCurrentCustomer,
respectively.

5. Add a textbox named txtCurrentCustomer, with ControlSource
set
to:
=[sbfOrderList].[Form]![CustomerID]

6. Set the link fields for sbfCurrentCustomer as follows:
LinkMasterFields: txtCurrentCustomer
LinkChildFields: CustomerID

Switch to form view and it all works like clockwork. As I
move
through
the
records in the list of orders, the matching CustomerID
appears
in
the

 

[efandango]

Hi Graham,


If you have not done so, could i please ask you to read my penultimate post.

meanwhile...

I am very sure this will resolve the problem with the rougue form.

I have a main table called tbl_Runs. This table stores a two pair sets of
addresses made up of two fields; a '[Run_From]' and a '[Run_To]' field.
(there are other fields such as postcodes, but the key to it all is the
two-pair sets, and the need to rearrange the sequence of which pairs they
appear in.

each two set of addresses is related to a Run Number [Run_No].

so for example, This is how things correctly stand at the moment:

Run No RUN_No_ID Run From Run To

1 125 STATION ROAD SUNRAY SQUARE
2 126 THORNYCROFT LANE QUEENS HOSPITAL
3 127 SOUTH RIVER STATION ROYCE AVENUE
4 128 BOOKER STREET MARLIN HARBOUR
5 129 STANLEY ROAD and so on...

what I need to do is have the 2nd field [Run_From] from the first record
replace the first field [Run_To] from the second record, and so on...

so that I get this sequence:

Run No RUN_No_ID Run From Run To

STATION ROAD (not used)
1 125 THORNYCROFT LANE SUNRAY SQUARE
2 126 SOUTH RIVER STATION QUEENS HOSPITAL
3 127 BOOKER STREET ROYCE AVENUE
4 128 STANLEY ROAD MARLIN HARBOUR
5 129 and so on... and so on...

Can you help me with a query do do this, which was what the 'Run_Links' form
was doing, until it became upset with the new form on the block...

regards

Eric

Here is the table breakdown:

Run_No Number
RUN_No_ID Autonumber
Run_From Text
Run_To Text

Hi Graham,

for now, ignore my last two posts about 'bad form references/form's not
existing', because I think I have nailed the specific cause of the problem.
It is related to the nested subforms, specifically the 'shell form' for the
two sub forms on that tab. This shell form has a master/child link to the
overall master runs table, using the 'Run_No' field.

I know this is the problem, but because of it's complexity, I am finding it
difficult to explain it lucidly, so please bear with me for now, and I will
come back to you with a proper explanation that will hopefully enable you to
give me guidance on how to solve it.

Suffice it to say, it will involve a query, where I take/tranpose one field
value from one record and another field value from the next record (which is
essentially what the form in question has been doing).

regards

Eric

Hi Eric

I wish I hadn't asked! ;-)

OK, I suspect that the complexity of the interaction between the nested
subforms is causing some sort of timing problem that is screwing up the
normal updating of frm_Road_Junctions.

As a workaround (and this is a complete stab in the dark!) try adding this
in Form_Current for frm_Waypoints:

DoEvents
Me.Parent!sbf_Road_Junctions.Form.Requery


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

well... it's a bit complex, but...

The form is a subform called frm_Links and is on its own tab page and is
linked to the main form (frm_Runs) using master/child [Run_No] for both
settings.

On this form are two other forms. frm_Link_Waypoints and
frm_Road_Restrictions_Links. The first one is linked to frm_Links using
master/child, and the second is linked to frm_Link_Waypoints using a
filter
in the forms Filter properties. As this little collection of forms stand,
they work just fine. But it is only when I remove them do things work ok
elsewhere. The key form seems to be the 'sub main' form, frm_Links. What I
mean to say is, I tried the process of removing them one-by-one and
testing
as I went along, but it is only when I remove the form 'frm_Links ' form
do
things work ok elsewhere.

here's is a summary breakdown of the form heirachy:

frm_Runs (overall master form)

frm_Links (linked to frm_Runs) (Main subform and shell for the 2 forms
below)

frm_Link_Waypoints (linked to frm_Links)
frm_Road_Restrictions_Links (linked to frm_Link_Waypoints - via filter)

this is the filter for the 'frm_Road_Restrictions_Links'

Run_waypoint_ID_From=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID
Or
Run_waypoint_ID_To=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID


I could try and rebuild it, but I get the feeling that I will simply
replicate the same problem.


:

Hi Eric

OK, so let's focus on this "rogue" form. Is it a linked subform? Is
there
anything unusual about it? If it's not too complex, can you recreate it
and
see if the problem goes away?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

this is a weird one. 2 scenarios:

If I remove the rouge form on the other tab, then the frm_Waypoints and
frm_Road_Junctions work just fine.


I I leave the form on the tab, then the forms in question don't work.
Except
if i move the mouse off the main tab page (which contains a number of
tabbed
pages/forms), and just move it over either one of two text controls,
then
the
forms synch together.

I am pretty sure this is not a database corrupt situation, because I
have
done this experiment with a new DB, but I do think it is some kind of
wird
replicable bug. The problem is consistent and not intemittent. Any
thoughts?



:

Hi Eric

Yes, I check my threads regularly, although next week I'm going off
for a
couple of weeks' holiday.

Good luck with your detective work!
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I think I have found the problem, though not the solution... yet...


I created a new DB and imported the 3 files frm_Runs, frm_Waypoints,
frm_Road_Junctions. and together they worked perfectly. So I began
to
import
the other forms, one-by-one... until I think I found the rouge form
causing
the problem, this form is on another tab. I will now investigate
that
form
and try to discover what it is that is affecting the other forms. I
will
come
back to you when I have something tangible to tell you. In the
meantime,
do
you check your threads or have them on autonotify? I value your help
and
advice immensely and do not want to lose the connection on this
particualr
problem.

regards

Eric


:

Hi Eric

I have a hunch it may have something to do with the properties of
the
'frm_Road_Junctions' form, but I have no idea what properties could
be
causing it. Does that form have any filters or any other unusual
settings?

As a test, could you please try creating a new form based on your
Road_Junctions table and insert that into the subform control
instead?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I removed the old subform, and created a new one. I even used the
wizard
(this time) to creat the form. It defaulted to datasheet (which
is
fine
for
now), I didn't change any of the forms settings, apart from
master/child
relationship, and this form behaves the exact same way, it only
shows
the
record change when I click on it. I don't think I have a corrupt
database,
everything else works fine, and it's worth pointing out that I
have
another
very similar/identical form setup on another tab in the same
database
using
master/child links to another form control and that works just
fine.

:

Hi Eric

Are you saying that you see the value in the textbox changing,
but
the
record in the subform does not change????

If so, this is most irregular! Can you please try doing a
compact/repair
on
the database, and if it still is not working, try creating a
new,
unbound
form and adding the two subforms and the textbox, as I described
in
my
NorthWind example.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

reading through your northwind example. I checked at this end
and
what
I
have reflects what you have at your end.

Yes, I made the linking textbox visible, and yes the record
ID's
match
exactly. As I mentioned, it works like clockwork in terms of
linking
the
correct records; they match perfectly, but I have to click
into
the
frm_Road_Junctions before it will update.

It's as if the subform only wakes up when I click on it?...




:

Hmmmm... that's a puzzle.

Just to prove it for myself, I set up the following in the
NorthWind
database:

1. Create a continuous form frmOrderList, bound to Orders,
with
fields
bound
to OrderID, CustomerID and OrderDate.

2. Create a standard form frmCustomers, bound to Customers
showing
all
fields.

3. Create a new, unbound form in design view. Drag onto it

 

[Graham Mandeno]

Hi Eric

Yes, I read your penultimate post and was ignoring you (for the meanwhile)
as requested ;-)

I need to know more about [Run_No] and [Run_No_ID]. Are they both unique?
Is one of them the primary key? Is either of them guaranteed to be in
ascending order of runs? If not, how do you ascertain the order of the
records? If so, does this ordered field guarantee no gaps?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,


If you have not done so, could i please ask you to read my penultimate
post.

meanwhile...

I am very sure this will resolve the problem with the rougue form.

I have a main table called tbl_Runs. This table stores a two pair sets of
addresses made up of two fields; a '[Run_From]' and a '[Run_To]' field.
(there are other fields such as postcodes, but the key to it all is the
two-pair sets, and the need to rearrange the sequence of which pairs they
appear in.

each two set of addresses is related to a Run Number [Run_No].

so for example, This is how things correctly stand at the moment:

Run No RUN_No_ID Run From Run To

1 125 STATION ROAD SUNRAY SQUARE
2 126 THORNYCROFT LANE QUEENS HOSPITAL
3 127 SOUTH RIVER STATION ROYCE AVENUE
4 128 BOOKER STREET MARLIN HARBOUR
5 129 STANLEY ROAD and so on...

what I need to do is have the 2nd field [Run_From] from the first record
replace the first field [Run_To] from the second record, and so on...

so that I get this sequence:

Run No RUN_No_ID Run From Run To

STATION ROAD (not used)
1 125 THORNYCROFT LANE SUNRAY SQUARE
2 126 SOUTH RIVER STATION QUEENS HOSPITAL
3 127 BOOKER STREET ROYCE AVENUE
4 128 STANLEY ROAD MARLIN HARBOUR
5 129 and so on... and so on...

Can you help me with a query do do this, which was what the 'Run_Links'
form
was doing, until it became upset with the new form on the block...

regards

Eric

Here is the table breakdown:

Run_No Number
RUN_No_ID Autonumber
Run_From Text
Run_To Text

Hi Graham,

for now, ignore my last two posts about 'bad form references/form's not
existing', because I think I have nailed the specific cause of the
problem.
It is related to the nested subforms, specifically the 'shell form' for
the
two sub forms on that tab. This shell form has a master/child link to the
overall master runs table, using the 'Run_No' field.

I know this is the problem, but because of it's complexity, I am finding
it
difficult to explain it lucidly, so please bear with me for now, and I
will
come back to you with a proper explanation that will hopefully enable you
to
give me guidance on how to solve it.

Suffice it to say, it will involve a query, where I take/tranpose one
field
value from one record and another field value from the next record (which
is
essentially what the form in question has been doing).

regards

Eric

Hi Eric

I wish I hadn't asked! ;-)

OK, I suspect that the complexity of the interaction between the nested
subforms is causing some sort of timing problem that is screwing up the
normal updating of frm_Road_Junctions.

As a workaround (and this is a complete stab in the dark!) try adding
this
in Form_Current for frm_Waypoints:

DoEvents
Me.Parent!sbf_Road_Junctions.Form.Requery


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hi Graham,

well... it's a bit complex, but...

The form is a subform called frm_Links and is on its own tab page and
is
linked to the main form (frm_Runs) using master/child [Run_No] for
both
settings.

On this form are two other forms. frm_Link_Waypoints and
frm_Road_Restrictions_Links. The first one is linked to frm_Links
using
master/child, and the second is linked to frm_Link_Waypoints using a
filter
in the forms Filter properties. As this little collection of forms
stand,
they work just fine. But it is only when I remove them do things work
ok
elsewhere. The key form seems to be the 'sub main' form, frm_Links.
What I
mean to say is, I tried the process of removing them one-by-one and
testing
as I went along, but it is only when I remove the form 'frm_Links '
form
do
things work ok elsewhere.

here's is a summary breakdown of the form heirachy:

frm_Runs (overall master form)

frm_Links (linked to frm_Runs) (Main subform and shell for the 2
forms
below)

frm_Link_Waypoints (linked to frm_Links)
frm_Road_Restrictions_Links (linked to frm_Link_Waypoints - via
filter)

this is the filter for the 'frm_Road_Restrictions_Links'

Run_waypoint_ID_From=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID
Or
Run_waypoint_ID_To=Forms!frm_Runs!frm_Links!frm_Link_Waypoints.Form!StreetNameID


I could try and rebuild it, but I get the feeling that I will simply
replicate the same problem.


:

Hi Eric

OK, so let's focus on this "rogue" form. Is it a linked subform?
Is
there
anything unusual about it? If it's not too complex, can you
recreate it
and
see if the problem goes away?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

this is a weird one. 2 scenarios:

If I remove the rouge form on the other tab, then the
frm_Waypoints and
frm_Road_Junctions work just fine.


I I leave the form on the tab, then the forms in question don't
work.
Except
if i move the mouse off the main tab page (which contains a number
of
tabbed
pages/forms), and just move it over either one of two text
controls,
then
the
forms synch together.

I am pretty sure this is not a database corrupt situation, because
I
have
done this experiment with a new DB, but I do think it is some kind
of
wird
replicable bug. The problem is consistent and not intemittent. Any
thoughts?



:

Hi Eric

Yes, I check my threads regularly, although next week I'm going
off
for a
couple of weeks' holiday.

Good luck with your detective work!
--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

I think I have found the problem, though not the solution...
yet...


I created a new DB and imported the 3 files frm_Runs,
frm_Waypoints,
frm_Road_Junctions. and together they worked perfectly. So I
began
to
import
the other forms, one-by-one... until I think I found the rouge
form
causing
the problem, this form is on another tab. I will now
investigate
that
form
and try to discover what it is that is affecting the other
forms. I
will
come
back to you when I have something tangible to tell you. In the
meantime,
do
you check your threads or have them on autonotify? I value your
help
and
advice immensely and do not want to lose the connection on this
particualr
problem.

regards

Eric


:

Hi Eric

I have a hunch it may have something to do with the properties
of
the
'frm_Road_Junctions' form, but I have no idea what properties
could
be
causing it. Does that form have any filters or any other
unusual
settings?

As a test, could you please try creating a new form based on
your
Road_Junctions table and insert that into the subform control
instead?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

I removed the old subform, and created a new one. I even
used the
wizard
(this time) to creat the form. It defaulted to datasheet
(which
is
fine
for
now), I didn't change any of the forms settings, apart from
master/child
relationship, and this form behaves the exact same way, it
only
shows
the
record change when I click on it. I don't think I have a
corrupt
database,
everything else works fine, and it's worth pointing out that
I
have
another
very similar/identical form setup on another tab in the same
database
using
master/child links to another form control and that works
just
fine.

:

Hi Eric

Are you saying that you see the value in the textbox
changing,
but
the
record in the subform does not change????

If so, this is most irregular! Can you please try doing a
compact/repair
on
the database, and if it still is not working, try creating
a
new,
unbound
form and adding the two subforms and the textbox, as I
described
in
my
NorthWind example.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Hi Graham,

reading through your northwind example. I checked at this
end
and
what
I
have reflects what you have at your end.

Yes, I made the linking textbox visible, and yes the
record
ID's
match
exactly. As I mentioned, it works like clockwork in terms
of
linking
the
correct records; they match perfectly, but I have to
click
into
the
frm_Road_Junctions before it will update.

It's as if the subform only wakes up when I click on
it?...




:

Hmmmm... that's a puzzle.

Just to prove it for myself, I set up the following in
the
NorthWind
database:

1. Create a continuous form frmOrderList, bound to
Orders,
with
fields
bound
to OrderID, CustomerID and OrderDate.

2. Create a standard form frmCustomers, bound to
Customers
showing
all
fields.

3. Create a new, unbound form in design view. Drag onto
it