Need a better way then using if statements in MS Access

Question

Right now I am working on a new database that will show changes to one of our manuals that people request. I have two tables and one form. The first table is were all the information will be stored from the reference or subchapter of the manual, the current text in that subchapter, and what the proposed changes are. The second table is for subchapter reference with three fields: ID, SubChapter, and a memo field with what the subchapter says. Right now I am using a combo box for the chapter reference and depending on the reference it fills in another field with current text from another field. Here is my if statement to get a better idea:

If code_ref = "82.101(c)" Then
current_code = "Other County Regulations"
proposed_code.SetFocus
End If

What I would like to do is turn those thousand if statements into a lookup to where it checks the reference and then inserts the proper text in the next field. I am having trouble trying to find a way to do this without just finishing the if statements. I want to do it on change so they can cycle through the reference to find exactly what they are looking for. Any suggestions?

Taken from my comment: An idea of how this will work will be they open the form fill out their name, the item it falls under, use the combobox to select the code reference, once they select the code reference the current code field box will be filled in for them and it will set focus on the proposed code field so they can make their changes.

Answer 1

You need a look-up that is table-based. From your description I'm not sure if this calls for a 3rd table, or an additional field in the 2nd table.

http://www.trigonblue.com/AccessLookup.htm
http://www.fabalou.com/Access/General/lookup%5Ftables%5Flut.asp

Do not use "LookUp Fields":
http://www.mvps.org/access/lookupfields.htm

Answer 2

Is the reference available in a table, and if not, can you build such a table:

code_ref     current_code
82.101(c)    Other County Regulations

With such a table, you should be able to create a query that can be used to show the current_code when code_ref is selected.

Edit re Comment

You may be able to create a query that selects data from both tables and then use the wizard to add a combobox to navigate through the records. If you have unique keys, the recordset may be editable.

You may be able to set up a combobox that can be used to DLookUp the relevant table:

Me.txtTextBox = DLookUp("FieldYouWantToReturn","TableNameHere","current_code='" _
   & Me.cboCombobox & "'"

The way DlookUp (briefly) is:

DLookUp("FieldYouWantToReturn","TableNameHere","NameOfTextFieldInTable='" _
       & Me.ControlOrValueToCompare & "'"

Me is shorthand for the current form
Note that the control is outside of the quotes
If you want to compare a number, you do not need quotes
If you want to compare a date, you need hash marks #yyyy/mm/dd#

You may be able to use a subform, with current_code for the link fields.

These are just a few ideas, it is not quite clear what you want the user to be able to do.