Is the form in question bound to a datasource? This is the most common way that Access would be used, and if it were you probbaly wouldn't need to update via SQL.
Form_BeforeUpdate() is usually used when a form is bound to a datasource. You wouldn't normally use this event to determine if controls' values have changed and need writing back to the DB...
You run those querys against currentdb (wich I assume is the access-db) but are in fact using a SQL server database where you want the changes to happend?
Are those tables in the sql server linked to tables in the access-db or what method are you using?
Have you checked the table for broken triggers? It's a long shot, but some very very odd problems have been caused by bad/broken triggers before, and they're hard to understand before you notice the trigger...
In your BeforeUpdate event, you should determine which (if any) controls have changed and only execute the write-to-changes table ONCE outside the loop over all the form's controls. Otherwise you are going to get so many change events! Why not create a copy of the target table, blah_history, without FKs and simply write the changed record to it?
If you can goto the specific SQL Server table (using Query Analyzer or Enterprise Mgr) & see whether it works as you expect. I am doubting that linked table in VBA might be showing you incorrect picture.
Edit: If you have SQL Profiler, see what is being executed there to confirm your doubt of the same query getting executed.
Ah, you're using SQL Server too. I have had very good luck with using triggers to audit data changes behind Access. There are a couple of quirks, but linking the tables via ODBC using trusted authentication makes it very easy.
OK, so you can't or don't want to use triggers. When front-ending SQL with Access, I usually use ADO objects in my VBA code for this sort of thing. Linked tables with ODBC are great for bound forms, but nothing beats ADO for going directly to the SQL Server.
Would you like more information about linking the tables using trusted authentication or help with using ADO to do the work with SQL Server?
Fixed by adding an auto-incrementing integer primary key column to the changes table.
Silly Access...
I have to say the discussion around this question drives me crazy. With ODBC linked tables, you start with bound forms. There is simply no reason to be using pooled connections. If you are hostile to the Access implementation of how to do these things STOP USING ACCESS.
--
David W. Fenton
David Fenton Associates