Before Update Event - not saving record, etc.

[CedarTree]

I have a few subforms on a main form and when a record is changed, I use before/after update events to write data to a SQL server. It normally works perfectly fine. I can edit a row in a subform, click to the next row, and the data gets written. But if I edit a cell and then click on ANOTHER subform, I get "the on before event warning / can't update the record..." (Something like that). But it works anyway. Anyone have an easy way to suppress the pop-up?

 

[theDBguy]

Hi. I imagine the popups are trying to tell you something. I wouldn't ignore them; rather, maybe try to figure out why they are happening and then try to fix the reason.

 

[Pat Hartman]

Sounds like you have code that is using DAO or an update query to update rows that are bound the the subform you are leaving or going to. We'd need to see the code behind both forms.

 

[arnelgp]

if you are using Bound form, there is No Need for you to issue "SQL update" query.
it will cause conflict. the form will "send" the update, while you also "send" the update.

 

[CedarTree]

Not using a bound form connected to the data I am updating. The subform looks at a temporary local table that mirrors the data I update via SQL. So it's client/server set up.

 

[Minty]

Even if it's a local table copy, it still means that the data in the table is trying to be written to somewhere else or that record is locked.
So you should still be trying to understand what is causing it.

I would possibly write the Remote SQL table changes on the main form exit/close rather than sub-form exit if they are bound.

 

[Pat Hartman]

Access is a Rapid Application Development (RAD) tool. It is used to create interfaces for all sorts or relational databases. One of the most powerful features of Access is bound forms. By not using bound forms, you are not using Access as it was intended and so you are doing potentially tons of extra work for no reason. Bound forms work fine with all relational databases. You just have to control the records you select. You don't bind a form to a naked table or a query with no criteria. Beyond that, bound forms save lots of work.

Doing an asynchronous process as you seem to be doing, is prone to error. Hopefully, you are working inside a transaction so you can ensure that either everything gets updated or nothing gets updated.