help on reference tables

[bill crumpton]

I have 17 unbound fields on a form which stores 1 character in which the user will enter a number or a letter. I have 17 more unbound fields that I need to translate the number or letter that the user inputs into a certain predetermined number. For example, "A" = 1, "B"=2 and so forth. Any help on the code to automatically do this. Thanks as always.


BC

 

[AlanS]

Set up a Public function in a standard module, something like the one below. If the user will be entering the letter in Field1 and you want to put the number in Field2, just put this statement in Field1's AfterUpdate event procedure:

Field2 = CodeValue(Field1)

Public Sub CodeValue(Arg as Variant) as Integer
Select Case Left(Nz(Arg, ""), 1)
Case "A": CodeValue = 1
Case "B": CodeValue = 2
...
Case Else: CodeValue = 99 'or whatever you want in case of a Null or "none of the above" argument
End Select
End Sub

 

[bill crumpton]

Thanks again Alan works great.