BeforeUpdate in a Form in Datasheet view

**fitshase** · 07-29-2011, 03:07 AM

Hi,

I'm new to this so please be kind

I am helping to develop a database ("Project DB") at work to help track dates for works on various sites (various actual dates and a selected few forecast dates for major milestones). I have the tables set up OK and I've created some forms as well to make it easier to input the data.

On one of the forms (in form view), I have the BeforeUpdate function assigned to the fields related to forecast dates. When a forecast date is updated, "Yes" is inserted into a field called "ForecastsUpdated":-

Private Sub Survey_Forecast_BeforeUpdate(Cancel As Integer)
ForecastsUpdated = "Y"
End Sub

Private Sub Planning_Forecast_BeforeUpdate(Cancel As Integer)
ForecastsUpdated = "Y"
End Sub

Private Sub Completion_Forecast_BeforeUpdate(Cancel As Integer)
ForecastsUpdated = "Y"
End Sub

This works fine on the form in form view but if I put this function on a field in a form in datasheet view I get an error:-

The expression Before Update you entered as the event property setting produced the following error: A problem occurred while Project DB was communicating with the OLE server or ActiveX Control.

* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
* There may have been an error evaluating the function, event, or macro

Can you let me know what I am doing wrong?

Cheers

**SoftwareMatters** · 07-29-2011, 05:26 AM

Unless there is a good reason not to I would place this code on the AfterUpdate and also add a check that the field isn't blank as follows:

Private Sub Survey_Forecast_AfterUpdate
If not isnull(me!Survey_Forecast) then
me!ForecastsUpdated = "Y"
End If
End Sub

NB: I don't know if this will fix your issue.

**fitshase** · 07-29-2011, 05:38 AM

JD,

Thanks for the prompt reply. Unfortunately that doesn't solve the problem. I no longer get the error message but I also don't get the "Y" in the ForecastsUpdated field.

Cheers

**fitshase** · 07-29-2011, 06:03 AM

I've figured it out (eventually!). It appears to be because that particular form was based on a query instead of a table. For some reason the person who set up the database created a query which pulled all the data from the table and then built a form based on the query. I've bypassed the query part (since it pulled all records from the table so was no different to the table) and it works.

How do I mark this as "Solved"?