Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Creating a new form in an existing application. The recordsource (incomplete!) for the form is a query involving four tables, that ultimately exposes three fields: RouteID, CityID, and ServiceOnReturnTrip. It works as it exists so far, and the recordset is updateable. SQL is:

    SELECT utblTripCustomerReverseService.TripIDfk, utblTripCustomerReverseService.CustomerIDfk, aTblCities.CityName, aTblCustomer.CustomerName, utblTripCustomerReverseService.ServiceOnReturnTrip , uTblTrip.RouteID, aTblCities.CityID
    FROM uTblTrip INNER JOIN (aTblCities INNER JOIN (aTblCustomer INNER JOIN utblTripCustomerReverseService ON aTblCustomer.CustomerID = utblTripCustomerReverseService.CustomerIDfk) ON aTblCities.CityID = aTblCustomer.CityID) ON uTblTrip.TripID = utblTripCustomerReverseService.TripIDfk
    ORDER BY aTblCities.CityName, aTblCustomer.CustomerName;


    Next step is to add a fifth table (atblCitiesOnRoute) that stores the SortOrder for each city on each route. The table contains fields named CityID and RouteID, and the two fields together comprise the table's primary key. Both fields are Required in the table. When I add the table to the existing query the recordsource becomes non-updateable.

    SELECT utblTripCustomerReverseService.TripIDfk, utblTripCustomerReverseService.CustomerIDfk, aTblCitiesOnRoute.SortOrder, aTblCities.CityName, aTblCustomer.CustomerName, utblTripCustomerReverseService.ServiceOnReturnTrip , uTblTrip.RouteID, aTblCities.CityID
    FROM (uTblTrip INNER JOIN (aTblCities INNER JOIN (aTblCustomer INNER JOIN utblTripCustomerReverseService ON aTblCustomer.CustomerID = utblTripCustomerReverseService.CustomerIDfk) ON aTblCities.CityID = aTblCustomer.CityID) ON uTblTrip.TripID = utblTripCustomerReverseService.TripIDfk) INNER JOIN aTblCitiesOnRoute ON (aTblCities.CityID = aTblCitiesOnRoute.CityID) AND (uTblTrip.RouteID = aTblCitiesOnRoute.RouteID)
    ORDER BY aTblCitiesOnRoute.SortOrder, aTblCustomer.CustomerName;


    I've tried every combination and syntax I can think of to make the recordset updateable, but no success. Bottom line is that I want to sort the recordset by aTblCitiesOnRoute.SortOrder and be able to edit utblTripCustomerReverseService.ServiceOnReturnTrip . Any suggestions welcome.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    The only thing I can suggest is to make this a named query. You can then change the "Recordset Type" query property to "Dynaset (Inconsistent Updates)". I don't really understand it, but it works.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Fantastic! Thanks.

    Turns out that it's not even necessary to save a named query - SQL statement in the form's recordsource is OK. With either method, the form's RecordsetType must be set to Dynaset (Inconsistent Updates). I had never used that property before; learn something new every day!
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •