Re: refresh an open form using records from another open form?

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?...




"Graham Mandeno" wrote:

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

"efandango" <efandango@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5AA73AAB-7DB6-4A81-AFCE-BFAF760BFE6A@xxxxxxxxxxxxxxxx
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?...



"Graham Mandeno" wrote:

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

"efandango" <efandango@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:168CF735-2035-4B5F-AD37-40768100B80B@xxxxxxxxxxxxxxxx
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.


"Graham Mandeno" wrote:

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


"efandango" <efandango@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:50C2A0CF-307C-4A74-B9B3-12A8FA4B50F0@xxxxxxxxxxxxxxxx
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" wrote:

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


"efandango" <efandango@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:279B5891-8A36-498C-8941-60233C686860@xxxxxxxxxxxxxxxx
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?
.

Relevant Pages

  • Re: Lookup record in subform based on combo im main form
    ... Facilities table). ... So I would like a Link Master/Child ... It doesn't give me the option of setting it to the name of the control, ... buttons on the subform. ...
    (microsoft.public.access.formscoding)
  • Re: refresh an open form using records from another open form?
    ... except for the Master/Child relationship. ... Road_Junctions table and insert that into the subform control instead? ... Graham Mandeno ...
    (microsoft.public.access.formscoding)
  • Re: refresh an open form using records from another open form?
    ... Road_Junctions table and insert that into the subform control instead? ... Graham Mandeno ... master/child links to another form control and that works just fine. ...
    (microsoft.public.access.formscoding)
  • Re: Slow Code
    ... value in textbox Text171 on the subform, then set the value of this textbox ... executing within the mainform. ... run the expression in the subform, ... no point using code to hide/unhide the control; ...
    (microsoft.public.access.forms)
  • Re: refresh an open form using records from another open form?
    ... Are you saying that you see the value in the textbox changing, ... It's as if the subform only wakes up when I click on it?... ... I click a control on the 'frm_Road_Junctions' form. ... Did you set up the master/child link for the subform control ...
    (microsoft.public.access.formscoding)