my for next loop is not working and gives error94

Cannot understand why my for and next loop is not working. What I am trying to do is to print a report for the same number of records details depending on the number value of a particular field called [nts].

If the field is empty it tells me I have error: 94. If the field has a value it goes to errorhandler mention in the program. Can any body be so kind and help me please? Thank you in advance :)** I am using ACCESS2007

Option Compare Database
Option Explicit

Private Sub Report_Close()

' Delete previous data from tabMeal

    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete from tabAmeal"
End Sub

Private Sub Report_Load()

Dim intNOM As Integer  
'NOM means number of nights meals

Dim mTimes As Integer

On Error GoTo errorhandler

intNOM = 1

mTimes = 0

mTimes = DLookup("nz([nts],0)", "tabAmeal", "[nts] > 0") 

'mTimes means number of meals

If mTimes = 0 Then

    MsgBox "File is empty GO to Query" & vbCrLf & "Error - Run the Query", vbQuestion

Else 

'now print the information found in table "tabmeal" number of times depending on the value field "nts"

    For intNOM = 1 To mTimes
    DoCmd.OpenReport "repAmeal?", acViewPreview
    Next intNOM
End If

errorhandler:

    MsgBox "Error #:- " & Err.Number & vbCrLf & "LOOP not working" & vbCrLf & "Must find why this error" & vbCrLf & Err.Description

End Sub

Private Sub Report_NoData(Cancel As Integer)

    MsgBox "Please note that you have no records to report." & vbCrLf & "You have to run the QUERY to get the required informatio."

End Sub

Answers


The first problem: Nz() must be used outside of DLookup, not inside:

mTimes = Nz(DLookup("[nts]", "tabAmeal", "[nts] > 0"), 0)

The second problem, I'm not sure. You can't open a report multiple times in acViewPreview (it will open only once), but it shouldn't give an error.

What is the exact error message you get when nts > 0 ?

Oops. You need Exit Sub befor your errorhandler: line - the code simply enters the error handler, without there being an error. :)


Need Your Help

Getting data from url hash in javascript

javascript regex url hash

I am trying to read the "source" parameter and the "group" parameter from the url hash below. I have no idea how to use regex and keep ending up with blank variables.

How to test functions speed in Visual Studio

c# performance testing visual-studio-2012

I would like to test how fast does my projects function work. It would be great if there were a possibility to mark slow places of my function as well so I can change my code to increase performanc...