If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Match multiple field records to any sub records. (complex....)
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 a 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 |
#2
|
|||
|
|||
Match multiple field records to any sub records. (complex....)
On Sat, 12 Jul 2008 02:29:03 -0700, efandango
wrote: 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. |
Thread Tools | |
Display Modes | |
|
|