Match multiple field records to any sub records. (complex....)

[efandango]

Apologies for the possibility of a misleading title, but I am struggling to
explain this in one short sentence, but I think this long standing problem is
affecting my sanity...

I have a continuous subform (linked to a master table of records) with a
number record sets made up of unique entries called Waypoints (street names)
and another continuous Form called Road Restrictions which contain various
pairs of street names that intersect as junctions with the street names on
the Waypoints form.

I want to be able to link the Road Restrictions form entries to the
occurring Waypoints entries, so that whenever I go to a new page of Waypoints
a corresponding list of Road Restrictions is presented on the Road
Restrictions form.

The problem is that because the Road Restrictions entries (from & To) are 2
separate fields, I have no way of linking them with a common unique ID to the
unique entry on the Waypoint form. I tried using Run_waypoint_ID, but that
will only link one of the two Road field entries back to the Run_waypoint
field, and it may not be the one that actually exists. I need some way of
saying. If either, or both of these two entries exist in the ‘master’
Waypoint recordset, then display them in the Road Restrictions list.

Below is an example of what I want to achieve.
Frm_Waypoint Form:
Main Street
South Street
Park Avenue
Brighton Street
Back Lane
Fuller Street
Garden Row
Tracy Street
Bleaker Street
Trent Street
West Avenue
Cyprus Mount


frm_Road Restrictions:
Road_Name_From Road_Name_To

Brighton Street Green St
Blue St Bleaker St
Back Lane West Avenue



I have tried using a query, using the Waypoint name with the 'From & To'
fields with a criteria like this:
SELECT tbl_Road_Restrictions.Road_Name_From,
tbl_Road_Restrictions.Road_Name_To, tbl_Road_Restrictions.Run_waypoint_ID
FROM tbl_Road_Restrictions
WHERE
(((tbl_Road_Restrictions.Road_Name_From)=[Forms]![frm_Runs]![frm_Waypoints].[Form]![Run_waypoint]))
OR
(((tbl_Road_Restrictions.Road_Name_To)=[Forms]![frm_Runs]![frm_Waypoints].[Form]![Run_waypoint]));


But it will only present a matching list of the first occurring Waypoint in
the recordset; if I have any other Waypoints that match a field in the Road
Restrictions form, it doesn’t show them in that form.


This is what the two table structures are:

Table: tbl_Waypoints
Field Type
Run_No (linked to master tbl_Runs)
Run_Waypoint_Auto_ID Autonumber
Run_waypoint_ID Unique ID
Run_waypoint Text
Postcode Text
OrderSeq Long Integer
Restriction_Flag Yes/No


Table: tbl_Road_Restrictions
Field Type
Road_Restriction_ID Autonumber
Run_waypoint_ID Long Integer
Road_Name_From Text
Postcode_From Text
Road_Name_To Text
Postcode_To Text
Restriction Text

 

[Michael Gramelspacher]

I want to be able to link the Road Restrictions form entries to the
occurring Waypoints entries, so that whenever I go to a new page of Waypoints
a corresponding list of Road Restrictions is presented on the Road
Restrictions form.

Admittedly I do not understand for sure what you are doing, but try creating a
textbox (should not be visible) on the main form, which has
=frm_Waypoints.Form.run_waypoint_id as its control source. Name it
txt_run_waypoint_id. Then link the main form to the restriction subform by link
master/link child fields txt_run_waypoint_id as master and run_waypoint_id as
child.