update 1 field from 2 others

[sue gray]

I have a table (tblcpt) similar to what I have listed below. I am having
trouble with a form. Basically I want to be able to select cptcode 345,
modifier TC, and the correct fee be put in a text box (fee) that is also
stored in the tblservices. I have some code attached. Also, I need a better
place to put the code, it doesn't work when I change the CPT code. Sorry if
I didn't explain well. THanks for any help.

tblservices
patientid
cptcode
modifier (which is TC or 26 or None)
fee

tblcpt layout
Cptcode TC 26 totfee

123 12 14 26
345 10 12 22
789 30
651 05 08 88
874 35

Private Sub modifier_AfterUpdate()
If Me!modifier = "TC" Then
Me!fee = tblcpt!TCfee
Else
If Me!modifier = "26" Then
Me!fee = tblcpt!twentysixfee
Else
If Me!modifier = "None" Then
Me!fee = tblcpt!totchargefee
End If
End If
End If
End Sub

 

[Mike Painter]

Even at this level bad table design will caouse problems
TC, 26, and fee are all the same kind of field and should be in a separate
related table.
"fee" might or might not be used. If at all possible it should not be used.
With related tables and a query, a combobox cn fill in the code and modifier
and things get easy.
When somebody says we want to add a 27, 28, and 30 modifier, you have only
to tell them how to use the form that fills in this information.

 

[sue gray]

Thanks for your response. Can you tell my how I should set up the table?
Here's an example of the data:

cptcode tc 26 total

99203 99.46
77057 51.64 38.10 89.74
99243 135.08
G0204 120.20 46.26 166.45

THanks again.

 

[Mike Painter]

Thanks for your response. Can you tell my how I should set up the
table? Here's an example of the data:

cptcode tc 26 total

99203 99.46
77057 51.64 38.10 89.74
99243 135.08
G0204 120.20 46.26 166.45

I would tend to use a two part key with
PriceCode and cptcode as the key, then all you need is the price.