Using Data from one field (on form) to complete other fields

[sb28224]

I am playing about and learning access.

I am trying to set up a database of flights of have been on.

The 2 tables are:

All flight details
Airport Details

When creating the form for the all flight details I have successfully managed to create a combo box for Airport_Code which then auto-fill in the Airport_Name with the correct airport name - all working fine.

When i then try to also get this to auto-fill in the country of the airport it does not return anything - the Country field is in the source of Airport_Code (column 2) - 3rd column over.

The code i added in the the code builder is:

Private Sub cmbDepAirportCode_AfterUpdate()

Me.Departure_Airport_Name.Value = Me.cmbDepAirportCode.Column(1)

Me.Departure_Country.Value = Me.cmbDepAirportCode.Column(2)

End Sub

The first line works fine to return the airport name but i cannot, for the life of me, get the country to return

Can anyone suggest anything?

Thanks

[Micron]

Don't think you should need it, but try a Me.Refresh after the column(2) line.
Really think you should have more than 2 tables for this. I imagine you're cramming a lot of related data into the same details table and instead, should at least add carrier, country and maybe trip tables. It might depend on what you consider "flight details" to mean. In relational db's each entity that the db deals with should have its own table; the trick is figuring out which things are entities and which are aspects or characteristics of them.

I suspect the trip is what this db is really all about and you don't have a table for that. A trip can consist of multiple flights, each departing/arriving at a specific airport, maybe on the same day, maybe not. Each flight can be on a different carrier and be on a different model of aircraft. That's six tables right there.

Do yourself a favour if you're going to get into this at any level of seriousness. Read these
https://www.accessforums.net/showthr...773#post521773

[sb28224]

Thanks for the reply - will definitely have a look at the link before I go any further!

[Welshgasman]

Show the source of the combo anyway.

[sb28224]

Show the source of the combo anyway.

Source code in Combo box:

SELECT Airports.[Aiport Code], Airports.Airport, Airports.Country FROM Airports;

[xps35]

I think you should not store the airport(s) name(s) and country/countries in the flight details at all. Just airport code is enough (if that is the key to the airport table). Storing the name and country (again) is redundant and bad database design. The airport table is the one and only table where you store the name and country of an airport.

Just out of curiosity, how do you handle stopovers?

[June7]

Agree with xps35.

If you want to display airport and country after airport code is selected, use UNBOUND textboxes with expression that references combobox column by index, index begins with 0 so column 2 is index 1:
=cmbDepAirportCode.Column(1)
=cmbDepAirportCode.Column(2)

And many would advocate using autonumber instead of AirportCode as key.

Should not even need VBA to save AirportCode if you use BOUND form.

Advise not to use spaces nor punctuation/special characters (underscore is only exception) in naming convention. Then [] will not be necessary in most cases.