Help Using VBA with a table lookup

[OldCityCat]

PrivateSub cboAsiaNoticeServiceContractCustomer_LostFocus()
If [cboAsiaNoticeShipLine] = "FE APL" And[cboAsiaNoticeServiceContractCustomer] = "AMERINA, INC." Then
    [AsiaNoticeContractNumber] = "N/C"
ElseIf [cboAsiaNoticeShipLine] = "FE Maersk" And[cboAsiaNoticeServiceContractCustomer] = "AMERINA, INC." Then
    [AsiaNoticeContractNumber] = "627157"
ElseIf [cboAsiaNoticeShipLine] = "FE MOLU" And[cboAsiaNoticeServiceContractCustomer] = "AMERINA, INC." Then
   [AsiaNoticeContractNumber] = "US0000280"
EndIf
  End Sub

I'm looking for a better way to rewrite this code, because contract numbers change frequently it's become a pain to have edit the code.
So my thought was to create a Lookup table containing , AsiaNoticeShipLine, AsiaNoticeServiceContractCustomer and AsiaNoticeContractNumber.
Now that I have table with data, I looked at using Dlookup, but couldn't see how to use it with a Lookup table.

I thought there might be a way to modify the current VBA code todo the lookup, but don't have the enough experience with VBA to know.

Thank you in advance for any and all suggestion

OldCityCat

[orange]

Can you describe this iin more detail?

because contract numbers change frequently

What does a ContractNumber represent? Why do they change frequently?


Can you post a jpg of your tables and relationships?

[pbaldy]

You could use the form control method, with both fields in the criteria:

http://access.mvps.org/access/general/gen0018.htm

[OldCityCat]

Canyou describe this in more detail?

Whydo they change frequently?

We will be receiving new contact numbers from our customer for the next 4 Months
SoI will have to update the code with the new number in order to complete abooking request.

Whatdoes a ContractNumber represent?

ContractNumber is a field on a form - see attached file

Canyou post a jpg of your tables and relationships?

Thereare no relationships defined in this db

[OldCityCat]

Paul,
Thanks for the suggestion and link.
Before I run off and try using Dlookup, I have another concern,
You wouldn't know this because I posted only 3 of 200+ "If, ElseIf statements" from my code.
With that in mind Is it still smart to use Dlookup as a replacement?

Thanks
OldCityCat

[pbaldy]

From the sound of it, sure, as long as they are all still looking at those 3 fields. You'd be replacing the 200+ If statements with a single DLookup. There could be 1,000 records in your lookup table, and it's still a single DLookup to find the 3rd field based on the 2 combos.

[OldCityCat]

Paul,
Thanks that's music to my ears.
With not having much experience with using Dlookup.
If may ask how does this look ?

  DLookup("AsiaNoticeContractNumber", "AsiaContractOwner","Criteria = '" & forms!frmAsiaNotice!cboAsiaNoticeShipLine  & forms!frmAsiaNotice!cboAsiaNoticeServiceContractCustomer& "'")

[pbaldy]

Mediocre.

Try


DLookup("AsiaNoticeContractNumber", "AsiaContractOwner"," AsiaNoticeShipLine= '" & forms!frmAsiaNotice!cboAsiaNoticeShipLine & "' And AsiaNoticeServiceContractCustomer = '" & forms!frmAsiaNotice!cboAsiaNoticeServiceContractCustomer & "'")

[June7]

The expression as constructed assumes the fields are text. If the value of combobox is actually a number ID then the code will probably fail. Don't use apostrophe delimiter for number fields. Also, use # delimiter with date fields.

However, since your original code has been working, the comboboxes must be returning text values so just be aware of the use of delimiters in SQL statements, which the WHERE argument of domain aggregate functions is patterned off.

[pbaldy]

The expression as constructed assumes the fields are text. If the value of combobox is actually a number ID then the code will probably fail. Don't use apostrophe delimiter for number fields. Also, use # delimiter with date fields.

If you read the first post, you'll see the values are text. All that was covered by the link as well.

[June7]

Yeh, I went back and edited my post after a closer look at the first.