Combo Box Help

johnherrerajuan

Registered User.
Local time
Today, 10:35
Joined
Feb 25, 2013
Messages
44
Hello
I have a form that is used for data entry; from there I want it to go to another 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 caluated field to be shown next to it.
I tried making an if statement 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)))))))))))))))))))))))))
 
Your post is very difficult to understand - what has the IIf statement got to do with opening a second form?

Is the IIf statement the calculated field?

What are you actually trying to achieve?
 
See again the syntax for IIF function.
IIF(Expresion As Boolean, WhatToReturnIfTrue, WhatToReturnIfFalse)
The Expresion part can hold an equal sign: IIF(A=B, .....) but is non equal sign in the WhatToReturnIfTrue or WhatToReturnIfFalse parts.

Example: IIF(A=B, "Division by ZERO is not allowed", A/(A-B))

I think that the really answer to your issue is an external function that perform all this complicate nested IIFs at once.
 
See again the syntax for IIF function.
IIF(Expresion As Boolean, WhatToReturnIfTrue, WhatToReturnIfFalse)
The Expresion part can hold an equal sign: IIF(A=B, .....) but is non equal sign in the WhatToReturnIfTrue or WhatToReturnIfFalse parts.

Example: IIF(A=B, "Division by ZERO is not allowed", A/(A-B))

I think that the really answer to your issue is an external function that perform all this complicate nested IIFs at once.


I fixed it but it would not let me enter it to the expression builder
=IIf([Combo4]=“Building Service Engineer”,[Forms]![Estimate]![Text787],
IIf([Combo4]=“Carpenter”,[Forms]![Estimate]![Text788],
IIf([Combo4]=“Custodian”,[Forms]![Estimate]![Text789],
IIf([Combo4]=“Custodian - Shift Pay (5am - 6am)“,[Forms]![Estimate]![Text790],
IIf([Combo4]=“Electrician”,[Forms]![Estimate]![Text791],
IIf([Combo4]=“Facilities Project Supervisor”,[Forms]![Estimate]![Text792],
IIf([Combo4]=“Fire Marshal”,[Forms]![Estimate]![Text793],
IIf([Combo4]=“Gardening Specialist”,[Forms]![Estimate]![Text794],
IIf([Combo4]=“Grounds Worker”,[Forms]![Estimate]![Text795],
IIf([Combo4]=“Interior Design”,[Forms]![Estimate]![Text796],
IIf([Combo4]=“Irrigation Specialist”,[Forms]![Estimate]![Text797],
IIf([Combo4]=“Laborer”,[Forms]![Estimate]![Text798],
IIf([Combo4]=“Lead Auto/Equip Mechanic”,[Forms]![Estimate]![Text799],
IIf([Combo4]=“Lead Custodian”,[Forms]![Estimate]![Text800],
IIf([Combo4]=“Lead Grounds Worker”,[Forms]![Estimate]![Text801],
IIf([Combo4]=“Light Auto/Equip Operator”,[Forms]![Estimate]![Text802],
IIf([Combo4]=“Locksmith”,[Forms]![Estimate]![Text803],
IIf([Combo4]=“Maintenance Mechanic”,[Forms]![Estimate]![Text804],
IIf([Combo4]=“Painter”,[Forms]![Estimate]![Text805],
IIf([Combo4]=“Pest Control Specialist”,[Forms]![Estimate]![Text806],
IIf([Combo4]=“Plumber”,[Forms]![Estimate]![Text807],
IIf([Combo4]=“Recycler (Laborer)“,[Forms]![Estimate]![Text808],
IIf([Combo4]=“Refrigeration Mechanic”,[Forms]![Estimate]![Text809],
IIf([Combo4]=“Supervising Building Service Engineer”,[Forms]![Estimate]![Text810],0)))))))))))))))))))))))))
 
I fixed it but it would not let me enter it to the expression builder
I don't like that expression builder. So I advice you to paste in the control source (including the very first equal sign)
 
Ufff. Lets work around.
Post the SQL for Combo4 (hope is Combo4 because in the first post was Combo2) and say me what the all this IIFs should return if nothing much the selection. I see 0 (ZERO) but I'm not sure because all other returning are texts (strings)
 
Well Im going to do this for 10 combo boxes so it would be from

Combo2-12 and the last 0 is not important. How do i Post the SQL for Combo4
 
In form design view, select Combo4, go to Property Sheet -> Row Source -> click the button with 3 dots.
This will open a query. Don't make any changes here.
On the ribbon, on the left side, is a button that allow you to run the query or to view in design mode or as SQL.
Show the query in SQL view, copy the code then paste on the forum.

Close the window and NOT SAVE if Access ask you for save.
 
SELECT Labor.Laborer
FROM Labor
ORDER BY Labor.[Laborer];
that shows up

And also i previously typed in the values in the combo box so i had to go back and change it to get the data from a table
 
OK. Lets start !
In VBA editor create a module. Do not use a module that is attached to a form. Create a new one.
In this module paste this code:
Code:
Public Function fncRetValBasedOnCmb4() As String
    Select Case Forms![B]YourFormNameThatContainCombo4[/B]!Combo4
        Case "Building Service Engineer"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text787
        Case "Carpenter"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text788
        Case "Custodian"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text789
        Case "Custodian - Shift Pay (5am - 6am)"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text790
        Case "Electrician"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text791
        Case "Facilities Project Supervisor"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text792
        Case "Fire Marshal"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text793
        Case "Gardening Specialist"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text794
        Case "Grounds Worker"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text795
        Case "Interior Design"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text796
        Case "Irrigation Specialist"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text797
        Case "Laborer"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text798
        Case "Lead Auto/Equip Mechanic"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text799
        Case "Lead Custodian"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text800
        Case "Lead Grounds Worker"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text801
        Case "Light Auto/Equip Operator"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text802
        Case "Locksmith"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text803
        Case "Maintenance Mechanic"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text804
        Case "Painter"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text805
        Case "Pest Control Specialist"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text806
        Case "Plumber"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text807
        Case "Recycler (Laborer)"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text808
        Case "Refrigeration Mechanic"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text809
        Case "Supervising Building Service Engineer"
            fncRetValBasedOnCmb4 = Forms!Estimate!Text810
        Case Else
            fncRetValBasedOnCmb4 = 0
    End Select
End Function
Replace the bold text with the name of the form that contain your Combo4 combobox.

Close the module window and return to your form.
In the control source for Combo4 write:
= fncRetValBasedOnCmb4()
including the equal signRun the form.

Hope this time will work.
 
Last edited:
Glad to help you.
I've edited my previous post
This line
= fncRetValBasedOnCmb4
should be
= fncRetValBasedOnCmb4()
 
One more advice for you:
Change the name for your controls in order to reflect what the control show.
As example, instead
Text787 you should use something like this: txtBuilServEng
 

Users who are viewing this thread

Back
Top Bottom