Problems with Access 2007 Project Forms not allowing input/update data

Question

It's been a while since I had to do anything with an Access Project so forgive me if this something easy.

I have an Access 2007 database which I have converted into an Access 2007 Project. I've created all the tables & views on a SQL 2008 server, granted all the necessary permissions and started testing.

Where I'm having trouble is, I have a form that is used to update data. The form is fed from a view (all tables in the view have primary keys) and view is schema bound. On the form for some of the fields I have a "Change" event handler which updates a "Last_Worked" field so we can track the last time those fields were changed. (the "Last_Worked" field is "datetime" in the SQL server) The event handlers are basically "Last_Worked = Now()".

The problem is, for those fields where I have a "Change" event handler, I can't put anything into those fields, I start typing and nothing is displayed in the field. When I check the "Last_Worked" field in table it's updating, but the fields I attempted to change from the form aren't changing.

I can update data and insert new records from a dataview so the view that is feeding the form is not read-only. If I remove the event handlers the problem goes away, but I need to log when those fields are updated. I've tried doing "Me!Last_Worked = Now()" and "Me!Last_Worked.Value = Now()" in the event handlers and get the same problem, I'm not able to update/edit those fields.

This worked perfectly in Access and I even created a blank Access database, copied the forms over, created linked tables to the views in the server and it worked. It just doesn't work as an Access Project.

Any help would be appreciated.

Fionnuala Fionnuala · Accepted Answer · 2011-12-20T21:08:37

When you say event handler, do you mean you have set the control source? You cannot do that if you need the control for data entry. Note also that the change event runs for every small change. You can either use the default value for new records, or run some VBA in an update event of some relevant control. I reckon in this case you need:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.LastChanged = Now()
End Sub

Problems with Access 2007 Project Forms not allowing input/update data

1 Answers