I just "enhanced" a form by introducing the "like" criteria into the query underlying a list box in an add record form. My frustration is that when the user first attempts to type into one of the text boxes (any box) there is a large delay (approx. 20 sec.). This delay only occurs once on the first attempt to type, and does not happen even as the user adds more records. My guess is that the Like criteria, [txtLastFirstName] & "*", is populating the combo box based on an empty text box, which you can see from the criteria would return every record, but I don't know away around this. I changed this criteria from simply equal to the [txtLastFirstName]
The Like criteria is useful because the end user would then be able to type only the beginning then select the exact member from the resulting list box.
One additional note: when I select "No" on the Data Entry option of the Form Properties, the delay takes place on load and not after that....
Could I correct this by using code to suspend the rowsource property of the list box until the user had entered the name? What would be the best way to do so?
The solution I implemented, and seems to work to my satisfaction is to use Me!lstDivNum.RowSource = "" on the opening of the form, which clearly makes the list box attached to nothing, then reinstate the query with Me!lstDivNum.RowSource = "qryNameLookup" when the uses updates the name text box. My personal problem with adding an additional button to refresh the list of members matching the query is that it would add an additional step in the data entry processing. Having the list refresh automatically when the name (or part of the name) is entered seems speed up the process of selecting the right member. I'm new to conventions, is there a reason that the search should not be done in this way?
Thanks for the additional advice. Indexing isn't possible as we're dealing with ODBC of an off-the-shelf product which cannot be altered. This is a supplementary database connected to that existing product. Part of the reason for implementing the search function in this way is to emulate as much as possible the way in which searches and results were performed on the existing database.....just makes for easier usability, training, and a warm fuzzy feeling.