Am trying to write a macro to calculate some values, but for some reason am gettin an Automation Object Error (screenshots attached).

I even tried to make a 'RemainingDeposit' field, in my table, and I still get the same error. Any ideas? Need help ASAP.
Thanks in advance


http://img246.imageshack.us/img246/2540/macrop.th.png (http://img246.imageshack.us/i/macrop.png/)
http://img514.imageshack.us/img514/5531/erroro.th.png (http://img514.imageshack.us/i/erroro.png/)

I would strongly recommend you did this via a public function as you have more flexibility over it.

What you could do is convert your macro to VB and lookk at what it is doing.

David

never actually used public function etc etc.. only generated ones using the code builder after events.. how do you call the function?

is it something like

public function something()
code
code
end function

and call it from the event?

here's the generated VBA code..



Function Deposit_Deposit()
On Error GoTo Deposit_Deposit_Err

With CodeContextObject
If (.[Deposit Used] = 0) Then
Beep
MsgBox "Deposit will be used since this is the 1st time a Payment Request is generated", vbInformation, ""
End If
TempVars.Add "RemainingDeposit", "0"
If (.Deposit > .[Deposit Used]) Then
.RemainingDeposit = .Deposit - .[Deposit Used]
End If
If (.RemainingDeposit > .totPriceIncVAT) Then
.[Deposit Used] = .RemainingDeposit - .totPriceIncVAT
End If
.[Deposit Depleted] = False
If (.totPriceIncVAT >= .RemainingDeposit) Then
.[Deposit Used] = .RemainingDeposit
End If
.[Deposit Depleted] = True
.Deposit = 0
End With


Deposit_Deposit_Exit:
Exit Function

Deposit_Deposit_Err:
MsgBox Error$
Resume Deposit_Deposit_Exit

End Function

i've scrapped most of the code generated, and tried re-writting it my self.

Unfortunately, am gettin errors when the code is executed.

"Application Defined or object-defined error"


Function Deposit_Deposit()
On Error GoTo Deposit_Deposit_Err

With CodeContextObject
Dim RemainingDeposit As Long

If (.[Deposit] <= "0") And (.[Deposit Used] <= No) Then
.[Deposit Depleted] = Yes
Else

RemainingDeposit = .Deposit - .[Deposit Used]
If (.[Deposit Depleted] = No) And (.[Deposit Used] = "0") And (.[Deposit] > "0") Then
Beep
MsgBox "Deposit will be used since this is the 1st time a Payment Request is generated", vbInformation, ""
End If


If (.totPriceIncVAT > .Deposit) And (.[Deposit Used] = "0") Then
RemainingDeposit = "0"
.[Remaining Deposit] = "0"
.[Deposit Used] = .Deposit
.[Deposit Depleted] = Yes
End If

If (.totPriceIncVAT < .Deposit) And (.[Deposit Used] < .Deposit) And (.totPriceIncVAT + .[Deposit Used] < .Deposit) Then
.[Remaining Deposit] = .Deposit - .[Deposit Used] - .totPriceInvVAT
.[Deposit Used] = .totPriceIncVAT + .[Deposit Used]
.[Deposit Depleted] = No
End If


If (.totPriceIncVAT <= .Deposit) And (.[Deposit Used] < .Deposit) And (.totPriceIncVAT + .[Deposit Used] > .Deposit) Then
.[Remaining Deposit] = "0"
.[Deposit Used] = .Deposit
.[Deposit Depleted] = Yes
End If
End If
End With


Deposit_Deposit_Exit:
Exit Function

Deposit_Deposit_Err:
MsgBox Error$
Resume Deposit_Deposit_Exit

End Function


As I have not written much VBA my self, am not sure whether Long need "", or whether Yes/No fields need one. I guess that's the problem here, but am just guessing.

Can someone help me out here?

Function CalcRemainingDeposit(Deposit As Integer,DepositUsed As String,DepositDepleted As Boolean,TotPriceIncVAT As Double)
On Error GoTo Deposit_Deposit_Err

Dim RemainingDeposit As Long

If Deposit <= 0 And DepositUsed <= "No" Then
Me.[Deposit Depleted] = Yes
Else

RemainingDeposit = Deposit - DepositUsed

If (DepositDepleted = False And DepositUsed = 0 And Deposit > 0 Then
Beep
MsgBox "Deposit will be used since this is the 1st time a Payment Request is generated", vbInformation, ""
End If


If TotPriceIncVAT > Deposit And DepositUsed = 0 Then
RemainingDeposit = 0
Me.[Deposit Used] = .Deposit
Me.[Deposit Depleted] = Yes
End If

If totPriceIncVAT < Deposit And DepositUsed < Deposit And totPriceIncVAT + DepositUsed < Deposit Then
Me.[Remaining Deposit] = (Deposit - DepositUsed) - totPriceInvVAT
Me.[Deposit Used] = totPriceIncVAT + DepositUsed
Me.[Deposit Depleted] = No
End If


If totPriceIncVAT <= Deposit And DepositUsed < Deposit And totPriceIncVAT + DepositUsed > Deposit Then
Me.[Remaining Deposit] = 0
Me.[Deposit Used] = .Deposit
Me.[Deposit Depleted] = Yes
End If
End If
End With


Deposit_Deposit_Exit:
Exit Function

Deposit_Deposit_Err:
MsgBox Error$
Resume Deposit_Deposit_Exit

End Function

The above modifed code might not be exactly right but what you need to do is to pass the values of certain fields to the function by way of a call statement. This would be done at some significant point on the form

Call CalcRemainingDepoist(Me.Depoist,Me.DepositUsed,Me. DepositDepleted,Me.TotinvIncVAT)

If you save the function inside the form declarations you will be able to update the fields accordingly.

David

So, let me get this right.. What you're saying is turning the field values into variables, use those for any calculations, and after all is done, replace the field values with the variables..

I get an "invalid or unqualified reference" error on this line:

Function CalcRemainingDeposit(Deposit As Integer, DepositUsed As String, DepositDepleted As Boolean, totPriceIncVAT As Double)

What version of Access are you using?
did you cut and paste the code?
Have you tried to recreate the line in a new function?
Have you checked Tools > References for missing references?

David