MS Access 2007 Help

[tjames]

I have two fields; Aircraft_Reg and Aircraft_Type on a form pulled out from the table (tblAircraft_Details) in MS Access 2007 as following:

Aircraft_Reg: Aircraft_Type:

tblAircraft_Details:


ID /Aircraft_Reg/ Aircraft_Type
1 / A5-EAA / A320
2/ A5-EAB / A320
3 / A5-EAC/ A320
4 / A5-EAD / A320
5/ A5-RAA/ B737
6 / A5-RAB / B737
7 / A5-RAC/ B737
8/ A5-RAD/ B737
9 / A5-GAA/ B747
10/ A5-GAB / B747
11 /A5-GAC/ B747


12 / A5-GAD/ B747
13/ A5-KAA/ A340
14/ A5-KAB/ A340
15 / A5-KAC/ A340
16 / A5-KAD / A340


When users select A5-EAA or A5-EAB or A5-EAC or A5-AED on Aircraft_Reg, I want Aircraft_Type to appear automatically as A320 , when A5-RAA or A5-RAB or A5-RAC or A5-RAD is selected as B737 , when A5-GAA or A5-GAB or A5-GAC or A5-GAD is selected as B747 and when A5-KAA or A5-KAB or A5-KAC or A5-KAD is selected as A340.


Please help me achieve the above either with VB code or macro or query. I would appreciate if you provide me with step by step process to solve the above as I am not really good in understanding access terms!


Thank you and best regards.

[SoftwareMatters]

If the aircraft type is fixed then I guess you don't really want to be able to select it, write?

This is what I would do:

In the aircraft reg drop-down rowsource add the aircraft type.
Then to display it I would add a textbox to the form and point the controlsource to the aircraft type column of the type drop-down.

So in the controlsource you should put something like this:

=[TypeFieldName].column(2)

It is column 2 because the first column is 0.

[tjames]

Thanks SoftwareMatters. Yes, the aircraft type is fixed such that it is one type (A320) for aircraft reg A5-EAA, A5-EAB, A5-EAC, A5-EAD and another type (B737) for aircraft reg A5-RAA, A5-RAB, A5-RAC, A5-RAD and so on. Therefore, when users select, aircraft reg as A5-EAA the aircraft type should automatically come up as A320 and when user select A5-RAA, then aircraft type should reflect as B737...

I remember I tried this method but didn't work. Anyway, I will keep you in the loop if it works.

[tjames]

Yeah this did not work. Guys, can anyone help me develop this with query? Appreciate your assistance.

[slave138]

This assumes you haven't done anything yet:

1. Create the drop down for the Aircraft_Reg selection. (I'm going to pretend it's called cboAirReg for the purpose of later coding).
2. Create a text field for Aircraft_Type (which I will be refrring to as txtAirType). I would probably set the Data properties for this field to Enabled = No & Locked = Yes.
3. In the OnChange event for cboAirReg add the following code:

Me.txtAirType.Value = DLookup("[Aircraft_Type]", "[tblAircraft_Details]", "[tblAircraft_Details].[Aircraft_Reg]=" & Chr(34) & Me.cboAirReg & Chr(34))