How do I make one field value determine value of second field in form?

[jcrist30]

In my database, I have a table that has a list of procedure codes as one field and a cost assigned to each procedure as a second field.

I have a second table where I enter one of the procedure codes (looked-up from my original table/typed in), and I would like the value associated with this code in the original table to auto-populate a second field "after update".

Is there a way to make this field pull from the same table as the procedure code is pulling rather than having to type hundreds of "If/Then" statements in VBA?

[zmbd]

jcrist30:

I would normally do this in a query.
As given in your first table, hopefully, the procedure codes are your primary key. These codes are then a foreign key in the second table and you would establish a one to many relationship between the tables on the common field... no need to have the same data in two tables as you are working in a relational database.

You would then use the tables in queries to do your data pulls and other calculations. I also create my lookup controls at the query and form level.

I must ask, due to the nature of your question, about your experience level with Access? Starting out, done some work etc...

Respectfully, I ask that you take a read thru the following:
Database Normalisation and Table Structures. This article does a fairly good job explaining the normalization concepts - which for many, is a difficult thing to master.... I know, been there once a long time ago myself.

This website has a great basic tutorial on RDMS design... you're not going to design a hospital admin application with this site... well, maybe you can... the steps would be basically the same: A Tutorial for Access

Of particular note is tables page within the tutorial - notice the information that is given, the table name, the field name, the field type, key or index, etc... AND the format used to present that data. When you post back, this is the type of information we'll need to help you along in your project. You don't need a fancy gridded table to do this... just a line by line layout.

As for lookup fields in tables... I avoid lookup fields at the table level! :Are Lookup Fields in Tables Evil? ... despite the fact that these are supported in MSAccess, they are not supported in other databases should you need to upgrade and writing queries later on will be somewhat problematic. IMHO: The ONLY exception I've seen for this deals with a share-point integration.
There will be other people that argue that these are just fine at the table level... I side with with the MVPs that do this stuff for a living.

-z

[lyodmichael]

the secret of your problem is in the query .