Automatically populating a field in a table

[JudyB]

I have a table titled "Job Codes" with 3 fields - 1) JobTitleID, 2) LOP, and
3)JobTitleName. I would like to tie the LOP field and the JobTitleName
fields together. In other words, when I select a JobTitleName on my form, I
would like for it to automatically enter the LOP that corresponds with that
title. Is this possible? Thanks!

 

[Jeff Boyce]

Why the LOP and not the [JobTitleID]? "Automatically enter the LOP" ...
?where?

If you use a combobox on your form, you can "see" the [JobTitleName]
displayed, even though your table is storing the [JobTitleID].

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have a table titled "Job Codes" with 3 fields - 1) JobTitleID, 2) LOP, and
3)JobTitleName. I would like to tie the LOP field and the JobTitleName
fields together. In other words, when I select a JobTitleName on my form, I
would like for it to automatically enter the LOP that corresponds with that
title. Is this possible? Thanks!

Possible? Yes.
A good idea? ABSOLUTELY NOT.

You're using a relational database - use it relationally! If a LOP code
implies a specific JobTitleName, then you should have a LOP table with one
record for each JobTitleName; you would store only one of these fields in your
Job Codes table, and then use a Query joining it to the LOP table to look up
the other one.

 

[vbasean]

you can use the 'After_Update' event of whichever control you use for
JobTitleName to insert the value into your form.

is LOP 'point in time' data? Meaning, is LOP a value that changes over time
for each job? Like a price of a product, you have a table with products and
current price and when you add them to an invoice that is the price at time
of sale. That is 'point in time' data. It is something to want to record.
If it's not, then you don't want to record it, you just want to retreave it.
If that's the case then you just create a query with the two tables
containing the information and then just add that field to your form. When
you set the value in 'JobTitleName' the other value shows up.

If it is 'time in point' then you want to 'copy' the data from one table to
the other. This code is added to a combo box named 'JobTitleName' in the
'AfterUpdate' event.

Private Sub JobTitleName_AfterUpdate()
Me.LOP = DLookup("[Job Codes]", "[LOP]", "[JobTitleName] = '" &
Me.JobTitleName & "'")
End Sub