Query runs fast until form button pushed, then is super slow

[SpinningHead]

I have an order form which utilizes a combo box to obtain user input and return a set of records to the same form. The query is simple, returning from one table all items of a specific type (select all records from tblItems where type = selected type). There are several categories to choose from and each time the query returns results within 2 seconds. Once a specific item is located, the user can push a button to add that item to the current order. Once that button is pushed, however, and the user selects another category, the query can take up to 10 minutes or more to run. I've stripped out all of the code in the button and the query still takes an insane amount of time even though no code was executed. The table is indexed according to the search criteria. The form is not based on a table or query and the query is executed from VB once a selection has been made. I've also tried using a saved query (also executed from VB) with no different results. Any ideas what is happening? Is there a better way to build an order form? I am using Access 2003 on a WinXP box. Thanks for your help.

 

[OhioSteve]

Well, this is not something that has a one-sentence answer. However, I will take a stab at it.

I picture the form having three items. The first one, a listbox, has categories like "meat, beverages, deserts". The second item, a listbox, displays subtypes of the first one, ie "bacon, hamburger, steak". The third item, a text box, contains items chosen. Your problem is that the process slows down after you choose the first item.

Is that accurate? If so, my next question would be "How do you refresh all this stuff?"

 

[SpinningHead]

Yes that is mostly correct. The first listbox is general categories (meat, bev., deserts), the second are subcategories, and the third list the results of the desired category (there are sometimes two level of categories). So the chosen items are listed in another, fourth, listbox. As long as no item is selected and "added" to the order, the query runs fine as many times as you make a different selection. I'm not necessarily refreshing anything... Once a user selects an item, the item is added to the Orders table for that specific order and the item is listed (written to the form from VB) in the fourth list box. The form then remains open for another selection, using the drop down menus, causing the query to run and bring another subset of data. The List box containing the results of the last query is cleared and re-written, again, using VB. Hope this helps. Thanks.

 

[OhioSteve]

Okay, hmmm. That fourth item. I think that we have both described it as a "list box". But I assume that its actually a subform, not a listbox. And it lists each of the chosen items. The subform's data source is probably a table or query. That table or query probably contains the selected items. You say "...I'm not necessarily refreshing..." the form. Refreshing the fourth item's data source is extremely important, because the data source needs to reflect the user's last choice. That is not a tangential issue.

 

[OhioSteve]

Okay, I sat down and did something analogous to your project. This is how I did it.

1) Created table1 with the fields name (text) and selected (yes/no). Name is the pk.

2) Created the queries notSelected, selected, and selectItem with this sql:

[notSelected]
SELECT Table1.name
FROM Table1
WHERE (((Table1.selected)=False));

[selected]
SELECT Table1.name
FROM Table1
WHERE (((Table1.selected)=True));

[selectItem]
UPDATE Table1 SET Table1.selected = True
WHERE (((Table1.name)=[Forms]![Form1]![List13]));

3)
I Created form1. Its a continous form. Its data source IS selected.

In its header, I added List13 as a listbox. notSelected is list13's data source. In the header, I added command13 as a button. The button runs macro1.

In the main part of the form, I added a text field for main. I made this field's data source name. Remember that's the name value from selected.

4) I created macro1. Its first action is to run selectItem. Notice that selectItem changes the selected value for whatever is in the listbox. The macro's next action is to close form1. The macro's final action is to open form1. Those two actions are a tidy way to refresh the form.

The effect of this is as follows...

When the form opens, it displays the selected items. But in its header, there is a listbox displaying all of the non-selected items. When the user presses the button, an item magically goes from the listbox into the form.

I can help you with the details later, like suppressing some message boxes.

 

[SpinningHead]

Thanks a lot for your help. The fourth item actually is a list box. So is the third item where the available items (according to chosen category) are listed. I'm not refreshing the form as you suggest, just updating the values for the listbox. Everything, the dropdowns (for category 1 and the possible sub-category), the list boxes (the result set and the chosen items), and the buttons are located in the detail section of the form. However, I understand the approach you are taking and am wondering if it is more suitable or appropriate to the way Access is designed to be used.

There are 1300 items in the DB and so there needs to be a query (or two if a subcategory is required) to return only a portion of the items. Should this all happen in the header? Once an item is selected, a new record is created in the orders table. Then as you suggest, place the chosen item(s) in a listbox in the detail section, have the form close and then open, and then update the listbox according to that order number (from the orders table) each time a new item is selected?

I'll try this out and see what happens. Again, thanks for your help.