one form and a table that gets data from another table/tables

[Krash878]

I have a form that writes to a Chargeback Table (tblChargeBack). I have two more tables that are linked to each other.

brief discription:
The user types in a RSN Code, The code is related to the codes in the tblCode, each code has a letter asigned to it, each letter is asigned to a dollar amount.
example
RSN
456 = A
457 = B
458 = A


RSN = A = $0.00
B = $3.00
C = $8.00
D = $12.00
E = $20.00

The RSN Code is paired with the letter that corresponds with it in one table and the letter is paired with it's dolar amount in a table.

I need to have my control that writes to "RSN" in the after update look at the tables and write the dollar amount to the "Fee" field in tblChargeBack.

This must be done for history purposes.

If I let the querys take care of it then I will have data that is from a year ago look like it has the same fees as today though the fee has changed.

Thanks for the help

 

[Krash878]

ok I figured it out and it works now.

the problem was In Access 2000 there are different sets of dictionaries for the code. I went and set the right dictionary for the code I was using (DAO 3.6)

The reason it still was not working is because the priorty was too low on the list. I moved it up and bing there it was.

Also on the form I did have a fee field. The final code is as follows

Private Sub CBRSN_AfterUpdate()

Dim MyDB As Database
Dim Rec As Recordset
Dim SQLString As String

SQLString = "SELECT tblCBFee.FeeAmnt FROM tblCBCodes INNER JOIN tblCBFee ON tblCBCodes.CBFee = tblCBFee.FeeCode WHERE tblCBCodes.RSNCode ='" & CBRSN & "'"
Set MyDB = CurrentDb()
Set Rec = MyDB.OpenRecordset(SQLString, dbOpenSnapshot)
fee = Rec!FeeAmnt




End Sub


Thanks for the help I do not think I would have figured it out with out the push.
Thanks again
Kenny

 

[bcoats]

You are welcome for the help. I am glad that you figured out the problem. I like to help people out with things like this. I am just glad that we finally zoned in on a method that worked. The thing I laways try to remember is that SQL can get really unweildy and if you are doing it in code anyway try to break it up into several littler problems that have easy SQL queries to write!!

BRian