Experienced Challenge 5: I'm Leaving on a Jet Plane

Office Programs Required

Microsoft Access 2007

Goldfish Awarded


Deadline for Submission

Monday, April 27, 2009 (8:00 AM Pacific Daylight Time)

Files Required

Experienced_challenge_5.accdb in the OfficePalooza Challenge Pack.

How do you get from Point A to Point B? To tell you the truth, we have no idea. Tell you what: give us a chance to finish our solution to Challenge 5 and then we’ll get back to you.

Which is our less-than-subtle way of telling you that, in Challenge 5, you need to tell people how they can fly from one city (e.g., Los Angeles) to another city (e.g., Miami). To do this, you’ll need the file Experienced_challenge_5.accdb, a file found in the OfficePalooza Challenge Pack . That file includes a form (Flight Information) that looks like this:

Experienced Challenge 5 

The idea here is that people choose a departure city and a destination city, then click the Show Flights button. When they click the button, the Visual Basic for Applications (VBA) code behind that button will check to see if there are any nonstop flights between the departure city and the destination city. (One-way only; you don’t need to worry about return flights.) If so, that flight – and the total trip time – will be displayed in the text box shown on the form. For example, if you did select Los Angeles and Miami you’d see this in the text box:

Los Angeles to Miami

Travel time: 4:40

But wait, that’s not it. After checking for nonstop flights the code will then search for one stop flights. For example, another way to get from Los Angeles to Miami is to fly from Los Angeles to Chicago and then from Chicago to Miami. That information would be recorded in the text box like this:

Los Angeles to Chicago

Chicago to Miami

Travel time: 7:03

Note. How did we get a travel time of 7:03? That’s 3:32 to fly from LA to Chicago, plus 3:31 to fly from Chicago to LA.

And yes, in this magical fantasy world of ours, you never have to worry about a layover at the airport: you get off one plane and immediately get on the other.

If there are any more single-stop routes from Los Angeles to Miami then those will be added to the text box as well.

So what’s the catch here? The catch is that, at the moment, there isn’t any code behind the Show Flights button. Instead, the subroutine looks like this:

Private Sub Command5_Click()

End Sub

Your task (and you had to know this was coming, didn’t you?) is to add the required code to the Show Flights button. To do that just right-click the button, click Build Event, and then click Code Builder. And after that? Well, after that you’re on your own. But we know that’s exactly the way you’d want it.

Submitting Your Entry

The OfficePalooza sweepstakes is over, but you’re welcome to try the challenges and learn on your own. Good luck!