Some queries prevent yes/no box being changed

Topic

New Reply

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.Priority0 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.Priority0) ” & _
“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

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote