Remove #Error from Unbound Textbox

[captain1974]

Access 2003.
I have added an unbound textbox to my (sub)form which displays a value based on the value in another bound textbox.
This works ok, but when first loading the form it displays #Error. It also displays #Error when there is no subform data for the record selected on the main form.

The value I want to return is based on a function I wrote which returns a year value from a date. (Eg if the date is between 1/7/06 and 30/6/07, the year 2007 is returned). The function works fine, but I need to stop the #Error message when there is no data.
So far I have tried (unsuccessfully):
1. Placing the function within a Iif statement (directly in the textbox)
2. Changing the function to allow for a Null
3. Adding code to the afterupdate event of the textbox

Option 3 leaves the textbox blank, so I think I may wildly wrong with my code:

Private Sub Text34_AfterUpdate(Vintage As Integer)
    Me.[Text34] = Vintage(Me.EventDate)
    Me.[Text34].Requery

End Sub

(Vintage is the name of my function, Text34 is the unbound textbox, the name of which will change if I get this to work)

Any ideas would be gratefully received.

 

[boblarson]

Access 2003.
I have added an unbound textbox to my (sub)form which displays a value based on the value in another bound textbox.

Private Sub Text34_AfterUpdate(Vintage As Integer)
    Me.[Text34] = Vintage(Me.EventDate)
    Me.[Text34].Requery
End Sub

(Vintage is the name of my function, Text34 is the unbound textbox, the name of which will change if I get this to work)

First of all, you are trying to modify the AfterUpdate event of the object. This is not possible to do. You can't add Vintage As Integer in to it. Second, you are setting the value of Text34 in the AfterUpdate event of itself, which means that technically you could put yourself in a perpetual loop as you can't update an object during it's update. In other words, you would be updating an object, which then fires the before update event which allows for validation, then it does the actual update, which is the After Update event. Then, since you just updated the object, it would fire those two events again.

Second, your code should be:

    Me.Text34 = Vintage(Me.EventDate)

You can't really requery a text box, so Me.Text34 is unecessary.
I would say you probably want this on the form's BeforeUpdate event, but it really isn't clear what it is for and when you need it updated.

 

[captain1974]

Thanks for that, my code kind of evolved as it didn't work first time
I now have

Private Sub frmSectionHarvest_BeforeUpdate()
    Me.Text34 = Vintage(Me.EventDate)
  
End Sub

In the before update event of the subform. Still not working.

What I'm trying to achieve: I am trying to display a year in a text box on a subform based on a value of a field in a record of the subform. Just adding the statement =Vintage([EventDate]) to the form achieves this apart from blank/no records (or when form first launched) displays the #Error.

 

[boblarson]

Probably displaying #Error because Me.EventDate is null at that point. Try adding this:

Me.Text34 = IIf(IsNull(Me.EventDate),"",Vintage(Me.EventDate))

 

[captain1974]

Probably displaying #Error because Me.EventDate is null at that point. Try adding this:

Me.Text34 = IIf(IsNull(Me.EventDate),"",Vintage(Me.EventDate))

Thanks, this didn't work in the before update procedure (the textbox was blank, so can't be recognising my function), so I modified & placed directly in the textbox and it works! Thanks!
=IIf(IsNull([EventDate]),"",Vintage([EventDate]))
Actually very similar to what I had in there when I tried to write an IIf statement.
Thanks again.