X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Does With/ End With "contain" The VBA Threads?, Access 2010    
post Nov 23 2017, 01:00 PM

Posts: 68
Joined: 10-October 17

I'm not looking for a solution here, since I think I've got it. But I am curious about why my solution works. I THINK I know why, but I need smarter people than myself to confirm (or reject) my idea.

I have a complex database that has hidden forms and visible forms, each of which has ontimer events running, and which also interact with each other using VBA code. For example, form A (hidden) and form B (visible) are both open and executing ontimer events; periodically, a user may use a control on form B to edit data on form A that is also subject to changes based on form A's ontimer events.

This system works well most of the time, but every once in a while I was trapping a 7878 "data has been changed" error originating from form B. I assumed this was because form A's ontimer events had changed something hile form B was running its user-activated code. I added code to form B that would first check whether A was dirty and, if so, requery it and would also update form A with dirty=false and form B had changed for A's data. That greatly reduced the 7878 frequency but did not eliminate it completely.

I then changed form B's code to encompass all of the above steps in a with/endwith statement, rather than having them just execute one after the other, and the error seems to have gone away. Is that just coincidence or does the with/endwith statement somehow "contain" the VBA thread and force all of the steps to operate together and "block" or delay the ontimer events?

Version 1 still had a 7878 error:

formA!field = newdata
formA.dirty = false

version 2 seems not to:

with formA
field = newdata
.form.dirty = false
end with

Does the with/endwith statement somehow "contain" the VBA thread and force all of the steps to operate together and "block" or delay the ontimer events?
Go to the top of the page
post Nov 23 2017, 01:46 PM

UtterAccess Editor
Posts: 16,297
Joined: 27-June 06
From: England (North East / South Yorks)


In a word, no. :-)
VBA is single threaded, ACE has, if memory serves, three threads, but we have no programmatic access to any of them - they relate to transaction processing.

A With block is no different than if you'd used an object variable. i.e.
With Forms!SomeForm
    .Control = "X"
End With

is functionally equivalent to
Forms!SomeForm.Control = "X"
End With

They have no bearing at all on the data persisting of either form.
What you need to be very careful with, is updating the same data on more than one form. You know this, it's the crux of your issue. But it's not about code blocks or anything other than timing.
If you're performing an update on the same data before another form has committed, then you'll get concurrency issues.
Do you really need to have two forms open, which both UPDATE the same data? What's the point of one to update it, if the other updates too?

But assuming you really do need this, then timing is all. Having a Timer run and potential edits and updates firing is a definite recipe for trouble. But if you have control and are clear on the order of updates, then you'll still be OK.
(Stepping back is a better plan, rather than throwing requeries and saves into the mix and seeing what sticks.)

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th December 2018 - 02:26 AM