Ideas on shorter code for cascading updates

[usmcgrunt]

I have a form with a textbox that the user enters an ID into. The field Text Format is A\(A\)0000;0;_. This ensures that the ID is in the appropriate format. i.e. E(E)2100. When the user enters an ID I want another txtbox on the form to populate with the corresponding division that the ID belongs to. And in another textbox on the form I want it to populate with the corresponding volume that the ID belongs to within the division.

I have 3 tables that contain all of the required information to execute this automation. They are:

tblData - contain the individual records where new IDs are added.
tblDivisions - contains a list of all the divisions in the school. There are currently 7 Divisions.


tblVolumes - contains a list of all the volumes within the divisions within the school. There are currently 60 volumes.

tblDivisions is related to tblVolumes (one-to-many)
tblDivisions is related to tblData (one-to-many)
tblVolumes is realted to tblData (one-to-many)

Currently I am able to accomplish the above casacading update but my code is very very very long and not very "pretty". Below is a very small portion of the code I have on the BeforeUpdate event of the txt field 'ID' on my form.

I am hoping to find a more efficient way of accomplishing my objective without having to have 180 lines of code behind the txtbox.

Any suggestions?

If ID.Value Like "E(C)21*" Then
    LDivision.Value = 2
    LVolume.Value = 5
ElseIf ID Like "E(C)22*" Then
    LDivision.Value = 2
    LVolume.Value = 21
ElseIf ID Like "E(C)23*" Then
    LDivision.Value = 2
    LVolume.Value = 11
ElseIf ID Like "E(M)31*" Then
    LDivision.Value = 3
    LVolume.Value = 2
ElseIf ID Like "E(M)32*" Then
    LDivision.Value = 3
    LVolume.Value = 1
ElseIf ID Like "E(M)33*" Then
    LDivision.Value = 3
    LVolume.Value = 15
ElseIf ID Like "E(E)41*" Then
    LDivision.Value = 4
    LVolume.Value = 14
ElseIf ID Like "E(E)42*" Then
    LDivision.Value = 4
    LVolume.Value = 12
ElseIf ID Like "E(E)43*" Then
    LDivision.Value = 4
    LVolume.Value = 8
.....And another 130 or so lines following the same logic

[pbaldy]

Offhand I don't see any pattern that would let you shorten it. That said, what I would probably do is have a table with 3 fields, the ID, division and volume. Your code could then use a DLookup or recordset (better) to get the 2 values. That also makes it more maintainable; if you add new ID's, you simply add them to the table. You don't have to modify code.

[usmcgrunt]

Thanks Paul, your suggestion makes sense and would be much "cleaner". I will give it a go. Thanks.

Sean

[pbaldy]

Happy to help Sean. Post back if you get stuck.