0

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.

ByerRA
  • 1
  • 2
  • 3

1 Answers1

0

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
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Sorry for the late reply, been rather busy. As for "Event handler" I mean like Change, BeforeUpdate etc. I have since solved the problem by moving the code to AfterUpdate and it works just fine. Thanks for the help. – ByerRA Jan 12 '12 at 15:02
  • @ByerRA AfterUpdate for the form is not a good event to use to change anything on a form, because it will cause an endless loop. I am sure you must be using the after update event of a control or updating data in some other way. – Fionnuala Jan 12 '12 at 15:23