Recordet makes my database works extremely slow!!

[Desperate]

Hello mates.I have a material management database.In one table there are received materials with their quantities and locations. And I have a form which should filled for a material request to be deducted from stock. On this form there is a little sweet listbox on the top. When user select the material, locations and quantities per each location appears in this listbox. I have done this with recordet. But as the amount of received materials increased it started to work slowly. Even clicking the combo box for selecting the material takes 3-4 seconds. Probably the reason is, recordset goes through all the entry and finds the matching part number to display its location and quantities. How can I make this more efficient so it works faster.By the way LockType is adLockReadOnly and CursorType is adOpenForwardOnly and these are the ones for the best performance as far as I know.

 

[mikehershey]

List boxes with lots of elements are annoying to search through and slow. Make a narrowing criterea sort of thing. Make 3 drop downs each will recieve data options from 3 tables that you will update to have all possiple of the 3 parameters and All. IE minerals could be (Gold,silver,all) the combo box will take them from the minerals table which will be updated will all minerals. Once the user changes the selection regenerate the listbox with only values that match the criterea from the listbox.

EDIT: I may have misread if you mean the listbox has only the locations just have 1 table that has only locations and update everything else when the user selects a new location. (the listbox will be driven from the 1 table with only locations so you dont need recordsets or queries). To search for things based on location set a query that selects only the selected location + recordset that query if u need more detail.