Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Feb 2010
    Location
    Essex, UK
    Posts
    48
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Some queries prevent yes/no box being changed

    Hi.

    I have a database of routes for spectators going to events, and I have a form which allows you to see the impact on those routes of events on the transport network. I have a subform which displays a datasheet that allows you to see routes and alternatives, with a yes/no box that you can select to say that a particular route is the currently recommended one.

    To help me, I have buttons to further filter the list of routes, so that all you see is the affected set of routes and their alternatives, rather than the whole lot.

    Essentially the ROWSOURCE is set in VBA by two parameters - ShowAffectedOnly and IncludeAvoid. My VBA looks like this:

    Dim F As Form

    ' Show All routes, include avoid routes
    If Not ShowAffectedOnly And IncludeAvoid Then
    Set F = Spectator_Routes_subform.Form
    F.RecordSource = "SELECT Spectator_Routes.VenueID, Spectator_Routes.FromStation, Spectator_Routes.ToStation, Spectator_Routes.Priority, " & _
    "Spectator_Routes.[Route Active], Spectator_Routes.[Route Blocked], Spectator_Routes.Link1, Spectator_Routes.Link2, " & _
    "Spectator_Routes.Link3, Spectator_Routes.Link4, Spectator_Routes.Link5 " & _
    "FROM Spectator_Routes WHERE Spectator_Routes.VenueID IN (SELECT DISTINCT Venues.VenueID FROM Venues WHERE Venues.Active=Yes) ORDER BY 1,2,4;"
    F.Requery
    SpecRt_Label.Caption = "All Spectator Routes for Active Venues"

    ' Show All routes, do not include avoid routes
    ElseIf Not ShowAffectedOnly And Not IncludeAvoid Then
    Set F = Spectator_Routes_subform.Form
    F.RecordSource = "SELECT Spectator_Routes.VenueID, Spectator_Routes.FromStation, Spectator_Routes.ToStation, Spectator_Routes.Priority, " & _
    "Spectator_Routes.[Route Active], Spectator_Routes.[Route Blocked], Spectator_Routes.Link1, Spectator_Routes.Link2, " & _
    "Spectator_Routes.Link3, Spectator_Routes.Link4, Spectator_Routes.Link5 " & _
    "FROM Spectator_Routes WHERE (Spectator_Routes.VenueID IN (SELECT DISTINCT Venues.VenueID FROM Venues WHERE Venues.Active=Yes)) " & _
    "AND Spectator_Routes.Priority<>0 ORDER BY 1,2,4;"
    F.Requery
    SpecRt_Label.Caption = "All Spectator Routes for Active Venues"

    ' Show Affected routes only, include avoid routes
    ElseIf ShowAffectedOnly And IncludeAvoid Then
    Set F = Spectator_Routes_subform.Form
    F.RecordSource = "SELECT Spectator_Routes.VenueID, Spectator_Routes.FromStation, Spectator_Routes.ToStation, Spectator_Routes.Priority, " & _
    "Spectator_Routes.[Route Active], Spectator_Routes.[Route Blocked], " & _
    "Spectator_Routes.Link1, Spectator_Routes.Link2, Spectator_Routes.Link3, Spectator_Routes.Link4, Spectator_Routes.Link5 " & _
    "FROM Spectator_Routes,(SELECT DISTINCT Spectator_Routes.VenueID, Spectator_Routes.FromStation FROM Spectator_Routes WHERE Spectator_Routes.[Route Blocked]=Yes AND Spectator_Routes.[Route Active]=Yes) AS AfctRt " & _
    "WHERE (Spectator_Routes.VenueID=AfctRt.VenueID AND Spectator_Routes.FromStation=AfctRt.FromStation AND (Spectator_Routes.VenueID IN (SELECT DISTINCT Venues.VenueID FROM Venues WHERE Venues.Active=Yes))) ORDER BY 1,2,4;"
    F.Requery
    SpecRt_Label.Caption = "Affected Spectator Routes for Active Venues"

    ' Show Affected routes only, do not include avoid routes
    ElseIf ShowAffectedOnly And Not IncludeAvoid Then
    Set F = Spectator_Routes_subform.Form
    F.RecordSource = "SELECT Spectator_Routes.VenueID, Spectator_Routes.FromStation, Spectator_Routes.ToStation, Spectator_Routes.Priority, " & _
    "Spectator_Routes.[Route Active], Spectator_Routes.[Route Blocked], " & _
    "Spectator_Routes.Link1, Spectator_Routes.Link2, Spectator_Routes.Link3, Spectator_Routes.Link4, Spectator_Routes.Link5 " & _
    "FROM Spectator_Routes,(SELECT DISTINCT Spectator_Routes.VenueID, Spectator_Routes.FromStation FROM Spectator_Routes WHERE Spectator_Routes.[Route Blocked]=Yes AND Spectator_Routes.[Route Active]=Yes) AS AfctRt " & _
    "WHERE (Spectator_Routes.VenueID=AfctRt.VenueID AND Spectator_Routes.FromStation=AfctRt.FromStation AND (Spectator_Routes.VenueID IN (SELECT DISTINCT Venues.VenueID FROM Venues WHERE Venues.Active=Yes)) AND Spectator_Routes.Priority<>0) " & _
    "ORDER BY 1,2,4;"
    F.Requery
    SpecRt_Label.Caption = "Affected Spectator Routes for Active Venues"

    End If
    My problem is that the first two options (showing all the routes) allow me to change the yes/no tick box values, while the last two do not. It is very frustrating, since the whole point of the ShowAffectedRoutes option is to allow a simpler list - at present, all you can do is say "yeah" and then go back to the full list in order to make the change.

    Any thoughts? Thanks.

    Stuart

  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
    Those to queries don't properly JOIN Spectator_Routes with AfctRt. What you have is a Cartesian Join (which Joins all records of 1 table with all records of the other table), and I don't think that is updateable. However, even if Joined properly, it still may not be updateable. I can't quite figure out what you are trying to do, so I can't make any suggestions.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    It is, indeed, the nature of the last two queries that doesn't allow them to be updateable. They seem rather convoluted to me, although it is hard to be definitive about it, since I don't know your tables structure. Isn't there an easier way to select the routes that obey certain conditions without the need to join the table with itself?

Posting Permissions

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