VBA to DoCmd.RunSQL Only Once Per Record (1 Viewer)

[bmcgree1]

Hi everyone,
I have a little problem. So i'm working on a DB where it keeps track of loans and if a balance is still unpaid after 10 days then a late fee of $100 is added to it. The code in red below I have running on Open of the form and it works but obviously it updates EVERY time you open the form.
What I need it to do is update, but update only once per record. Basically:
If (Record hasn't been updated before) Then

If Me.[DateDue] < DateSerial(Year(Date), Month(Date), Day(Date) - 10) And Me.Balance > 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE [Tbl_Payment] SET [Tbl_Payment].[Balance] = [Tbl_Payment].[Balance] +100 WHERE (((Tbl_Payment.Loanid)=forms!Frm_OutstandingBalances.loanid));")
DoCmd.SetWarnings True
Else
'Nothing
End If

Else (skip record)
End If

That would be nice but how would I do this?? Thank anyone in advance for their help!

P.S. - I also have a PaymentID field in tbl_Payment that's unique if we could somehow use that to say WHERE PaymentID = Updated Once or something like that.....

 

[BamaColtsFan]

I'd say you need to add a column to your table to store a true/false value once the late fee is assessed. The deafult value would be false; add a condition to your IF to check the value and then set it to True once you apply the fee.

Just my $100 worth...

 

[The_Doc_Man]

Actually, though BamaColtsFan is right, there is even a better way to do this.

If the late fee is assessed only through dates, not through someone's action, then what you do is write a QUERY that includes a check on the date to see if a late fee is involved.

There might be several ways to do this. For instance, in the LATEFEE column, put an IIF function that checks for age and has as its TRUE option, 100.0, and as its FALSE option, 0.0. This query field would look like =IIF(condition, 100.0, 0.0) AS LATEFEE

Then have a field that equals the actual charge plus the late fee, as =[RealFee]+LateFee AS TOTALFEE.

Then base whatever you are running off the query.

Now, if there are conditions under which the late fee would optionally NOT be imposed, then the idea of a check box saying "LATE FEE HAS BEEN IMPOSED" and using a query with =IIF(LateFeeBox, 100.0, 0.0) AS LATEFEE also works.

The reason I'm being vague is because only you know the details of your problem. I surely don't and therefore cannot tell you which way is right.

 

[bmcgree1]

thank you, both of you.

I tried what bama suggested and it worked great, I just saw doc's posting when I went to reply and i'm going to try that as well. thanks again guys!

 

[gemma-the-husky]

just a few thoughts, as there are a few complexities with this

personally, i would be inclined to store the VALUE of the late charge rather than a flag, and probably some comments along side

a) it might change form 100 at some point
b) you WILL agree with a disgruntled customer at some point to discount the charge- when he offers you 50 to settle
c) definitely store it separately from the main charge - but
d) - consider how it affects any sales tax you might need to levy/account for.
e) - also consider how you print out/report your invoice documentation, as now you have an extra field.

probably other practical issues as well, when you start going

 

[bmcgree1]

Youre absolutely right, it could change from client to client, thank you. But now I'm having another problem with my code, even though there is an If statement that explictly states what I want it to look for, every record in my table is being updated.
The data doesn't even fit my If statement but it's still adding 100 to each and every latefee record.

If Me.[DateDue] < DateSerial(Year(Date), Month(Date), Day(Date) - 10) And Me.Balance > 0 And Me.[LateFee] = 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE [Tbl_Payment] SET [Tbl_Payment].[LateFee] = 100;")
DoCmd.SetWarnings True

Tbl_Payment.LateFee = Currency field now, with the default as 0.

Is there anything you can pick out that i'm just flat out doing wrong? Because I have no idea. Thank you!

 

[gemma-the-husky]

this needs to look like

update table set field where somecondition
the where in this case, being identifying the record you want to change


DoCmd.RunSQL ("UPDATE [Tbl_Payment] SET [Tbl_Payment].[LateFee] = 100 where keyfield = " & keyvalue)

note that the syntax is slightly different for numbers, compared with text.

the keyfield/keyvalue is whatever identifies the selected record


---------
note that if you are actually editing the selectedrecord, and you have a control (textbox) (visible or not visible) for the field you can just say

myfield = 100, rather than updating the record. Indeed if you have currently changed anything in the record manually, so the record is dirty, I am pretty sure that access will have a problem when it comes to saving the changes, as the underlying record will have changed - and you will get the "another user has edited the record" message

 

[bmcgree1]

Got it. Thank you very much, I got too frustrated and now I am again for trying to make it so complicated! Thank you much. Good day