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.

 

[Kryst51]

Hmmmm. Are you willing to redesign your tables? If so you can set up an Aircraft_Reg table, and include the Aircraft_Type in that table. Then in Aircraft Details you would have a fk to the Aircraft_Reg Pk.

Then you could display the Reg and the Type on a form either using a dlookup in an unbound text box once a Reg is selected in a combo box, or having a listbox which could display both the reg and the type.

 

[Kryst51]

If you aren't willing to redesign then you can put something LIKE this in the after update event of the Aircraft_Reg combo box/textbox

Select Case Me.Aircraft_Reg
 
Case A5-EAA, A5-EAB, A5-EAC, A5-EAD
Me.Aircraft_Type = "A320"
 
Case A5-RAA, A5-RAB, A5-RAC, A5-RAD
Me.Aircraft_Type = "B737"
 
Case A5-GAA, A5-GAB, A5-GAC, A5-GAD
Me.Aircraft_Type = "B747"
 
Case A5-KAA, A5-KAB, A5-KAC, A5-KAD
Me.Aircraft_Type = "A340"
 
End Select

 

[tjames]

Thank you Kryst51. I would prefer to do your first suggestion. In second case, whenever I get new aircraft, the update might require to touch this coding, so I don't want to do that.

Will let you know very soon if it is working.

 

[vbaInet]

I don't think a redesign is necessary to achieve this. An ID relates to the Aircraft_Reg and Aircraft_Type.

If the control you use to make a selection is a combo box or a listbox then you can simply point to the right column to get the Type. Use this as the Control Source of the textbox you want to display the Type in:

[Combobox1].[Column](2)

 

[tjames]

Probably I might be doing wrong. But I tried everything and still I could not make it work. Can anyone help me solve this problem?

Would be grateful, if anyone could do it.

Thanks & regards.

 

[vbaInet]

Upload a stripped down copy of your db and we'll take a look.