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

 

[bcoats]

If your form control source is a querry, you can't update any of the data. That may be you problem.

 

[Krash878]

Everything is based off of Tables.
As I type in the RSN code I want the fee feild to populate with the correct amount that is designated in a different Table.

 

[bcoats]

I think I see what you are doing now. Sorry about that other one. I would try using a recordset to retrieve the info from the other table. Sort of like this.

SQLString = "select letter from RSNCodes where Rsn = " & control.value & ";"
set db = currentdb()
set Rec = bd.OpenRecordset(SQLString, dbOpenSnapshot)
SQLString = "select ammount from Letter_to_Ammount where Letter = " & Rec!letter & ";"
Rec2 = db.OpenRecordset(SQLString, dbOpenSnspshot)
' the ammount should be in Rec2!ammount now
' so set the field in tblChargeBack to ammount
' if your form is connected to the table just type in
' the fieldname = ammount like this
<fieldname> = Rec2!ammount

This should get the correct ammount from the RSN Code you entered. Sometimes Joins and Linking Muddy the waters and it is just easier to go in the backdoor. It is normally unlocked, or you can pay the doorman.

Brian

 

[Krash878]

SQLString = &quot;select letter from RSNCodes where Rsn = &quot; & control.value & &quot;;&quot;
set db = currentdb()
set Rec = bd.OpenRecordset(SQLString, dbOpenSnapshot)
SQLString = &quot;select ammount from Letter_to_Ammount where Letter = &quot; & Rec!letter & &quot;;&quot;
Rec2 = db.OpenRecordset(SQLString, dbOpenSnspshot)

ok I am a little confused

1. Is the &quot;select letter from RSNCodes where RSN = &quot; & control.value & &quot;;&quot; Valid or is there something that I should change?
2.On line 4 what should I change.

Lets try this

Any where that I should change something put a &quot;~&quot; infront and behind it.
like
strTemp = Me![TextBox]changes to strTemp = Me![~TextBox~]

thanks I am still learning and still do not know the basics of code

 

[bcoats]

OK, understand that I don't know what your table names are so the code might not be valid in your program.
The code written the way it is is because when building a string you have to append (&) things to your text to get your variable names in there, like ...&quot; & Rec!letter & &quot;...
You have to strive to make the code you build look exactly like working SQL code. to check this try msgbox SQLString to see what you have built. I often go to the querry tab in the database window to make the querry in SQL view and then put it in my code. To let you know what I was trying to do here, let me tell you what my names meant.

RSNCodes Table = RSN Letter
438 A
439 B

Letter_to_Ammount = Letter Ammount
A 80.00
B 90.00

etc. You might have to change the names to fit your tables.
Also control.value should be whatever you control name that has the RSN value input into it. like RSNinput.value
Other changes should have th ~ pre and post change


SQLString = &quot;select letter from RSNCodes where Rsn = ~&quot;&quot;~&quot; & control.value & ~&quot;&quot;~&quot;;&quot;
set db = currentdb()
set Rec = ~db~.OpenRecordset(SQLString, dbOpenSnapshot)
SQLString = &quot;select ammount from Letter_to_Ammount where Letter = &quot;~&quot;&quot;~ & Rec!letter & ~&quot;&quot;~&quot;;&quot;
Rec2 = db.OpenRecordset(SQLString, dbOpenSnspshot)
' the ammount should be in Rec2!ammount now
' so set the field in tblChargeBack to ammount
' if your form is connected to the table just type in
' the fieldname = ammount like this
<fieldname> = Rec2!ammount

The above changes should make it work. However I am not sure that the ~&quot;&quot;~'s should be there. I know they have to be there around letters but not sure about numbers. The reason for them is so that you can have a single quote (&quot in a string, since the (&quot ends the string. I hope this helps. Fell free to post further questions, I am happy to help.

Brian

 

[Krash878]

3 tables and 1 form

The main table (tblChargeBack) has a &quot;CBRSN&quot; and &quot;Fee&quot; field along with others that are no concern here.

The next table is (tblCBCodes) where the definitions for the RSN codes is. It has 3 fields: &quot;RSNCode&quot;,&quot;Description&quot;, and &quot;CBFee&quot;

The last table (CBFee) has 2 fields &quot;FeeCode&quot; and &quot;FeeAmnt&quot;

note:
In the realtionships &quot;CBRSN&quot; and &quot;RSNCode&quot; are related and &quot;CBFee&quot; and &quot;FeeCode&quot; are related

I hope this helps I am still confused.

The database is called &quot;ChargeBack&quot;
Thanks
Kenny

 

[bcoats]

What I am hearing is this. You want to retrieve the FeeAmnt that coresponds to the CBRSN entered by the user. The realationships dont matter here. Think of them as three separate entities. The though process I am going through is this: Get the RSN from the user, retrieve the CBFee that coresponds to that RSN from tblCBCodes, then use that CBFee to find the coresponding FeeAmnt from tblCBFee.

Try this just like it is typed here.

dim SQLString as string
dim db as database
dim Rec as recordset
dim Rec2 as Recordset

SQLstring = &quot;select CBFee from tblCBCodes where &quot; & _
&quot;RSNCode = &quot;&quot;&quot; & CBRSN.value & &quot;&quot;&quot;;&quot;
set db = currentdb()
set Rec = db.OpenRecordset(SQLString, dbOpenSnapshot)
SQLString = &quot;select FeeAmnt from tblCBFee where &quot; & _
&quot;FeeCode = &quot;&quot;&quot; & Rec!CBFee & &quot;&quot;&quot;;&quot;
tblChargeBack!fee = Rec2!CBFee

this should do it. If you are displayin the Fee field in on the form, you can set that fields value to FeeAmnt and it will be stored in the database. Do that like this if you control is also named fee.

fee.value = Rec2!FeeAmnt

Also, in the code where CBRSN.value is, if you aren't displaying this field or the control has a different name you are going to have to change it. Let me know if you have any other questions.

Brian

 

[Krash878]

ok 2 problems still

On the &quot;fee.value = Rec2!FeeAmnt&quot; Where is this placed in the Properties box

and the big problem. I am in Access 2000 and it does not like the
&quot;Dim db As DataBase&quot;
Is there a conversion somewhere I could use?
Thanks
Kenny

 

[bcoats]

I don't know about the declaration in access 2000. I have not used that yet. I would just look through the possible data types for a database compatible property. Maybe you don't need this in 2000.

About this and the fee.value statement:
I am working under the assumption that you are coding this in the form module window and not entering this all in a property box. Put this in the after update method for the control and set the after update property to event procedure. I don't think this will work at all just placed in the property box. Let me know if you have trouble placing the code in themethods window.

Brian

 

[bcoats]

i think I may have found something else for you to try. Try

dim db as dao.database

I think this might still be in 2000

Brian

 

[Krash878]

ok I figured out why it would not take Database but it works now. I had go to modules and select tools then references and tell it that I wanted it to use DOA 6.3



Any way my code reads

Private Sub CBRSN_AfterUpdate()
Dim SQLString As String
Dim MyDB As Database
Dim Rec As Recordset
Dim Rec2 As Recordset

SQLString = &quot;select CBFee from tblCBCodes where &quot; & _
&quot;RSNCode = &quot;&quot;&quot; & CBRSN.Value & &quot;&quot;&quot;;&quot;
Set MyDB = CurrentDb()
*Set Rec = MyDB.OpenRecordset(SQLString, dbOpenSnapshot)*
SQLString = &quot;select FeeAmnt from tblCBFee where &quot; & _
&quot;FeeCode = &quot;&quot;&quot; & Rec!CBFee & &quot;&quot;&quot;;&quot;
tblChargeBack!fee = Rec2!CBFee


The line where the stars are is where I am getting a compile error Type Mismatch

 

[Krash878]

I am getting stuck again.

This is my code and it gets stuck at set rec

Private Sub CBRSN_AfterUpdate()

Dim SQLString As String
Dim db As Database
Dim Rec As Recordset
Dim Rec2 As Recordset

SQLString = &quot;SELECT tblCBFee.FeeAmnt FROM tblCBCodes INNER JOIN tblCBFee ON tblCBCodes.CBFee = tblCBFee.FeeCode WHERE tblCBCodes.RSNCode=&quot;&quot;&quot; & CBRSN & &quot;&quot;
Set db = CurrentDb()
Set Rec = db.OpenRecordset(SQLString, dbOpenSnapshot)
SQLString = &quot;select FeeAmnt from tblCBFee where &quot; & _
&quot;FeeCode = &quot;&quot;&quot; & Rec!CBFee & &quot;&quot;&quot;;&quot;
tblChargeBack!fee = Rec2!CBFee

 

[bcoats]

Same drill as before with the ~. One question, is it getting stuck in declaration of Rec or the Set Rec line? If it is on the set rec line I think I might have spoted the problem.

SQLString = &quot;SELECT tblCBFee.FeeAmnt FROM tblCBCodes INNER JOIN tblCBFee ON tblCBCodes.CBFee = tblCBFee.FeeCode WHERE tblCBCodes.RSNCode=&quot;&quot;&quot; & CBRSN & &quot;&quot;~&quot;;&quot;~

Looks like you forgot the semicolon and the closing Quote. I don't even see how this got through the line compiler. Also, pertaining to you previous post whet you were gettind a type mismatch, the ; was tehre so I think it might be better for yu to just let the dbOpenSnapshot be nothing like this:

Set Rec = db.OpenRecordset(SQLString)

This will default the opening type to, I think, dynaset. The difference is that you can change the data in a dynaset but not in a snapshot. Let me know if you are still having problems.

Brian

 

[Krash878]

What type of info needs to be between the ~?

 

[bcoats]

I think the end of the line should have looked like this.

& CBRSN & &quot;&quot;&quot;;&quot;

The reason is that SQL queries have to end with a ; and the CBRSN has to be surrounded by quotes. And when building strings, you have to double quote in order to have a single quote in your string and after CBRSN you have one quote to begin the rest of the string, two to get the single quote after the CBRSN, then the ; then the last single quote to end the string.

I hope this fixes your problem.

Brian

 

[Krash878]

ok my code looks like this and I am stii getting a Run-Time error '13':
Type Mismatch

Private Sub CBRSN_AfterUpdate()

Dim SQLString As String
Dim db As Database
Dim Rec As Recordset
Dim Rec2 As Recordset

SQLString = &quot;SELECT tblCBFee.FeeAmnt FROM tblCBCodes INNER JOIN tblCBFee ON tblCBCodes.CBFee = tblCBFee.FeeCode WHERE tblCBCodes.RSNCode=&quot;&quot;&quot; & CBRSN & &quot;&quot;&quot;;&quot;
Set db = CurrentDb()
Set Rec = db.OpenRecordset(SQLString)
SQLString = &quot;select FeeAmnt from tblCBFee where &quot; & _
&quot;FeeCode = &quot;&quot;&quot; & Rec!CBFee & &quot;&quot;&quot;;&quot;
tblChargeBack!fee = Rec2!CBFee

 

[bcoats]

try replacing CBRSN in the SQL querry with CBRSN.value That way it should get the Data from the text field on the form. That might be you problem. Also if you have the data type in the form set up as number you might not need the quotes around the value in the comparison so that it would look like this:

= &quot; & CBRSN.value & &quot;;&quot;

I am not sure if you need the quotes or not. If it is looking for a number and you are getting your value from a text box though you might have to convert the number over to integer or single using the cInt(value) or cSng(value) functions. look up type conversions in the online help to find out if they have changed in 2000.
Try these things and let me know what happens.

Brian

 

[Krash878]

no good.
Should I send it to you and have you look at it?

 

[bcoats]

I don't have 2000 though and won't be able to load it I don't think and the compilers are different.

It is worth a try though. bcoats@entergy.com if you would like to send it to me so I can fiddle with it. No promises though.

Brian