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