Suggestions for one form depending on several tables

[Richard1980]

Hello gents,
I need your help on this item.
I have a DB with an AIRPORTS table which collects many information e.g.: ICAO & IATA codes, Airport Name and other stuffs.
Here following is the Relationships screen of my DB: http://i42.tinypic.com/30wtmhl.png
Now I want to create a FLIGHTS form, which contains some information taken from AIRPORTS table. It should have:
- (2 combobox) ICAO code for both Departure and Arrival airport.
- (2 textbox) IATA & (2 textbox) Airport Name based on ICAO codes choosen.
ICAO field will be managed by a combo box with row source AIRPORTS.[ICAOApt].
Then the form will have two textboxes for Airport Names, every one with a variable text depending on Departure and Arrival airport.
Well, could I manage those textbox in another way than using DLookup function? Maybe using the relationships screen?
I don't want to use DLookUp because I should use it for a lot of textboxes: 2 for Airport names, 2 for IATA codes, 2 for GMT offeset and so on... All these fields are related on ICAO code.
Thanks for your suggestions.
Bye.

Riccardo

 

[pbaldy]

I'm not clear on the question, but is this what you're after?

http://www.baldyweb.com/Autofill.htm

 

[Richard1980]

I'm not clear on the question, but is this what you're after?

http://www.baldyweb.com/Autofill.htm

Hello Paul,
sorry but my english sucks and I cannot explain well what is the matter.
So, try again. Here is the new relationships screes: Relationships Screen
As you can see I added a new table called FLIGHTS which is related (by ICAOAptDEP field) to AIRPORTS.
Then I created a new form (FLIGHTS): FLIGHTS form
The form has two combos, which are row sourced to the table AIRPORTS and a textbox for the Airport name. I took the textbox from AIRPORTS table because this field is not to be stored in the FLIGHTS table.
Well, due to relationships between ICAOAptDEP (in table FLIGHTS) and ICAOApt (in table AIRPORTS) when I select from the first combo one airport, its complete name comes out in the textbox. Right?
Well, now I wanna insert a second textbox for the Airport name of the arrival airport, choosen by the second combobox. How to do that? Selecting it from AIRPORTS table it will show again the first airport name...

 

[pbaldy]

Well, the link does what you want, but perhaps with a different method. That said, looking at your relationships, there is none between the arrival airport in flights and the airports table.

 

[Richard1980]

Well, the link does what you want, but perhaps with a different method. That said, looking at your relationships, there is none between the arrival airport in flights and the airports table.

I have just followed the procedure described in the link you have written. It works for AIRPORT NAME field but not for the COUNTRY field, which is also related to ICAO code selected by combobox.
These screens can explain what I did:
- http://i39.tinypic.com/9sawc7.png
- http://i43.tinypic.com/160q8vk.png

About your last sentence. How to link Arrival Airports from FLIGHTS to AIRPORTS table? When I try to link ICAOAptArr to ICAOApt I get an error:
"A relationships already exists...". I have already experienced that is not possible link one field from a table (A) to two fields of the same table (B).
Thanks.

 

[pbaldy]

Make sure the column count property of the combo is correct. If you added fields to the row source, it won't be.

Regarding the relationships, you link the second field to a copy of the airports table (Access will alias the copy). In other words, you will add the airports table to the relationships diagram twice.

 

[Richard1980]

Make sure the column count property of the combo is correct. If you added fields to the row source, it won't be.

Regarding the relationships, you link the second field to a copy of the airports table (Access will alias the copy). In other words, you will add the airports table to the relationships diagram twice.

Ok, modified the column count to 3, now it works, but the combobox shows 3 columns (ICAO, AptName and Country), it is not good. I should to set the column width such as: 2cm,0cm,0cm to avoid that. It does not fit my needs.

 

[pbaldy]

Setting the column widths doesn't work for you? Did the relationship change work for you?

 

[Richard1980]

Speaking about the relationship change...
Something like that?
http://i43.tinypic.com/334846g.png

However, creating this link to the AIRPORTS alias table does not fix the problem. Because when I drag the AptName from AIRPORTS table into the form, it gives only the airport name of the first combobox. So I have two textbox but the same result. While I should have two textbox with two different results depending on two different airports choosen by combobox.

 

[pbaldy]

If the form is based on a query, it would need the same relationship. In other words, you'd be dragging the name from the alias.

 

[Richard1980]

I understand you, but unfortunately the AIRPORTS alias table is not available or maybe I don't know how to view it.
This is the screenshot of the Form record source, as you can see the alias 1 table (which I have just created in the relationships screen) does not exist.

http://i44.tinypic.com/prvw6.png

I don't know how going forward.

Edit: Wait, wait, please. I am able to fix the matter. I added twice the Airports table and then created a relation between the ICAOAptARR (in FLIGHTS table) and the ICAOApt (in ARIPORTS table). Now it seems to be ok.
Ah, ok, I have just read your answer... Thanks!
Anyway, if I create a relationship in the Form record source, is it necessary to create the same relationship in the Relatioships screen? I don't know why this new relationship was not shown in the Form record source...

 

[pbaldy]

No, you'd do the same thing here (add the airport table twice).

 

[Richard1980]

No, you'd do the same thing here (add the airport table twice).

Many thanks Paul, your patience was absolute appreciable.
Thanks.
Bye.

 

[pbaldy]

No problemo!

 

[Richard1980]

For a strange event it does work no more. When I open the form all data are wrong
Now when I select from first combobox all textboxes are updated, not only those textboxes related to the first combobox.
I cannot understand!
When I populate (correctly) the form using the "alias" Airports_1 table I see this:
http://i39.tinypic.com/14txwuh.png

I save the form, close it and re-open, and all it has been changed!
http://i39.tinypic.com/qrkk87.png

All textboxes are related to th Airports Table, no-more to the Aiports_1. Why???