Another question: My combobox list sporadically disappears!

[NCYankee1]

Hi again

I have an Access XP form with a combo box on subform within that form. The combo box's rowsource is a query with a condition based on the value of a field in a textbox on the form. This value does not change once the user is on this form. What I am finding is that sporadically the list in the combo box disappears. I am not losing the value of the field used in the condition. But the query sometimes just returns no rows in the list. How do I correct this??

Thanks!

 

[FoxProProgrammer]

It sounds like the combo box is being requeried and the value in the WHERE clause is missing or changed. How are you referring to the text box in the WHERE clause? You might try using [Forms]![formname]![fieldname] instead of simply fieldname if you aren't already. I'd set a breakpoint in the procedure that sets the Rowsource and make sure that the value in the WHERE clause is what you think it is.

dz

 

[NCYankee1]

Yes I am passing the criteria using [Forms]![formname]![fieldname] and I have even put a msgbox there to show me the contents of the field I am passing. The field hasn't lost its value but the combo box will sometimes be empty. It is bizarring me out. Any ideas?

 

[FoxProProgrammer]

Are you setting the Rowsource in VB or in the Rowsource property of the combo box on the form? You might try setting it in VB and setting a breakpoint in the code to see what the value of [Forms]![formname]![fieldname] is when it gets there. If you are setting the Rowsource in the control on the form, you don't have much visibility into what is happening, and this makes it more difficult to debug the program. Once you figure out what is causing this issue, you might be able to set the Rowsource in the control instead of VB if you want.

 

[NCYankee1]

The rowsource is set in the combo box properties. It is simple query returning one field from a table based on one "where" criteria. And it works fine. For awhile. And then it stops working. Yesterday we performed a stress test on the app. We had 30 people hit it at once. It is an Access XP app running on a Citrix terminal server. It has a SQL Server back end. We had 30 people hammer at it for an hour. And things were fine until late in the test. That's when the app failed for everyone. The combo box stopped returning list items. The terminal server was running at 8% of capacity and the SQL Server was hardly breaking a sweat either. The app had a good response time. But then that combo box just began failing. Any ideas what causes this? Or how to debug a problem like this?

 

[FoxProProgrammer]

Gee, I don't know for sure. I realize that the Query doesn't change, but the value in the WHERE clause does. If your Query is:

SELECT table.field FROM table WHERE table.field = x;

I would assume that the value of x can change each time the user loads the combo box on their local machine. It sounds like the problem could be on your server or with your network. I don't see how the Access front end could be causing this because you are only using it to retrieve data from the SQL back end server. I don't have any experience running Access on a network with an SQL Server back end. Hopefully someone else will have some ideas for you.