Options for large If/ElseIf statement

[gchichester via AccessMonster.com]

I’m trying to add value to a form control(Contract Number) using the data
from a table “Contract†which has 3 fields ContractShipline,
ContractCustomer, ContractNumber.
I’m currently using a very long “If / ElseIf †statement in the form to fill
in the Contract Number when Shipline and ContractCustomer are equal .

If [cboAsiaNoticeShipLine] = "FE Evergreen" And
[cboAsiaNoticeServiceContractCustomer] = "CELSUS EXPORT LLC." Then
[AsiaNoticeContractNumber] = "35378"

Because the contract numbers change a lot this has become high maintenance .
My thoughts where to put the contract data in a table for easy maintenance
and use it instead of the vba code. I’m unsure how to use the Contract table
data in a If statement
or some other means.
To summarize : As the user enters data to Ship Line & Contract Customer then
the corresponding “Contract Number†would be filled in using the “Contractâ€
table data.

Thanks for any and all suggestions.
Gil

 

[Daryl S]

Gil -

Yes, put the data in a table - much easier maintenance. Then where your
code currently has the If statements, put in something like this:

Me.[AsiaNoticeContractNumber] =
DLookup("[ContractNumber]","ContractTableName","[ShipLine] = '" & _
Me.[cboAciaNoticeShipLine] & "' AND [ServiceContractCustomer] = ' &
Me.[cboAsiaNoticeServiceContractCustomer] & "'")

It will depend on the name of your table and fields. Look up help for
DLookup and you will get it.