Access VBA: Show a specific value in a text box when no source record exists

0
votes

This has been bugging me for a while now and I can't seem to find a way around it; I currently have a text box on a form (txt1) and the control source for this text box is a Sum() function linked to another text box (txt2) on another form. Txt1 totals up the values fine until there is a query run where there are no results on the form on which txt2 is situated; in this instance, because there are no records for txt1 to run the =Sum() function on, it simply returns #Error; since txt1 itself is then involved in a Sum function (the results for which are stored in txt3), txt3 returns the value #type due to txt1 being #Error. What I'd love to know is if there is a way to get txt1 to simply display "£0.00" if there are no arguments passed to it through the Sum() function.

Thanks.

1
vbams-access-2010

1 Answers

0
votes

There is a workaround:

Create a function in a public module, that checks if a form/report has any record:

Function HasRecords(ByVal xobj As Object) As Boolean
  '
  On Error GoTo ErrorState
  '
  HasRecords = (xobj.RecordsetClone.RecordCount > 0)
  '
  Exit Function
  '
ErrorState:
  HasRecords = False
End Function

Then in private module of the form that contains the control txt2, that sums up a field, for example, Quantity of a table/query,

Me.Controls("txt2").ControlSource = "=IIF(HasRecords(Form), SUM(Quantity), 0)"

As a result, txt2 has a value of 0 if there is no records, a sum if there is any.

As txt1 and txt3 depends on the value of txt2, error will not occur.