updating field

[sades1]

hi,
in my database ive got a susbcription limit for each subscriber and every claim the subscriber makes is added onto a total claims field. if the total claims<sub. limit i want to make the 'status' field show 'reimbursed' for the particular claim. and if total claims>sub limit i want it to show 'rejected'.......how can i make this update?????

please help,
SaDeS

 

[JoanneM]

SaDeS
by using an immediate if statement

iif(total claims < sub.limit ,me!status = &quot;Reimbursed&quot;,&quot;rejected&quot

For precised syntax check out iif in help but this should do
help


regards

Jo

 

[sades1]

tnx for the help!

 

[sades1]

hi......i tried making this work today but i get a syntax error message........
iif(total claims < sub.limit ,me!status = &quot;Reimbursed&quot;,&quot;rejected&quot

it points at the '=' and says &quot;expected: =&quot;

what do i do?????

 

[JoanneM]

Hi SaDes

iif([total claims] < sub.limit ,me!status = &quot;Reimbursed&quot;,me!status = &quot;rejected&quot

I think it was the false part that needs the '='
After re reading I'm not to sure about the sub.limit but I'm taking that as how you refer to it.

Any probs I am around for a while

Jo

 

[sades1]

hi...this is exactly what i wrote-
Iif ((subs![total value of reimbursements] + claims![reimbursement value]) > subs![reimbursement limit],subs!status= &quot;reimbursed&quot;,&quot;rejected&quot
it wasnt as direct as the version i was given by you ppl...
i need to add 'total value of reimbursements' which is in the table 'subs' to 'reimbursement value' which is in the table 'claims' . if this value is greater than 'reimbursement limit' which is in the subs table i want to change the status to reimbursed........

is what i have done wrong??? will i need to do the addition outside this bit of coding and save it as a different field and check if its greater than the reimbursement limit?????if so how will i be able to do this???? .....im a major amateur at this...so please help me!!!!!!!

sades

 

[JoanneM]

Sorry you had a wait before I responded but the email only just landed

I'm Re reading your requirement to make sure I understand -

check back about 10:30 and pick it up I think that should be as much as I need
regards


Jo

 

[JoanneM]

This time I've included the sum function so lets see how it goes


iif(sum(subs![total value of reimbursements] + claims![reimbursement value])) > subs![reimbursement limit],subs!status= &quot;reimbursed&quot;,subs!status = &quot;rejected&quot

regards

Jo

 

[JoanneM]

I had one too many closing ) with the sum so I've Redone it


iif(sum(subs![total value of reimbursements] + claims![reimbursement value]) > subs![reimbursement limit],subs!status = &quot;reimbursed&quot;,subs!status = &quot;rejected&quot

regards

Jo

 

[sades1]

hey,

thanks a lot for all the trouble you're going thru......u people r real angels!!!!!!!

i'll check it out and report back
SaDeS

 

[sades1]

i still keep getting the same error!!!!!!

Private Sub saveclaim4_click()
subs![total value of claims] = subs![total value of claims] + claims![total claim value]
iif(sum(subs![total value of reimbursements] + claims![reimbursement value]) > subs![reimbursement limit],subs!status = &quot;reimbursed&quot;,subs!status = &quot;rejected&quot

End Sub

is what ive written.......i wanna run this code when the save button is clicked on the 'claims4' form.

 

[JoanneM]

Are these all txt boxes on a form holding currency data?

 

[sades1]

yep theyre all text boxes...do they have to be currency?because ive just specified 'number' is this the problem??

sades

 

[JoanneM]

No but me somewhere along the line with the Sub! & Claim!
got it into my head we were in a query.

Have you tried the.... code I posted earlier which I've just seen isn't here using Me!

Me![total value of claims] = Me![total value of claims] + Me![total claim value]
iif(sum(me![total value of reimbursements] + me![reimbursement value]) > me![reimbursement limit],me!status = &quot;reimbursed&quot;,me!status = &quot;rejected&quot

I don't know where that one went to but here is the me! statement
For info ME! refers to the active object in this instance the form so whatever you've named your textboxes fill them in appropriately and try it.


regards

Jo

 

[sades1]

hehe.......cool but now uve put me!s everywhere...some of the data used in this thing arent actually used in this form so the program has to get it off the tables and tehn use it.....
the only thing that is on this form is 'reimbursement value'. the 'total value of reimbursements' and 'reimbursement limit' are both on the 'subs' table. and teh field 'status' that needs to be updated here is on the 'claims' table.....

i know i appear to be very thick here and am probably being a lot of trouble to you......all your help is greatly appreciated.....

tnx,
SaDeS

 

[JoanneM]

Hi there


I'm just doing my MCSD 70-100 course pt iv could you give me an hour and I'll be back with you

regards

jo

ps I do this for fun & I don't think you're dumb - In 1.1 my boss took 4 hours to discover the single finger moved a single control ( he also thought you could develop and implement a database each Wednesday afternoon as well!!) - That's DUMB

 

[sades1]

hehe.....ok i have no idea what MCSD stands for......is this an undergraduate thing or post?
i'm thinking of doing computer engineering at college next year...but the amount of programming involved is making me have second thoughts......neways......help me!!!!!!!im in real deep trouble......i cant fighre out why this thing doesnt work......it says its a syntax error when i click 'debug'.....

tnx,
sades

 

[sades1]

well i still havent solved this problem.......
are there any other ways this can be done?
heres a summary of what needs to be done
the [total value of claims] which is in the 'claims' table and is a 'number' field needs to be added to the [total claim value] which is entered in the particular form this code is associated with and is saved as [total claim value] in the 'claims table]. adding this gives the new [total value of claims] field.....(ie this is just updating this field).

the [total value of reimbursements] which is a number field in the 'subs' table needs to be added to [reimbursement value] which is calculated by using the [total claim value] entered in this form by the user on another part of the coding(this calculation code works).

if the value we get when we add these two fields (total value of reimbursements and reimbursement value) is less than [reimbursement limit] which is a number field in the 'subs' table the [status] field which is on the 'claims' table should = 'reimbursed' (this field is a text field).

if the value is greater than [reimbursement limit] then the [status] field should become 'rejected'.......

thats it......is there any way this could be done???

help!

SaDeS