Sum of records in a text box

[umair434]

Hi,

I have a text box which shows the sum of numbers for today - the text box is located in a form which is based on a query.

I want a pop up message to appear if the sum of records is zero (meaning no entries were added). My code works for a simple (empty) text box, but doesn't work if I use =SUM([Source]) as the control source of the text box:

Here is the code im using:

If IsNull(Me.Text2) Then
MsgBox ("No records have been added today!")
End If


Also, what's the difference between using Me.text2 and Me![Text2]

thanks guys!

 

[umair434]

I used

If Me.text2 = "" Then
MsgBox "No records have been added"
End If

but that displays the Msgbox everytime (no matter values are in the text box or not)

:S

 

[vbaInet]

If Len(Me.text2 & vbNullString) = 0 Then
    Msgbox "No records have been added"
End If

 

[umair434]

Hi - that displays the msgbox even if the sum is not 0 :S

 

[vbaInet]

You are firing the code at the wrong time or you are referring to a different textbox. Let's have a look at your db.

 

[umair434]

I have uploaded a stripped version. Thank you for your time.

basically, I want an alert system which pops up when the user opens the application. It will tell them whether records have been added today or not - If added, what are the values - and the last time a report was actioned.

Any suggestions to approach this will be highly appreciated

thanks!

 

[vbaInet]

Use a DSum() in the Load event of the form.

If Nz(DSum("[COLOR=Red]FieldName[/COLOR]", "[COLOR=Red]TableName[/COLOR]", "[COLOR=Red]DateField[/COLOR]=Date()"), 0) = 0 Then
    Msgbox ...
End If

Amend the bits in red.

 

[umair434]

Private Sub Form_Load()

If Nz(DSum("Email SPP Actioned", "Copy of tblActivities", "dte=Date()"), 0) = 0 Then
MsgBox ("SPPs have NOT been Actioned today!")

End If
End Sub


this gives an error saying "missing bracket"

 

[vbaInet]

That was just code I wrote on here so it wasn't tested. The missing parens is here:

"dte=Date()"), 0)) = 0

 

[umair434]

It still gives an error :S

If Nz(DSum("Email SPP Actioned", "Copy of tblActivities", "dte=Date()"), 0)) = 0 Then

 

[vbaInet]

Actually, the first code is correct, so go back to that.

What is the exact error message.

 

[umair434]

Runtime Error '3075'

Syntax Error (Missing operator) in query expression 'Sum(Email SPP Actioned)'

 

[vbaInet]

If Nz(DSum("[Email SPP Actioned]", "Copy of tblActivities", "dte=Date()"), 0) = 0 Then

 

[umair434]

Phew! these small things sometimes. thank you for being so patient

 

[vbaInet]

You're welcome!