refresh an open form using records from another open form?

[efandango]

How can I refresh an 'open' form each time I select a record on another open
form?

the forms are: frm_Waypoints (continous) and frm_Road_Junctions (single form)

each time I select a record on frm_Waypoints, I want the form on
frm_Road_Junctions to refresh and reflect the same record details in the 1st
form, frm_Waypoints.

They are data-synchronised with a master and child relationship with the
Link Master field and Link child field on the frm_Road_Junctions set to:

Link Master fields:
Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

Link Child fields: Road_Junction_ID


This relationship works because if I manually go into a record on
frm_Road_Junctions and hit F5, it refreshes and correctly shows the matching
record in the frm_Waypoints form. alternatively, I can acheive the same
result with the 2nd forms filter set to:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

But regardless of what linking method I use, I want to be able to avoid
having to select a text boxt on the 2nd form and hit F5 each time I go down
the list of records.

I tried moving the focus to the 2nd form and using form....requery, but
nothing happens. Ideally I don't even want to move the focus to the 2nd form,
because the records on the 1st form are a combo list, and if I need to change
any them, I don't want the cursor jumoing to the 2nd form, each time I select
the combo.



How would I do this?

 

[Graham Mandeno]

Hi Eric

The fact that you are using Link Master/Child Fields implies that
frm_Road_Junctions is actually in a subform control and is not the main
form. Is this correct?

If so, do you have any other reason for this setup, or is it just to achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved like this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

 

[efandango]

G'day Graham,

frm_Road_Junctions is not a 'real' subform, just another form on the overall
'main form' frm_Runs.

you are correct, the master child setup was simply to achieve synchro
between the two forms. I can also use:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]
in the Filter on 'frm_Road_Junctions' (never quite sure which is
best/appropriate?)

with regard to your code, I placed it verbatim into the 'On Current' event
of the 'frm_Waypoints' form, but it when i click from each record, the
'frm_Road_Junctions' form doesn't change... but as usual, if I go to the
forms record control and hit F5, it does work...

as I stands, I have removed the master/child references, and use the Filter:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

regards

Eric

Hi Eric

The fact that you are using Link Master/Child Fields implies that
frm_Road_Junctions is actually in a subform control and is not the main
form. Is this correct?

If so, do you have any other reason for this setup, or is it just to achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved like this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

How can I refresh an 'open' form each time I select a record on another
open
form?

the forms are: frm_Waypoints (continous) and frm_Road_Junctions (single
form)

each time I select a record on frm_Waypoints, I want the form on
frm_Road_Junctions to refresh and reflect the same record details in the
1st
form, frm_Waypoints.

They are data-synchronised with a master and child relationship with the
Link Master field and Link child field on the frm_Road_Junctions set to:

Link Master fields:
Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

Link Child fields: Road_Junction_ID


This relationship works because if I manually go into a record on
frm_Road_Junctions and hit F5, it refreshes and correctly shows the
matching
record in the frm_Waypoints form. alternatively, I can acheive the same
result with the 2nd forms filter set to:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

But regardless of what linking method I use, I want to be able to avoid
having to select a text boxt on the 2nd form and hit F5 each time I go
down
the list of records.

I tried moving the focus to the 2nd form and using form....requery, but
nothing happens. Ideally I don't even want to move the focus to the 2nd
form,
because the records on the 1st form are a combo list, and if I need to
change
any them, I don't want the cursor jumoing to the 2nd form, each time I
select
the combo.



How would I do this?

 

[efandango]

Graham,

I was puzzled why after implementing your code, nothing happened, it was as
if the code didn't recognise the 'subform' reference, so I looked at it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called: frm_Runs)

this seemed to spark some life into things, but now I get a type mismatch
error, so first things first, am I on the right track when I made a reference
to the main/master form?. If so, I can then look into this type mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it is late here
now, and I have been gnashing my teeth with this issue all day and need
sleep...

regards

Eric

Hi Eric

The fact that you are using Link Master/Child Fields implies that
frm_Road_Junctions is actually in a subform control and is not the main
form. Is this correct?

If so, do you have any other reason for this setup, or is it just to achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved like this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

How can I refresh an 'open' form each time I select a record on another
open
form?

the forms are: frm_Waypoints (continous) and frm_Road_Junctions (single
form)

each time I select a record on frm_Waypoints, I want the form on
frm_Road_Junctions to refresh and reflect the same record details in the
1st
form, frm_Waypoints.

They are data-synchronised with a master and child relationship with the
Link Master field and Link child field on the frm_Road_Junctions set to:

Link Master fields:
Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

Link Child fields: Road_Junction_ID


This relationship works because if I manually go into a record on
frm_Road_Junctions and hit F5, it refreshes and correctly shows the
matching
record in the frm_Waypoints form. alternatively, I can acheive the same
result with the 2nd forms filter set to:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

But regardless of what linking method I use, I want to be able to avoid
having to select a text boxt on the 2nd form and hit F5 each time I go
down
the list of records.

I tried moving the focus to the 2nd form and using form....requery, but
nothing happens. Ideally I don't even want to move the focus to the 2nd
form,
because the records on the 1st form are a combo list, and if I need to
change
any them, I don't want the cursor jumoing to the 2nd form, each time I
select
the combo.



How would I do this?

 

[Graham Mandeno]

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 textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing frm_Road_Junctions and
frm_Waypoints are named sbfJunctions and sbfWaypoints, respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I was puzzled why after implementing your code, nothing happened, it was
as
if the code didn't recognise the 'subform' reference, so I looked at it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called: frm_Runs)

this seemed to spark some life into things, but now I get a type mismatch
error, so first things first, am I on the right track when I made a
reference
to the main/master form?. If so, I can then look into this type mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it is late
here
now, and I have been gnashing my teeth with this issue all day and need
sleep...

regards

Eric

Hi Eric

The fact that you are using Link Master/Child Fields implies that
frm_Road_Junctions is actually in a subform control and is not the main
form. Is this correct?

If so, do you have any other reason for this setup, or is it just to
achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved like this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

How can I refresh an 'open' form each time I select a record on another
open
form?

the forms are: frm_Waypoints (continous) and frm_Road_Junctions (single
form)

each time I select a record on frm_Waypoints, I want the form on
frm_Road_Junctions to refresh and reflect the same record details in
the
1st
form, frm_Waypoints.

They are data-synchronised with a master and child relationship with
the
Link Master field and Link child field on the frm_Road_Junctions set
to:

Link Master fields:
Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

Link Child fields: Road_Junction_ID


This relationship works because if I manually go into a record on
frm_Road_Junctions and hit F5, it refreshes and correctly shows the
matching
record in the frm_Waypoints form. alternatively, I can acheive the same
result with the 2nd forms filter set to:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

But regardless of what linking method I use, I want to be able to avoid
having to select a text boxt on the 2nd form and hit F5 each time I go
down
the list of records.

I tried moving the focus to the 2nd form and using form....requery, but
nothing happens. Ideally I don't even want to move the focus to the 2nd
form,
because the records on the 1st form are a combo list, and if I need to
change
any them, I don't want the cursor jumoing to the 2nd form, each time I
select
the combo.



How would I do this?

 

[AccessVandal via AccessMonster.com]

To help Graham, what is the datatype for Road_Junction_ID†and
“txt_Run_waypoint_ID�

Use the break points while debugging where the error occurs.

I think the error refers to this.

FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID

You may have to use a single quote or double quote if datatype is not an
integer.

 

[efandango]

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 textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing frm_Road_Junctions and
frm_Waypoints are named sbfJunctions and sbfWaypoints, respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I was puzzled why after implementing your code, nothing happened, it was
as
if the code didn't recognise the 'subform' reference, so I looked at it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called: frm_Runs)

this seemed to spark some life into things, but now I get a type mismatch
error, so first things first, am I on the right track when I made a
reference
to the main/master form?. If so, I can then look into this type mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it is late
here
now, and I have been gnashing my teeth with this issue all day and need
sleep...

regards

Eric

Hi Eric

The fact that you are using Link Master/Child Fields implies that
frm_Road_Junctions is actually in a subform control and is not the main
form. Is this correct?

If so, do you have any other reason for this setup, or is it just to
achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved like this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


How can I refresh an 'open' form each time I select a record on another
open
form?

the forms are: frm_Waypoints (continous) and frm_Road_Junctions (single
form)

each time I select a record on frm_Waypoints, I want the form on
frm_Road_Junctions to refresh and reflect the same record details in
the
1st
form, frm_Waypoints.

They are data-synchronised with a master and child relationship with
the
Link Master field and Link child field on the frm_Road_Junctions set
to:

Link Master fields:
Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

Link Child fields: Road_Junction_ID


This relationship works because if I manually go into a record on
frm_Road_Junctions and hit F5, it refreshes and correctly shows the
matching
record in the frm_Waypoints form. alternatively, I can acheive the same
result with the 2nd forms filter set to:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

But regardless of what linking method I use, I want to be able to avoid
having to select a text boxt on the 2nd form and hit F5 each time I go
down
the list of records.

I tried moving the focus to the 2nd form and using form....requery, but
nothing happens. Ideally I don't even want to move the focus to the 2nd
form,
because the records on the 1st form are a combo list, and if I need to
change
any them, I don't want the cursor jumoing to the 2nd form, each time I
select
the combo.



How would I do this?

 

[efandango]

the datatypes are:

Road_Junction_ID: Long Integer
txt_Run_waypoint_ID: Long Integer


But since I removed (see my last post) Graham's OnCurrent code the type
mismatch error has gone.

 

[Graham Mandeno]

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

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
textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing frm_Road_Junctions and
frm_Waypoints are named sbfJunctions and sbfWaypoints, respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I was puzzled why after implementing your code, nothing happened, it
was
as
if the code didn't recognise the 'subform' reference, so I looked at it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called: frm_Runs)

this seemed to spark some life into things, but now I get a type
mismatch
error, so first things first, am I on the right track when I made a
reference
to the main/master form?. If so, I can then look into this type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it is late
here
now, and I have been gnashing my teeth with this issue all day and need
sleep...

regards

Eric





:

Hi Eric

The fact that you are using Link Master/Child Fields implies that
frm_Road_Junctions is actually in a subform control and is not the
main
form. Is this correct?

If so, do you have any other reason for this setup, or is it just to
achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved like
this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


How can I refresh an 'open' form each time I select a record on
another
open
form?

the forms are: frm_Waypoints (continous) and frm_Road_Junctions
(single
form)

each time I select a record on frm_Waypoints, I want the form on
frm_Road_Junctions to refresh and reflect the same record details in
the
1st
form, frm_Waypoints.

They are data-synchronised with a master and child relationship with
the
Link Master field and Link child field on the frm_Road_Junctions set
to:

Link Master fields:
Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

Link Child fields: Road_Junction_ID


This relationship works because if I manually go into a record on
frm_Road_Junctions and hit F5, it refreshes and correctly shows the
matching
record in the frm_Waypoints form. alternatively, I can acheive the
same
result with the 2nd forms filter set to:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

But regardless of what linking method I use, I want to be able to
avoid
having to select a text boxt on the 2nd form and hit F5 each time I
go
down
the list of records.

I tried moving the focus to the 2nd form and using form....requery,
but
nothing happens. Ideally I don't even want to move the focus to the
2nd
form,
because the records on the 1st form are a combo list, and if I need
to
change
any them, I don't want the cursor jumoing to the 2nd form, each time
I
select
the combo.



How would I do this?

 

[efandango]

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

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
textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing frm_Road_Junctions and
frm_Waypoints are named sbfJunctions and sbfWaypoints, respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham,

I was puzzled why after implementing your code, nothing happened, it
was
as
if the code didn't recognise the 'subform' reference, so I looked at it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called: frm_Runs)

this seemed to spark some life into things, but now I get a type
mismatch
error, so first things first, am I on the right track when I made a
reference
to the main/master form?. If so, I can then look into this type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it is late
here
now, and I have been gnashing my teeth with this issue all day and need
sleep...

regards

Eric





:

Hi Eric

The fact that you are using Link Master/Child Fields implies that
frm_Road_Junctions is actually in a subform control and is not the
main
form. Is this correct?

If so, do you have any other reason for this setup, or is it just to
achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved like
this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


How can I refresh an 'open' form each time I select a record on
another
open
form?

the forms are: frm_Waypoints (continous) and frm_Road_Junctions
(single
form)

each time I select a record on frm_Waypoints, I want the form on
frm_Road_Junctions to refresh and reflect the same record details in
the
1st
form, frm_Waypoints.

They are data-synchronised with a master and child relationship with
the
Link Master field and Link child field on the frm_Road_Junctions set
to:

Link Master fields:
Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

Link Child fields: Road_Junction_ID


This relationship works because if I manually go into a record on
frm_Road_Junctions and hit F5, it refreshes and correctly shows the
matching
record in the frm_Waypoints form. alternatively, I can acheive the
same
result with the 2nd forms filter set to:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

But regardless of what linking method I use, I want to be able to
avoid
having to select a text boxt on the 2nd form and hit F5 each time I
go
down
the list of records.

I tried moving the focus to the 2nd form and using form....requery,
but
nothing happens. Ideally I don't even want to move the focus to the
2nd
form,
because the records on the 1st form are a combo list, and if I need
to
change
any them, I don't want the cursor jumoing to the 2nd form, each time
I
select
the combo.



How would I do this?

 

[Graham Mandeno]

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

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

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
textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing frm_Road_Junctions
and
frm_Waypoints are named sbfJunctions and sbfWaypoints, respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham,

I was puzzled why after implementing your code, nothing happened, it
was
as
if the code didn't recognise the 'subform' reference, so I looked at
it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called: frm_Runs)

this seemed to spark some life into things, but now I get a type
mismatch
error, so first things first, am I on the right track when I made a
reference
to the main/master form?. If so, I can then look into this type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it is
late
here
now, and I have been gnashing my teeth with this issue all day and
need
sleep...

regards

Eric





:

Hi Eric

The fact that you are using Link Master/Child Fields implies that
frm_Road_Junctions is actually in a subform control and is not the
main
form. Is this correct?

If so, do you have any other reason for this setup, or is it just
to
achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved like
this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


How can I refresh an 'open' form each time I select a record on
another
open
form?

the forms are: frm_Waypoints (continous) and frm_Road_Junctions
(single
form)

each time I select a record on frm_Waypoints, I want the form on
frm_Road_Junctions to refresh and reflect the same record details
in
the
1st
form, frm_Waypoints.

They are data-synchronised with a master and child relationship
with
the
Link Master field and Link child field on the frm_Road_Junctions
set
to:

Link Master fields:
Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

Link Child fields: Road_Junction_ID


This relationship works because if I manually go into a record on
frm_Road_Junctions and hit F5, it refreshes and correctly shows
the
matching
record in the frm_Waypoints form. alternatively, I can acheive
the
same
result with the 2nd forms filter set to:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

But regardless of what linking method I use, I want to be able to
avoid
having to select a text boxt on the 2nd form and hit F5 each time
I
go
down
the list of records.

I tried moving the focus to the 2nd form and using
form....requery,
but
nothing happens. Ideally I don't even want to move the focus to
the
2nd
form,
because the records on the 1st form are a combo list, and if I
need
to
change
any them, I don't want the cursor jumoing to the 2nd form, each
time
I
select
the combo.



How would I do this?

 

[efandango]

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

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

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
textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing frm_Road_Junctions
and
frm_Waypoints are named sbfJunctions and sbfWaypoints, respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham,

I was puzzled why after implementing your code, nothing happened, it
was
as
if the code didn't recognise the 'subform' reference, so I looked at
it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called: frm_Runs)

this seemed to spark some life into things, but now I get a type
mismatch
error, so first things first, am I on the right track when I made a
reference
to the main/master form?. If so, I can then look into this type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it is
late
here
now, and I have been gnashing my teeth with this issue all day and
need
sleep...

regards

Eric





:

Hi Eric

The fact that you are using Link Master/Child Fields implies that
frm_Road_Junctions is actually in a subform control and is not the
main
form. Is this correct?

If so, do you have any other reason for this setup, or is it just
to
achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved like
this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


How can I refresh an 'open' form each time I select a record on
another
open
form?

the forms are: frm_Waypoints (continous) and frm_Road_Junctions
(single
form)

each time I select a record on frm_Waypoints, I want the form on
frm_Road_Junctions to refresh and reflect the same record details
in
the
1st
form, frm_Waypoints.

They are data-synchronised with a master and child relationship
with
the
Link Master field and Link child field on the frm_Road_Junctions
set
to:

Link Master fields:
Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

Link Child fields: Road_Junction_ID


This relationship works because if I manually go into a record on
frm_Road_Junctions and hit F5, it refreshes and correctly shows
the
matching
record in the frm_Waypoints form. alternatively, I can acheive
the
same
result with the 2nd forms filter set to:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

But regardless of what linking method I use, I want to be able to
avoid
having to select a text boxt on the 2nd form and hit F5 each time
I
go
down
the list of records.

I tried moving the focus to the 2nd form and using
form....requery,
but
nothing happens. Ideally I don't even want to move the focus to
the
2nd
form,
because the records on the 1st form are a combo list, and if I
need
to
change
any them, I don't want the cursor jumoing to the 2nd form, each
time
I
select
the combo.



How would I do this?

 

[Graham Mandeno]

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

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

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
textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing
frm_Road_Junctions
and
frm_Waypoints are named sbfJunctions and sbfWaypoints,
respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham,

I was puzzled why after implementing your code, nothing happened,
it
was
as
if the code didn't recognise the 'subform' reference, so I looked
at
it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called:
frm_Runs)

this seemed to spark some life into things, but now I get a type
mismatch
error, so first things first, am I on the right track when I made
a
reference
to the main/master form?. If so, I can then look into this type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it is
late
here
now, and I have been gnashing my teeth with this issue all day
and
need
sleep...

regards

Eric





:

Hi Eric

The fact that you are using Link Master/Child Fields implies
that
frm_Road_Junctions is actually in a subform control and is not
the
main
form. Is this correct?

If so, do you have any other reason for this setup, or is it
just
to
achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved
like
this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


message
How can I refresh an 'open' form each time I select a record
on
another
open
form?

the forms are: frm_Waypoints (continous) and
frm_Road_Junctions
(single
form)

each time I select a record on frm_Waypoints, I want the form
on
frm_Road_Junctions to refresh and reflect the same record
details
in
the
1st
form, frm_Waypoints.

They are data-synchronised with a master and child
relationship
with
the
Link Master field and Link child field on the
frm_Road_Junctions
set
to:

Link Master fields:
Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

Link Child fields: Road_Junction_ID


This relationship works because if I manually go into a record
on
frm_Road_Junctions and hit F5, it refreshes and correctly
shows
the
matching
record in the frm_Waypoints form. alternatively, I can acheive
the
same
result with the 2nd forms filter set to:
Road_Junction_ID=Forms![frm_Runs]![frm_Waypoints].Form![txt_Run_waypoint_ID]

But regardless of what linking method I use, I want to be able
to
avoid
having to select a text boxt on the 2nd form and hit F5 each
time
I
go
down
the list of records.

I tried moving the focus to the 2nd form and using
form....requery,
but
nothing happens. Ideally I don't even want to move the focus
to
the
2nd
form,
because the records on the 1st form are a combo list, and if I
need
to
change
any them, I don't want the cursor jumoing to the 2nd form,
each
time
I
select
the combo.



How would I do this?

 

[efandango]

Hi Graham,

Yes, the value in the textbox on the main/master form (frm_Runs) changes
correctly. The values on the subform also change correctly, but I only see
the effect of this when I click any control in the subform form. If I don't
click any controls, or the form itself, I see no changes.

I will rebuild the subform from scratch with the various relationships you
suggest and get back to you.

In the meantime, I don't know if this is directly related, but something
else that is odd occurs when I first open my database main form. Unless I
move beyond the first main record, I am unable to change/ammend any exisitng
records. But if I do move to the 2nd record, then I can change anythiing. It
is as if the records are locked until I wake them up by inititiating a next
record process.

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

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

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
textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing
frm_Road_Junctions
and
frm_Waypoints are named sbfJunctions and sbfWaypoints,
respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham,

I was puzzled why after implementing your code, nothing happened,
it
was
as
if the code didn't recognise the 'subform' reference, so I looked
at
it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called:
frm_Runs)

this seemed to spark some life into things, but now I get a type
mismatch
error, so first things first, am I on the right track when I made
a
reference
to the main/master form?. If so, I can then look into this type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it is
late
here
now, and I have been gnashing my teeth with this issue all day
and
need
sleep...

regards

Eric





:

Hi Eric

The fact that you are using Link Master/Child Fields implies
that
frm_Road_Junctions is actually in a subform control and is not
the
main
form. Is this correct?

If so, do you have any other reason for this setup, or is it
just
to
achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved
like
this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


message
How can I refresh an 'open' form each time I select a record
on
another
open
form?

 

[efandango]

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

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

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
textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing
frm_Road_Junctions
and
frm_Waypoints are named sbfJunctions and sbfWaypoints,
respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham,

I was puzzled why after implementing your code, nothing happened,
it
was
as
if the code didn't recognise the 'subform' reference, so I looked
at
it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called:
frm_Runs)

this seemed to spark some life into things, but now I get a type
mismatch
error, so first things first, am I on the right track when I made
a
reference
to the main/master form?. If so, I can then look into this type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it is
late
here
now, and I have been gnashing my teeth with this issue all day
and
need
sleep...

regards

Eric





:

Hi Eric

The fact that you are using Link Master/Child Fields implies
that
frm_Road_Junctions is actually in a subform control and is not
the
main
form. Is this correct?

If so, do you have any other reason for this setup, or is it
just
to
achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved
like
this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" & Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


message
How can I refresh an 'open' form each time I select a record
on
another
open
form?

 

[Graham Mandeno]

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

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

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
textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing
frm_Road_Junctions
and
frm_Waypoints are named sbfJunctions and sbfWaypoints,
respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


message
Graham,

I was puzzled why after implementing your code, nothing
happened,
it
was
as
if the code didn't recognise the 'subform' reference, so I
looked
at
it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called:
frm_Runs)

this seemed to spark some life into things, but now I get a
type
mismatch
error, so first things first, am I on the right track when I
made
a
reference
to the main/master form?. If so, I can then look into this
type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it
is
late
here
now, and I have been gnashing my teeth with this issue all day
and
need
sleep...

regards

Eric





:

Hi Eric

The fact that you are using Link Master/Child Fields implies
that
frm_Road_Junctions is actually in a subform control and is
not
the
main
form. Is this correct?

If so, do you have any other reason for this setup, or is it
just
to
achieve
the synchronisation between the two forms?

Simple synchronisation between two open forms can be achieved
like
this:

In frm_Waypoints:

Private Sub Form_Current()
Dim frmRJ as Form
On Error GoTo ProcErr
Set frmRJ = Forms!frm_Road_Junctions
With frmRJ.RecordsetClone
.FindFirst "Road_Junction_ID=" &
Me.txt_Run_waypoint_ID
If Not .NoMatch Then
frmRJ.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err.Number <> 2450 Then ' ignore form not open
MsgBox Err.Description, vbExclamation
End If
Resume ProcEnd
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


message
How can I refresh an 'open' form each time I select a
record
on
another
open
form?

 

[efandango]

Hi Graham,

I did that; as mentioned in my last post, I created a new form and have not
changed any of the properties, except for the Master/Child relationship.

The odd thing is though, If I click a required record on the continous form:
frm_Waypoints and then hit F9, then the subform frm_Road_Junctions does
update and shows the correct matching record... so the record matching
'engine' is working, just the weird non-instant update that is broken.

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

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

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
textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing
frm_Road_Junctions
and
frm_Waypoints are named sbfJunctions and sbfWaypoints,
respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


message
Graham,

I was puzzled why after implementing your code, nothing
happened,
it
was
as
if the code didn't recognise the 'subform' reference, so I
looked
at
it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called:
frm_Runs)

this seemed to spark some life into things, but now I get a
type
mismatch
error, so first things first, am I on the right track when I
made
a
reference
to the main/master form?. If so, I can then look into this
type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it
is
late
here
now, and I have been gnashing my teeth with this issue all day
and
need
sleep...

regards

Eric

 

[efandango]

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

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

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
textbox
(hidden) on your main form - let's name it txtCurrentWaypoint.

Also, let's assume the subform controls containing
frm_Road_Junctions
and
frm_Waypoints are named sbfJunctions and sbfWaypoints,
respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


message
Graham,

I was puzzled why after implementing your code, nothing
happened,
it
was
as
if the code didn't recognise the 'subform' reference, so I
looked
at
it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called:
frm_Runs)

this seemed to spark some life into things, but now I get a
type
mismatch
error, so first things first, am I on the right track when I
made
a
reference
to the main/master form?. If so, I can then look into this
type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as it
is
late
here
now, and I have been gnashing my teeth with this issue all day
and
need
sleep...

regards

Eric

 

[Graham Mandeno]

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

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
textbox
(hidden) on your main form - let's name it
txtCurrentWaypoint.

Also, let's assume the subform controls containing
frm_Road_Junctions
and
frm_Waypoints are named sbfJunctions and sbfWaypoints,
respectively.

Set the ControlSource of txtCurrentWaypoint to:
=[sbfWayPoints].Form![txt_Run_waypoint_ID]

Now, set the master/child links for sbfJunctions:
LinkMasterFields: txtCurrentWaypoint
LinkChildFields: Road_Junction_ID

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


message
Graham,

I was puzzled why after implementing your code, nothing
happened,
it
was
as
if the code didn't recognise the 'subform' reference, so I
looked
at
it
again, and replaced:

Set frmRJ = Forms!frm_Road_Junctions

with

Set frmRJ = Forms![frm_Runs]![frm_Road_Junctions]

(both forms in question are on a main/master form called:
frm_Runs)

this seemed to spark some life into things, but now I get a
type
mismatch
error, so first things first, am I on the right track when
I
made
a
reference
to the main/master form?. If so, I can then look into this
type
mismatch
problem at my end, and hopefully resolve it.

If not, can I come back to you on this problem tomorrow, as
it
is
late
here
now, and I have been gnashing my teeth with this issue all
day
and
need
sleep...

regards

Eric

 

[efandango]

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

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