Set value for field if default gives #ERROR

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

Hello -

I have a form that sets the default value of field [BomRev] to the value
on another form as follows:

=[Forms]![Main Form]![f_order].[Form]![BomRevMax]+1

This of course only works if there is already a record in the table to
calculate BomRevMax against, otherwise the field shows #Error

I would like some help with the syntax for the following ON OPEN Event:

DoCmd.GoToRecord, , acNewRec

If IsError ([BomRev]) Then
[BOMREV]=0
Else
[BOMREV]=[Forms]![Main Form]![f_order].[Form]![BomRevMax]+1)

End If

I am getting a Run-time error '440':
Automation error

The highlight in the VB window is on the last line before the End If

Any help greatly appreciated.

thanks!
Sandra
 
Sandy,
try it like this

With [Forms]![Main Form]![f_order].[Form]
If IsNull(!BomRevMax) Then
[BOMREV] = 0
Else
[BOMREV] = !BomRevMax + 1
End If
End With


Jeanette Cunningham -- Melbourne Victoria Australia
 
Back
Top