• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Error in DSum textbox while on new record

I have a form in datasheet view where the user chooses a few items to buy. I then have a textbox that calculates the total price of those items. The control source of the Totals textbox is as follows:

=DSum("([Price]+([Price]*[Adjust]))*[Quantity]","QuoteDetails","[QuoteID] =" & [QuoteID] & "AND [Optional]=0")

The problem is that when I am on the form and the cursor is in one of the fields for a new record, the Totals textbox shows #Error. Why is this happening and what can I do to prevent this #Error from showing up when the user is selecting a new item?
Thank you for your help.
0
cers4686
Asked:
cers4686
1 Solution
 
Rey Obrero (Capricorn1)Commented:


=DSum("(nz([Price],0)+(nz([Price],0)*nz([Adjust],0)))*nz([Quantity],0)","QuoteDetails","[QuoteID] =" & [QuoteID] & " AND [Optional]=0")
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

try this

=IIF(Not IsNull([YourPrimaryKeyFieldName],   DSum("([Price]+([Price]*[Adjust]))*[Quantity]","QuoteDetails","[QuoteID] =" & [QuoteID] & "AND [Optional]=0") , Null)

Replace YourPrimaryKeyFieldName  with the primary key field name - or some field you can guarantee will not be Null IF if you are on a record (not on a New record)

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

Or use the function attached like so:

=AvoidError(DSum("([Price]+([Price]*[Adjust]))*[Quantity]","QuoteDetails","[QuoteID] =" & [QuoteID] & "AND [Optional]=0"))


Function AvoidError(n As Variant, varReplaceWith As Variant)
         
    On Error GoTo AvoidError_Error
    
    AvoidError = Nz(n, varReplaceWith)
    
AvoidError_Exit:
    Exit Function
 
AvoidError_Error:
    AvoidError = varReplaceWith
    Resume AvoidError_Exit
    
End Function

Open in new window

0
 
cers4686Author Commented:
brilliant! thank you!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome ...

mx
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now