Using a combo box to fetch information on a form

[johnherrerajuan]

Hello
I have a form that is used for data entry; from there I want to create a new form where I can select a field from a combo box which would generate the amount associated.
Example on the first form it has a Carpenter that has a calculated output of $1000, on the second form I would want to click on the combo box and pick carpenter and I would like the calculated field to be shown next to it.
I tried making an if statement on an unbound box but I found it difficult
=IIf([Combo2]=Building Service Engineer, =[Forms]![Estimate]![Text787],IIf([Combo2]=Carpenter,=[Forms]![Estimate]![Text788],IIf([Combo2]=Custodian,=[Forms]![Estimate]![Text789],IIf([Combo2]=Custodian - Shift Pay (5am - 6am) ,=[Forms]![Estimate]![Text790],IIf([Combo2]=Drafting Technician,=[Forms]![Estimate]![Text791],IIf([Combo2]=Electrician,=[Forms]![Estimate]![Text792],IIf([Combo2]=Facilities Project Supervisor,=[Forms]![Estimate]![Text793],IIf([Combo2]=Fire Marshal,=[Forms]![Estimate]![Text794],IIf([Combo2]=Gardening Specialist,=[Forms]![Estimate]![Text795],IIf([Combo2]=Grounds Worker,=[Forms]![Estimate]![Text796],IIf([Combo2]=Interior Design,=[Forms]![Estimate]![Text797],IIf([Combo2]=Irrigation Specialist,=[Forms]![Estimate]![Text798],IIf([Combo2]=Laborer,=[Forms]![Estimate]![Text799],IIf([Combo2]=Lead Auto/Equip Mechanic,=[Forms]![Estimate]![Text800],IIf([Combo2]=Lead Custodian,=[Forms]![Estimate]![Text801],IIf([Combo2]=Lead Grounds Worker,=[Forms]![Estimate]![Text802],IIf([Combo2]=Light Auto/Equip Operator,=[Forms]![Estimate]![Text803],IIf([Combo2]=Locksmith,=[Forms]![Estimate]![Text804],IIf([Combo2]=Maintenance Mechanic,=[Forms]![Estimate]![Text805],IIf([Combo2]=Painter,=[Forms]![Estimate]![Text806],IIf([Combo2]=Pest Control Specialist,=[Forms]![Estimate]![Text805],IIf([Combo2]=Plumber,=[Forms]![Estimate]![Text805],IIf([Combo2]=Recycler (Laborer) ,=[Forms]![Estimate]![Text805],IIf([Combo2]=Refrigeration Mechanic,=[Forms]![Estimate]![Text805],IIf([Combo2]=Supervising Building Service Engineer)))))))))))))))))))))))))


[Text787]-[Text805] are fields from the first form, these are calculated fields that multiplies wages quantity etc.

Also [Combo2] wont let me pick any data that I typed in

 

[Cronk]

The syntax for the Immediate If statement is
IIf(Condition, true value, false value)

In your post,
=IIf([Combo2]=Building Service Engineer, =[Forms]![Estimate]![Text787],....

it should be
=IIf([Combo2]=Building Service Engineer, [Forms]![Estimate]![Text787],

ie lose the equals sign shown in red.

Incidentally, I find it much easier and more productive to use a function rather than so many nested IIf statements.

 

[johnherrerajuan]

The syntax for the Immediate If statement is
IIf(Condition, true value, false value)

In your post,
=IIf([Combo2]=Building Service Engineer, =[Forms]![Estimate]![Text787],....

it should be
=IIf([Combo2]=Building Service Engineer, [Forms]![Estimate]![Text787],

ie lose the equals sign shown in red.

Incidentally, I find it much easier and more productive to use a function rather than so many nested IIf statements.

I fixed it:
=IIf([Combo5]=“Building Service Engineer”,[Forms]![Estimate]![Text787],IIf([Combo5]=“Carpenter”,[Forms]![Estimate]![Text788],IIf([Combo5]=“Custodian”,[Forms]![Estimate]![Text789],IIf([Combo5]=“Custodian - Shift Pay (5am - 6am)”,[Forms]![Estimate]![Text790],IIf([Combo5]=“Electrician”,[Forms]![Estimate]![Text791],IIf([Combo5]=“Facilities Project Supervisor”,[Forms]![Estimate]![Text792],IIf([Combo5]=“Fire Marshal”,[Forms]![Estimate]![Text793],IIf([Combo5]=“Gardening Specialist”,[Forms]![Estimate]![Text794],IIf([Combo5]=“Grounds Worker”,[Forms]![Estimate]![Text795],IIf([Combo5]=“Interior Design”,[Forms]![Estimate]![Text796],IIf([Combo5]=“Irrigation Specialist”,[Forms]![Estimate]![Text797],IIf([Combo5]=“Laborer”,[Forms]![Estimate]![Text798],IIf([Combo5]=“Lead Auto/Equip Mechanic”,[Forms]![Estimate]![Text799],IIf([Combo5]=“Lead Custodian”,[Forms]![Estimate]![Text800],IIf([Combo5]=“Lead Grounds Worker”,[Forms]![Estimate]![Text801],IIf([Combo5]=“Light Auto/Equip Operator”,[Forms]![Estimate]![Text802],IIf([Combo5]=“Locksmith”,[Forms]![Estimate]![Text803],IIf([Combo5]=“Maintenance Mechanic”,[Forms]![Estimate]![Text804],IIf([Combo5]=“Painter”,[Forms]![Estimate]![Text805],IIf([Combo5]=“Pest Control Specialist”,[Forms]![Estimate]![Text806],IIf([Combo5]=“Plumber”,[Forms]![Estimate]![Text807],IIf([Combo5]=“Recycler (Laborer)”,[Forms]![Estimate]![Text808],IIf([Combo5]=“Refrigeration Mechanic”,[Forms]![Estimate]![Text809],IIf([Combo5]=“Supervising Building Service Engineer”,[Forms]![Estimate]![Text810],0)))))))))))))))))))))))))

But now it says its to long

 

[johnherrerajuan]

I also tried it this way but there is an invalid syntax

=Switch([Combo5]=“Building Service Engineer”,[Forms]![Estimate]![Text787],[Combo5]=“Carpenter”,[Forms]![Estimate]![Text788],[Combo5]=“Custodian”,[Forms]![Estimate]![Text789],[Combo5]=“Custodian - Shift Pay (5am - 6am)”,[Forms]![Estimate]![Text790],[Combo5]=“Electrician”,[Forms]![Estimate]![Text791],[Combo5]=“Facilities Project Supervisor”,[Forms]![Estimate]![Text792],[Combo5]=“Fire Marshal”,[Forms]![Estimate]![Text793],[Combo5]=“Gardening Specialist”,[Forms]![Estimate]![Text794],[Combo5]=“Grounds Worker”,[Forms]![Estimate]![Text795],[Combo5]=“Interior Design”,[Forms]![Estimate]![Text796],[Combo5]=“Irrigation Specialist”,[Forms]![Estimate]![Text797],[Combo5]=“Laborer”,[Forms]![Estimate]![Text798],[Combo5]=“Lead Auto/Equip Mechanic”,[Forms]![Estimate]![Text799],[Combo5]=“Lead Custodian”,[Forms]![Estimate]![Text800],[Combo5]=“Lead Grounds Worker”,[Forms]![Estimate]![Text801],[Combo5]=“Light Auto/Equip Operator”,[Forms]![Estimate]![Text802],[Combo5]=“Locksmith”,[Forms]![Estimate]![Text803],[Combo5]=“Maintenance Mechanic”,[Forms]![Estimate]![Text804],[Combo5]=“Painter”,[Forms]![Estimate]![Text805],[Combo5]=“Pest Control Specialist”,[Forms]![Estimate]![Text806],[Combo5]=“Plumber”,[Forms]![Estimate]![Text807],[Combo5]=“Recycler (Laborer)”,[Forms]![Estimate]![Text808],[Combo5]=“Refrigeration Mechanic”,[Forms]![Estimate]![Text809],[Combo5]=“Supervising Building Service Engineer”,[Forms]![Estimate]![Text810])