We help IT Professionals succeed at work.

Unwanted loop when using On current event in Microsoft Access 2007

I have a form with two listboxes, two subforms and two pictureboxes.

1) The first listbox is populated via the row source in it.
   When I mark a row in the listbox one subform show detail information and one picturebox show a linked picture.
   I use the listbox After_update to populate the picturebox.

   This works fine.

2) The second subform is linked to a textbox which is drawing the value from the listbox. The column in the subform is a foreign key, so I get a number of rows in the subform.

   This works fine.

3) I have a textbox taking a value from the second subform. This textbox is used in the query populating the second listbox as a criteria.
   When I mark a row in the second subform I want the second listbox to show values reflecting this.
   To do this I have a requery in the second subforms event On current. I do the requery on the second listbox.

   Here is the problem: A loop is started and the On current event is run over and over again.
   I can not understand why because I do not trigg the On current event in the code, I do the requery on the listbox which is placed in the main form.

4) The last thing is that when I click on a row in the second listbox the second picturebox is populated.

This works fine except for that the loop is removing the picture from the second picturebox. (When I click on another row in any form or list the pictures are removed.)

I know this sounds a bit complicated but I have tried to keep the structure strait forward.
I upload the file (Access 2007) together with some picture files used in the example, and hope someone can tell me where I go wrong.
There is also a textfile with short instructions.

Best Regards,
Hans Hansson
Brandbilar-20111206.accdb
A.jpg
B.jpg
Bild1.jpg
Bild2.jpg
Bild3.jpg
Instructions.txt
Comment
Watch Question

Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
In the future, please be sure to Compact your database before uploading. If it's large, you might also consider Zipping it before doing so.

I was able to recreate your issue, and from what I can gather it is coming from the Master/Child links on the subform "frmData".

I commented out ALL code except for the Current event in frm_Data. When I selected a value in the listbox, that current event continued to fire.

However, I cleared the Master and Child links on the subform, and then added this line at the TOP of lstFordon_AfterUpdate:

Me.frmData.Form.RecordSource = "SELECT * FROM qryData WHERE Fordon_ID='" & Me.txtFordon & "'"

This stopped the constant requery, and seems to work correctly. I can only assume that Access is having troubles with the calculated control value that is the ControlSource for txtFordon, and continues to requery for that value. This is yet another in my long list of reasons to never use calculated values for a control source, if you need that control for anything else
EE-lsFrodon.png
Brandbilar-20111206-Rebuild.zip
Hi,

LSMConsulting is right, calculated fields should never be used in anything like links of subforms. It's not only that strange behaviour of Access, it's also the performance: Calculated fields will be calculated after all other things happened on the form and so they have always a viewable delay - and so also the subforms would have this delay. This is deadly for the performance.

You don't need the calculated field here. Simply change the "txtFordon" in the link criteria of both subforms to "lstCriteria" then the first reason of the update is away.

The second is of course your "updat" sub, which does a requery of the listbox in the main form. As the subforms depends on the data of the listbox they will be refreshed after each new selection of the listbox. Then the Current event fires, requeries the listbox and this means the subforms must again load their data. This fires the Current event and so on, that will never stop. Simply remove the requery line from the "updat" sub, that's all.

By the way: If you go on to program this way you will get in trouble more often. You should think of any form as a class module and the highest target each class module should reach is to know nothing about their environment. The subforms should not access any object of the main form and vice versa. Create clean interfaces between them, for example a "Public Sub UpdateForm" in the form where you need it. This sub should update anything needed inside the same form and only this public sub should be called from outside of the form. This method helps you to make sure that the objects of the form like controls or properties will never be changed from outside of the form.

If you want to go a step further you could create own events which fire their status "into the heaven" and anything else must react on the event. So if an update elsewhere is needed the subform only fires the event "PleaseUpdate", and it will never know if this is be done by any other object or not. The main form can then react on the "PleaseUpdate" event and update anything on the own form (except the subforms of course). In this way you have a 100% cut between both forms so if you would open the subform alone as main form it would only fire the event "PleaseUpdate" but there is no main form so nothing else happens. If you manipulate the objects of the main form in the sub form it is completely dependent on that it is opened as subform, if you open it as main form it would create an error and says that the other form is not loaded, can't be found.

This separation should be done in ALL objects. For example, you should not create queries with a WHERE criterion which is linked to a form or control, because a query can get a parameter and can be filled by the form which wants to use it. Same procedure: Strictly separate all objects from each other and you get clean code which maybe is a bit more to work in the beginning but easier to maintain later.

Cheers,

Christian

Author

Commented:
Thank you both for two different ways to solve my problem, and your effort to explain how these things works. I though I have found a good way to create a simple structure in the application, but now I understand that this has great risks. I will rethink the way I create applications in the future.
I split the point between you.
Many thanks,
Hans