I need help figuring out this formula or need a suggestion on coming up with a better

johnherrerajuan

Registered User.
Local time
Today, 07:06
Joined
Feb 25, 2013
Messages
44
I need help with these formulas, i tried using this one but it told me it was to long

=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)))))))))))))))))))))))))

So I approced it as a SWITCH functnion

=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])

That didnt work so I used it as a module


Code:
Option Compare Database
    Public Function fncRetValBasedOnCmb4() As String
    Select Case Forms!ReportFinished!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


The thing is i do not know how to trigger this

---------------------------------------------------
I have a form where the data is entered
and im trying to create a new form with a few combo boxes that once selected it will show the amount associated in an unbound text box (calculated field from the first form)
 
That seems like an extensive amount of code. What are you trying to accomplish?
 
It is!
I have a form that is mainly used for data entry.

and all the information will migrate to a second form as shown here
attachment.php


the thing is that i dont want all of them to show, just the ones that have a value great then one. so i want to replace the boxes with combo boxes so i can just click the drop down and click an item. once i click on it i would get the amount associated.
 

Attachments

  • Image 33.jpg
    Image 33.jpg
    27.2 KB · Views: 246
Oh, is so cool and unusual to design a VB function before to know how and where to use it.
Fortunately, the exact answer is in this thread.

Good luck !
 

Users who are viewing this thread

Back
Top Bottom