Automation Object Problem? URGENT

[ombadboy]

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

 

[DCrake]

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

 

[ombadboy]

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?

 

[ombadboy]

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

 

[ombadboy]

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?

 

[DCrake]

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

 

[ombadboy]

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..

 

[ombadboy]

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)

 

[DCrake]

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