Runtime error 2427 on opening report

  • Thread starter Thread starter john ierston via AccessMonster.com
  • Start date Start date
J

john ierston via AccessMonster.com

I have designed a basic d/base in access 97. On a form I can select a job
manager and month and then press a command button to run a report of
customers jobs for that selection. Where there are no jobs I get Runtime
error "you entered an expression that has no value. Here is the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Set the backstyle to normal (default is usually transparent)
YearEnd.BackStyle = 1

If Int((Now - YearEnd) > 516) Then
YearEnd.BackColor = vbYellow

Else
YearEnd.BackColor = vbWhite
End If
'Set the backstyle to normal (default is usually transparent)
Text24.BackStyle = 1

If Int((Now - YearEnd) > 516) Then
Text24.BackColor = vbYellow

Else
Text24.BackColor = vbWhite
End If
End Sub


The purpose of the code is to highlight in yellow any date older than 516
days and associated client name (text 24).
When I go to debug it highlights the line "If Int((Now - YearEnd) > 516)
Then".
I cannot seem to crack the problem . Any help very gratefully appreciated.
I am very much a novice! Thanks John
 
Hi John,

The error is occurring because you have no data for the report, and as a
result the formula Int((Now - YearEnd) will not work. To prevent this add
some logic to the On No Data event of the report that will display a message
on screen that there is no data, whatever, and return the user to the form.

Aslo, I would consolidate the If statements you have to use only one as
follows....

YearEnd.BackStyle = 1
Text24.BackStyle = 1

If Int((Now - YearEnd) > 516) Then
YearEnd.BackColor = vbYellow
Text24.BackColor = vbYellow
Else
YearEnd.BackColor = vbWhite
Text24.BackColor = vbWhite
End If


Jim
 
Why not just catch the error:

On Error Resume Next
....
If Err.Number = 2427 Then
MsgBox "No Records..."
Exit Sub
End If
....

Hth
PerL
 
Hi Jim, Thanks very much for this.
Being a bit green about code, could you possibly suggest what to put in the
On No Data event-I have tried but obviously doing something wrong.
really grateful for the help
Cheers
John
 
Dear Per,
Thanks for your message. I cannot seem to get this to work - am probably
putting the code in the wrong place! Does it fit in the code I posted? Put
another way, would really appreciate it if you could tell me how to slot it
into the code I posted. Much appreciated!
Thanks
John
 
It seems like using the NoData event is a simpler solution in this case:

Private Sub Report_NoData(ByRef Cancel As Integer)

MsgBox "NO DATA"
Cancel = True '// Exit report

End Sub

hth
PerL
 
Per
Thanks very much for this.
All works now. YOu're brilliant!
Thanks again
regards
John
 
Back
Top