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.
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...
Code:
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
![[bigcheeks]](/data/assets/smilies/bigcheeks.gif "[bigcheeks] [bigcheeks]")
