Form and Field Design

[scsuflyboy2005]

Ok, here's my question:

I am an airport operations person and I have a pretty comprehensive database developed. One thing I can't figure out how to do is this:

I have one form (of course along with a table) for what we call "Flight Tracking". This form allows input of an airline, flight number, scheduled arrival (or departure) time, actual arrival (or departure) time, along with other miscellaneous info.

Here's what I would the like the form to do. It is tedious for us to keep inputting the same scheduled times and airlines, etc.

How do I format the fields in a such a way that if I select a flight number, for example 6375 from the flight number drop down box, it automatically fills in the other related fields, such as Airline name, scheduled arrival or departure time, etc?

Any help would be appreciated

 

[smedvid]

Here is "one" approach that I have use in the past.

1. Create combo-box with a query where the query contains the necessary information. You can set the width of these extreaneous fields to zero in the combo-box to hide them, if desired.
2. On the After Update Event, simply assign the column values to different fields.

Example:
cbo_LoanCommissCategoryID Data Row Source
SELECT LoanCommissCategoryID, CompPlanType, LoanCommissCategoryName FROM tbl_LoanCommissCategory;

On After Update
Private Sub LoanCommissCategoryID_AfterUpdate()
Me.CompPlanType.Value = Me.cbo_LoanCommissCategoryID.Column(1)
End Sub

Note: Column Counts start at Zero. (1) is actually the 2nd column.

htwhm,




Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[DoubleD]

There are several ways to accomplish this.

The preferred method is to use VBA to lookup the data and populate the fields. Something like this code put in the AfterUpdate event of the combo box.

Dim rs as New ADODB.Recordset

rs.Open "SELECT * FROM tblFlightDetail WHERE FlightNum = ", CurrentProject.Connection
Me.txtAirlineName = rs!AirlineName
Me.txtSchedArriveTime = rs!SchedArriveTime
Me.txtSchedDepartTime = rs!SchedDepartTime

set rs = Nothing

This assumes there is a table called tblFlightDetail which contains the following fields:
FlightNumber
AirlineName
SchedArriveTime
SchedDepartTime

And on your form you have the following text boxes:
txtAirlineName
txtSchedArriveTime
txtSchedDepartTime

I am what I am based on the decisions I have made.

DoubleD

 

[DoubleD]

Oops, heres the complete recordset line:

If FlightNum is a numeric field:
rs.Open "SELECT * FROM tblFlightDetail WHERE FlightNum = " & Me.txtFlightNum, CurrentProject.Connection

If FlightNum is a text field:
rs.Open "SELECT * FROM tblFlightDetail WHERE FlightNum = '" & Me.txtFlightNum & "'", CurrentProject.Connection


smedvid's solution will also work very effectively.

I am what I am based on the decisions I have made.

DoubleD

 

[smedvid]

In my case, I was looking at limiting database connections and developing an efficient tool. Since the data was already available in the combobox I was using, I simply referenced that value, as opposed to developing a seperate record set lookup routine. However, if the data is not stored in the cbo field, by all means, use the record set approach.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[scsuflyboy2005]

Ok, I tried the VBA method.

I have a table named tblFlightDetail.

Form has all text fields mentioned above.

When I try to run I get a runtime error.

Here is the code...

Private Sub FlightNumber_AfterUpdate()
Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM tblFlightDetail WHERE FlightNumber = ", CurrentProject.Connection"
Me.TxtAirlineName = rs!AirlineName
Me.txtSchedArriveTime = rs!SchedArriveTime
Me.TxtSchedDepartTime = rs!SchedDepartTime

Set rs = Nothing

What is the problem?


End Sub

 

[DoubleD]

If you're not familiar with VBA and recordsets, I would suggest smedvid's solution. For clarity:

Write a query against tblFlightDetail containing the following fields:
FlightNumber
AirlineName
SchedArriveTime
SchedDepartTime

Change the properties on your combo box to have 4 columns. Column widths should be 1",0",0",0"

Now your text fields should have Control Sources as listed:
txtAirlineName = [cboFlightNum].Column(1)
txtSchedArriveTime = [cboFlightNum].Column(2)
txtSchedDepartTime = [cboFlightNum].Column(3)

If you will be storing this data in a table (which seems unlikely as it would just be duplicate info and break the rules of normalization) then use this code in the AfterUpdate event of the combo box:

Me.txtAirlineName = Me.cboFlightNum.Column(1)
Me.txtSchedArriveTime = Me.cboFlightNum.Column(2)
Me.txtSchedDepartTime = Me.cboFlightNum.Column(3)

I am what I am based on the decisions I have made.

DoubleD

 

[scsuflyboy2005]

Ok, i'm an idiot when it comes to Access (what I know I've learned on my own..which I must say isn't a lot)

I have tried the method above but get a compile error when I try to use the form.

Here's what I have done....

I have created a table called tblFlightDetail. Within this table are all of the flight numbers, airlines, scheduled arrival times, scheduled departure times, etc.

I then created a query based on tblFlightDetail.

Next I created a form with a dropdown box. 4 columns, based on the query. Column widths are 1,0,0,0. The form has the text fields called txtAirline Name, txtSchedArriveTime, and txtSchedDepart Time.

I then have this code on the after update of the combo box:

Private Sub FlightNum_AfterUpdate()
Me.TxtAirlineName = Me.cboFlightNum.Column(1)
Me.txtSchedArriveTime = Me.cboFlightNum.Column(2)
Me.TxtSchedDepartTime = Me.cboFlightNum.Column(3)

End Sub

For reach of the text boxes I have the control source as:
=cboFlightNum.Column(1)
=cboFlightNum.Column(2)
=cboFlightNum.Column(3)

When I try to run the form and select a flight number I get a "Compile Error: Method or data member not found"

What is the problem..This is driving me nuts..

 

[ChuckWhite]

As long as I'm understanding all this correctly ...

With text box control sources set to:
=cboFlightNum.Column(1)
=cboFlightNum.Column(2)
=cboFlightNum.Column(3)

There is no need for the AfterUpdate statements:
Me.TxtAirlineName = Me.cboFlightNum.Column(1)
Me.txtSchedArriveTime = Me.cboFlightNum.Column(2)
Me.TxtSchedDepartTime = Me.cboFlightNum.Column(3)

The statements in your AfterUpdate event are probably causing the compile error as the syntax should be similar to (without checking it):
Me!TxtAirlineName = Me!cboFlightNum.Column(1)
Me!txtSchedArriveTime = Me!cboFlightNum.Column(2)
Me!TxtSchedDepartTime = Me!cboFlightNum.Column(3)

A simple rule that generally works is ... If you named the object follow "Me" with a "!". If VBA provided the object follow "Me" with a "."

 

[willir]

scsuflyboy2005

To do the above, you have to have a schedule defined, perhaps based on the flight number. Your tblflightDetails seems to fit the need for this.

Contrary to ChuckWhite, the AfterUpdate is the appropriate event procedure to use, and the OnCurrent record.

But, the code needs to accommodate if there is no record selected, etc. And, only the one record is retrieved by the query.

You seem to know what you are doing. I am sure you know that the names of the controls on the form have to match the code, and that the data types have to match too.

...BUT let's go back to the basics.
Your database is used to track variances in departure / arrival times.
We know that you are interested in...
airline, flight number,
scheduled arrival
departure time,
actual arrival
departure time,
+ miscellaneous info

But we do not know your table structure and queries.

To do what you want to do, you need at least three or more
- airline carrier
- flight history
- flight schedule
- city

You will have used your own names.

tblCarrier
CarrierCode - primary key
CarrierName
+ contact info, etc

tblCity
CtyCode - primary key
City

tblFlightSchedule
(this is similar to your flight detail table??)
ScheduleID - primary key
FlightNumber - see discussion
CarrierCode - foreign ket to tblCarrier
FromCity - forein key to tblCity
ToCity - foreign key to tblCity
DepartTime - date/time
ArrivalTime - date/time
Active - yes/no
Comments - memo

Discussion:
I suspect the schedule for a flight could change. You still want to keep the old schedules to be used with the old variances. Consequently, I used ScheduleID as the primary key, not the flight number even though the flight number will be the primary reference. The Active yes/no field is used to decide which flight scheudle is active.

I separated the Departure / Arrival times. You may only concerned about one time, whether is departure or arrival. that's fine.

tblFlightHistory
HistoryID - primary key
ScheduleID - foreign key to tblFlightSchedule
FlightDate - date
ArrivalTime - date/time
DepartureTime - date/time
FlightStatus - text string
VarianceCode - text string
Comments - memo

Discussion:
This table only tracks the actual history. It references the scheduled flight for the planned times. You can also store various statistics too. (I am sure you already do this). For example:
FlightStatus - no issues, bad weather, heavy traffic, mechanical failure
VarianceCode - green, amber - carrier, amber - weather, amber - tower, amber - other, red...

So you can group delays by weather, mechanical, etc, and assign ownership of the delay, and the extent of the delay. So, over time, you may have stats to demonstrate that a carrier or sister city is costing you money with their delays.

The history table can also store the variance. Normally, calcualted values are not stored as part of normalization. but in this case, it may reasonable and may simplify calcualtions to store the time variance for each flight. If so, just add a field such as VarianceTime with a data type interger.

...Moving on
Your design may not match this, and you already have a "mature" database -- I understand.

However, by reviewing the above, there is less of a chance of skewed assumptions.

ALSO, with this design, you do not need to display the scheduled times. This can be displayed in a subform.

With the above assumptions, you would have created the records for the tblFlightSchedule, and now need to create a a subform based on tblFlightSchedule and form based on tblFlightHistory.

Befor creating these forms, be sure to create the relationships using the Relationship GUI tool (Tools -> Relationships. Add your tables, then click and drag the primary key to the respective foreign key. Enforce referential integrity)

For the subform created using tblFlightSchedule, call it sbfrmFlightSchedule and make it wide with only one row in height. Space the text boxes for CarrierCode, FromCity, ToCity, DepartureTime and ArrivalTime across the page. The idea is that the Date/Time fields will line up with the corresponding text boxes on the main form, later.

After creating the main form, lets call it frmFlightHistory Click and drag the sbfrmFlightSchedule to a suitable location on frmFlightHistory (I envision the subform being lined up and under the respective fields of the main form)

Create an unbound combo box for the FlightNumber; cancel out of the combo box wizard. Place this control near the top of the form.

I assume you have the Properties window open ("View" -> "Properties").

Select the "Data" tab in the Properties window and select the RowSource. Click on the "..." command button to open the query builder tables tblFlightSchedule, select the fields ScheduleID, FlightNumber, Active. For criteria, enter "True" for Active. (SELECT ScheduleID, FlightNumber, Active FROM tblFlightSchedule Where Active = True)
Close the query builder.

BoundColoumn: 1

On the Format tab
CoulmnCount: 3
CoulmnWidths: 0";0.7";0"

Select the "Other" tab
Change name to - "cmbSelectFlight"

Then select the "Event" tab and click on AfterUpdate and then click on the "..." command button to enter the VBA coding window.

SetSubForm

Create the same code for the OnCurrent record. To do this, click on the top left box of the form where the vertical and horzontal rulers meet. Then look at the "Event" tab in the Properties window, and select OnCurrent. Repeat the above instructions.

Then move to an unused part of the VBA coding window, perhaps below the END SUB line for the OnCurrent event, and enter the following...

Private Sub SetSubForm()

Dim strSQL as String

If Nz(Me.cmbSelectFlight, 0) Then
   strSQL = "SELECT * FROM tblFlightSchedule WHERE ScheduleID = " _
   & Me.cmbSelectFlight
Else
   strSQL = "SELECT * FROM tblFlightSchedule WHERE ScheduleID = 0"
End If

Me.sbfrmFlightSchedule.From.RecordSource = strSQL
Me.sbfrmFlightSchedule.Requery

End Sub

What happens is that the record source for the subform will change depending on the selected value in the combo box. The same thing will occur for the OnCurrent record.

Should work just fine.

Richard

 

[scsuflyboy2005]

Richard, I have tried this but keep running into problems with the coding for some reason.