Solution - Experienced Challenge 5: I’m Leaving on a Jet Plane
We have to admit that Challenge 5 took us a little by surprise: it turned out to be easier than we anticipated. (As you might expect, things almost always turn out to be harder than we expect.) Even we knew that a simple SQL query could return a collection of all the non-stop flights from City A to City B and we were pretty sure that another SQL query could return a collection of all the single-stop flights between the two cities. What we weren’t sure of was just how complicated that second SQL query was going to be. The final verdict? Not too complicated at all. (A little cryptic looking, but not too complicated.)
Here’s the solution we came up with for Challenge 5:
Private Sub Command5_Click()
Set objDatabase = CurrentDb()
Set objRecordset = objDatabase.OpenRecordset("SELECT * FROM FlightInformation " & _
"WHERE DepartureCity = '" & Combo1.Value & "' " & _
"AND ArrivalCity = '" & Combo3.Value & "';")
Text6.Value = "Non-stop flights:" & vbCrLf & vbCrLf
If objRecordset.RecordCount > 0 Then
Do Until objRecordset.EOF
strValue = strValue & objRecordset.Fields("DepartureCity") & " to " _
& objRecordset.Fields("ArrivalCity") & vbCrLf _
& "Travel time: " & Format(objRecordset.Fields("TravelTime"), "h:nn") _
& vbCrLf & vbCrLf
objRecordset.MoveNext
Loop
Text6.Value = Text6.Value & strValue
Else
Text6.Value = Text6.Value & "There are no non-stop flights from " & Combo1.Value & " to " & _
Combo3.Value & "." & vbCrLf & vbCrLf
End If
objRecordset.Close
strValue = ""
Text6.Value = Text6.Value & "One-stop flights:" & vbCrLf & vbCrLf
Set objRecordset = objDatabase.OpenRecordset _
("SELECT a.ArrivalCity AS Stop3, b.ArrivalCity AS Stop2, " _
& "b.DepartureCity AS Stop1, a.TravelTime + b.TravelTime AS TotalTime " _
& "FROM FlightInformation a INNER JOIN FlightInformation b " _
& "ON a.DepartureCity = b.ArrivalCity " _
& "WHERE a.ArrivalCity = '" & Combo1.Value & "' " _
& "AND b.DepartureCity = '" & Combo3.Value & _
"'ORDER BY (a.TravelTime + b.TravelTime);")
Do Until objRecordset.EOF
strValue = strValue & objRecordset.Fields("Stop1") & " to " & _
objRecordset.Fields("Stop2") & vbCrLf & objRecordset.Fields("Stop2") & _
" to " & objRecordset.Fields("Stop3") & vbCrLf & "Travel time: " & _
Format(objRecordset.Fields("TotalTime"), "h:nn") & vbCrLf & vbCrLf
objRecordset.MoveNext
Loop
Text6.Value = Text6.Value & strValue
End Sub
So what does all this code actually mean? We were afraid you were going to ask that. We actually start things off pretty simply; all we do is use this line of code to create an object reference to the current database (that is, the same database where our form resides):
Set objDatabase = CurrentDb()
After that we get right to work, using the following query to retrieve a collection of all the non-stop flights between our departure city (Combo1.Value) and the destination city (Combo3.Value):
Set objRecordset = objDatabase.OpenRecordset("SELECT * FROM FlightInformation " & _
"WHERE DepartureCity = '" & Combo1.Value & "' " & _
"AND ArrivalCity = '" & Combo3.Value & "';")
If you’re even the least bit familiar with SQL then this query shouldn’t be too hard to parse: all we’re doing is selecting all the records from the FlightInformation table where the DepartureCity field is equal to the Value of Combo1 and the ArrivalCity field is equal to the Value of Combo3. Child’s play.
Well, assuming your child is one of those kids who appears in the “I’m a PC” commercials.
After executing the query we use this line of code to write the string value Non-stop flights: (plus a pair of carriage return-linefeeds, represented by the constant vbCrLf) to our text box:
Text6.Value = "Non-stop flights:" & vbCrLf & vbCrLf
As soon as we’ve done that, we’re ready to list all the non-stop flights from City A to City B.
Well, assuming that there are any non-stop flights from City A to City B; needless to say, there could be times when you can’t get there from here. Consequently, the first thing we do is check to see if there is at least one record in our recordset:
If objRecordset.RecordCount > 0 Then
If the value of the RecordCount property is equal to 0 that means there are no non-stop flights between City A and City B; in turn, we then add a note to that effect to the text box:
Text6.Value = Text6.Value & "There are no non-stop flights from " & Combo1.Value & " to " & _
Combo3.Value & "." & vbCrLf & vbCrLf
But what if there is at least one record in the recordset? In that case, we execute the following block of code:
Do Until objRecordset.EOF
strValue = strValue & objRecordset.Fields("DepartureCity") & " to " _
& objRecordset.Fields("ArrivalCity") & vbCrLf _
& "Travel time: " & Format(objRecordset.Fields("TravelTime"), "h:nn") _
& vbCrLf & vbCrLf
objRecordset.MoveNext
Loop
Text6.Value = Text6.Value & strValue
All we’re doing here is setting up a Do Until loop that runs until we reach the end of the recordset. (Or, if you want to put a more technical spin on things, until the recordset’s EOF property – end-of-file property – is True.) Inside that loop, we put together a string consisting of the value of the DepartureCity field, the value of the ArrivalCity field, and the value of the TravelTime field. (Because the latter field represents time, format the value using the h:nn – hours:minutes – format.) This string that we compose is then assigned to a variable named strValue.
From there we call the MoveNext method to move to the next record in the recordset.
Helpful hint: Don’t leave this method out; if you do, your subroutine will get caught in an endless loop.
Or so we’ve been , uh … told ….
We then use this line of code to append the value of strValue to the text box:
Text6.Value = Text6.Value & strValue
What does all that mean? Well, if we had a departure city of Los Angeles and a destination city of Minneapolis that means our text box will look like this:
Non-stop flights:
Los Angeles to Minneapolis
Travel time: 3:09
Pretty exciting, huh?
But wait: you ain’t seen nothin’ yet. After taking care of the non-stop flights our next chore is to figure out all the single-stop flights from City A to City B. Before we get into the nitty-gritty details of doing that, we first run these three lines of code:
objRecordset.Close
strValue = ""
Text6.Value = Text6.Value & "One-stop flights:" & vbCrLf & vbCrLf
Needless to say, there’s nothing very special going on here. In the first line we close our recordset; that will enable us to reuse the same object reference (objRecordset) when we execute query No. 2. In line 2 we set strValue to an empty string, and in line 3 we add the heading One-stop flights to the text box.
Yes, we know: pretty ho-hum. But – and we don’t mean to shock you here – sometimes life is a little ho-hum.
At least for some of us.
That brings us to this scary-looking hunk of code:
Set objRecordset = objDatabase.OpenRecordset _
("SELECT a.ArrivalCity AS Stop3, b.ArrivalCity AS Stop2, " _
& "b.DepartureCity AS Stop1, a.TravelTime + b.TravelTime AS TotalTime " _
& "FROM FlightInformation a INNER JOIN FlightInformation b " _
& "ON a.DepartureCity = b.ArrivalCity " _
& "WHERE a.ArrivalCity = '" & Combo1.Value & "' " _
& "AND b.DepartureCity = '" & Combo3.Value & _
"'ORDER BY (a.TravelTime + b.TravelTime);")
So should you be scared of this code? Not really; its bark is far worse than its bite. What we’re doing here is using an Inner Join query that joins the table FlightInformation to, well, the table FlightInformation. (This is also known as a “self-join” query, because the table is joined to itself.)
Admittedly, that all sounds a little weird (to say the least), but this construction essentially allows us to retrieve some information from the table (e.g., all the cities you can fly to from City A), then use that retrieved information to extract additional data from the table (e.g., all the cities you can fly to from those cities you can fly to from City A). For example, if we take off from Minneapolis we can fly non-stop to the following cities:
· Los Angeles
· Detroit
· St. Louis
· Dallas
· New York
· Cleveland
And now – just to pick one – let’s see what cities we can fly to from Dallas:
· Los Angeles
· Denver
· Detroit
· St. Louis
· Houston
· Cleveland
· Minneapolis
One of the cities we can fly to when we take off in Dallas is Houston. So what does that mean? That means that there’s at least one way we can get from Minneapolis to Houston: we can fly to Dallas, change planes, and then fly to Houston.
That’s the kind of information our query returns.
As for how the query is actually constructed, let’s take a peek at one portion of the thing:
SELECT a.ArrivalCity AS Stop3, b.ArrivalCity AS Stop2
As you can see, we don’t just refer to the field name (e.g., ArrivalCity); instead, we reference the table instance (a) and the field name: a.ArrivalCity. As far as we know, you must always use this same approach: the first instance of the table is table a, the second instance of the table is table b, and so on. When we actually join the two tables you’ll see references to FlightInformation a and FlightInformation b:
FROM FlightInformation a INNER JOIN FlightInformation b
Notice, too that we give our fields “aliases;” for example, we give a.ArrivalCity the alias Stop3. Later on in the code, these aliases will be used when we report the value retrieved by the query. For example, any instance of a.ArrivalCity will be referenced using the alias Stop3:
objRecordset.Fields("Stop3")
Finally, we loop through the returned recordset, add the retrieved information to the variable strValue, and then append strValue to our text box. That’s what this section of code is for:
Do Until objRecordset.EOF
strValue = strValue & objRecordset.Fields("Stop1") & " to " & _
objRecordset.Fields("Stop2") & vbCrLf & objRecordset.Fields("Stop2") & _
" to " & objRecordset.Fields("Stop3") & vbCrLf & "Travel time: " & _
Format(objRecordset.Fields("TotalTime"), "h:nn") & vbCrLf & vbCrLf
objRecordset.MoveNext
Loop
Text6.Value = Text6.Value & strValue
And if we chose to check flights from Cleveland to St. Louis (which is what we did choose when we tested the submissions) our text box should end up looking like this:
Non-stop flights:
There are no non-stop flights from Cleveland to St. Louis.
One-stop flights:
St. Louis to Chicago
Chicago to Cleveland
Travel time: 1:35
St. Louis to New York
New York to Cleveland
Travel time: 2:57
St. Louis to Dallas
Dallas to Cleveland
Travel time: 3:27
St. Louis to Boston
Boston to Cleveland
Travel time: 3:32
And to think some people thought this was going to be hard!