List Box anomaly

%3CLINGO-SUB%20id%3D%22lingo-sub-2230420%22%20slang%3D%22en-US%22%3EList%20Box%20anomaly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2230420%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20multi-user%20Access%20application%20--%20each%20user%20has%20a%20client%20piece%20locally%2C%20all%20looking%20at%20a%20single%20Access%20database%20with%20the%20table%20data%20on%20a%20company%20network.%3C%2FP%3E%3CP%3EA%20form%20on%20that%20client%20application%20(let's%20call%20it%20Form1)%20has%20a%20number%20of%20objects%2C%20including%20a%20list%20box%20and%20several%20buttons.%26nbsp%3B%20The%20form%20itself%20is%20unbound.%26nbsp%3B%20One%20button%20has%20some%20code%20for%20the%20OnClick%20event%2C%20and%20all%20the%20VBA%20code%20behind%20the%20click%20event%20does%20is%20simply%20open%20open%20another%20form%20(call%20it%2C%20Form2).%26nbsp%3B%20But%2C%20when%20I%20click%20that%20button%20on%20Form1%20to%20open%20Form2%20(which%20is%20also%20an%20unbound%20form)%2C%20I%20can%20see%20that%20the%20list%20box%20on%20Form1%20requeries%20(or%20refreshes%20--%20I%20can't%20tell).%26nbsp%3B%20There%20is%20nothing%20in%20the%20VBA%20code%20to%20requery%20the%20listbox%20on%20Form1.%3C%2FP%3E%3CP%3EHas%20anyone%20ever%20encountered%20such%20an%20anomaly%20or%20have%20any%20thoughts%20on%20what%20might%20be%20causing%20this%20anomaly%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2230420%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2230564%22%20slang%3D%22en-US%22%3ERe%3A%20List%20Box%20anomaly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2230564%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F536307%22%20target%3D%22_blank%22%3E%40RichNewman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20without%20SEEING%20the%20actual%20code%2C%20it's%20impossible%20to%20guess%20what%20might%20be%20happening.%3C%2FP%3E%3CP%3ETo%20share%20the%20code%2C%20you%20can%20open%20the%20form%20in%20design%20view%20and%20select%20the%20VBA%20for%20the%20button's%20click%20event.%20Copy%20it%20all%20and%20paste%20it%20here.%20That%20way%20someone%20might%20be%20able%20to%20spot%20a%20line%20that%20could%20be%20causing%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a multi-user Access application -- each user has a client piece locally, all looking at a single Access database with the table data on a company network.

A form on that client application (let's call it Form1) has a number of objects, including a list box and several buttons.  The form itself is unbound.  One button has some code for the OnClick event, and all the VBA code behind the click event does is simply open open another form (call it, Form2).  But, when I click that button on Form1 to open Form2 (which is also an unbound form), I can see that the list box on Form1 requeries (or refreshes -- I can't tell).  There is nothing in the VBA code to requery the listbox on Form1.

Has anyone ever encountered such an anomaly or have any thoughts on what might be causing this anomaly?

12 Replies

@RichNewman 

Unfortunately, without SEEING the actual code, it's impossible to guess what might be happening.

To share the code, you can open the form in design view and select the VBA for the button's click event. Copy it all and paste it here. That way someone might be able to spot a line that could be causing this.

 

 

@George Hepworth 

 

Thanks for the prompt reply.  Here is the code, but it's really amazingly simple..

 

Private Sub Command6_Click()
     Dim stDocName As String
     Dim stLinkCriteria As String

     stDocName = "frmContractAdd"
     DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

@RichNewman 

At what point do you see the refresh or requery happen? 

 

@RichNewman 

Also, what happens in that second form, "frmContractAdd"? Is it adding a new contract to the table which is also the row source for this list box, by any chance?

As soon as I click that button.

When I was developing this, I used a copy of the production data placed on my laptop and linked to the tables in that local copy, just to improve performance. When I do that, this odd requerying action does seem to happen now that I'm looking closer, but so quickly that nobody would notice unless they tried. Likewise, when everyone was working in the office last year, the response time was much better. Now that folks are working from home with slower response times (varies by user of course) the requerying is much more noticeable, and one person shared her desktop with me and I saw that underlying list box requery a few times, very slowly. The odd behavior does seem to be specific to the listbox on the first form where the button to open the second form lives. The second form is to add a record to the database, and when that is done, and I close that 2nd form, then in that case there is an explicit requery of the listbox to display the added or modified record in the listbox. But this is happening before we even get that far.

@RichNewman 

 

Thank you. That was going to be my suggestion, in fact. So you see this requery even without closing the second form and BEFORE using it to add a new contract?

 

Correct. The second form to add a contract is a modal popup that users have to fill in. They click a button on that popup, I run some editing checks, and when it passes I add the record to the database, close the second form, and force a requery on the listbox in question on the first form. That particular requery is intentional and I see that in the code. But the one described above on that first form where I just clicked to bring up the popup seems inexplicable to me -- at least insofar as looking at the code. (We all makes mistakes, of course, and I'm no different. But I've been coding Access with VBA for about 15 years, so I'm pretty good at spotting coding issues. Something else is afoot here.)

@RichNewman 

One more test comes to mind then. Open the contract form, then immediately close it without adding a new contract or doing any of the other validation. Simply open and close it. Does that still cause the unintentional requery? 

 

Keeping in mind that processing in the calling form would be suspended until the called modal is closed, is there possibly some other event in the calling form that is pending until the contract form closes again? Maybe a timer event? 

The unintentional requery happens before the user even makes a decision to add a contract or just close the popup with no changes. They just have to click the button to open the popup. By the way, it's not just the "Add Record" button. On the first form, there's also a button to delete a record. The user selects a record to delete from the list box in question, then clicks the Delete button, I throw up a warning message to be sure they want to perform the delete, if they affirm, I delete the selected record, and then I explicitly requery the list box (no additional form has been opened). In this case, the moment I click "Delete", I see the list box requerying, and again, I didn't even open a new form.

@RichNewman hm. That suggests the problem is not in the called form, then, but in the calling form. It is requerying the list box based on some internal event. As soon as it loses focus to the message box or to the called popup, something forces that requery. What other event(s) are in that form? Anything that could be going on there?

Also, is this on a subform or in a main form? It occurs to me that subforms do exhibit that repeated requery behavior when loading. Is that possibly involved?

There's no sub-form. The form that contains the listbox in question has only one event procedure that executes OnOpen. There's nothing for the OnGotFocus or OnLostFocus events, though I see where you were headed there.

@RichNewman 

 

I wonder if you've found the place where the requery is being triggered. If you'd like, I could take a look. Can you provide a sample copy of the accdb with any personal or confidential data removed?