Access Cookbook - Ken Getz [50]
The code for acbDoSearchTable is almost identical to that for acbDoSearchDynaset, except that the table search uses the Seek method to search through an indexed recordset instead of the FindFirst method. Because it can use the index, it should be able to find matches much more rapidly than acbDoSearchDynaset.
TIP
Because acbDoSearchTable requires that the list box's record source be a table, it will trap for that error and return a nonzero value as an error variant if you try to use it with some other data source. In addition, the function will not work correctly if you mismatch the bound field and the index. That is, the bound field must be the only field in the selected index).
The code for acbDoSearchDynaset, acbDoSearchTable, and acbUpdateSearch is in the module basSearch. If you want to use this functionality in other applications, import that module into your application and follow the steps outlined earlier to set the properties for your text and list boxes. In addition, if you import the sample code into a database created in Access 2000 or later, make sure you use the Tools → References menu item from within VBA to add a reference to the Microsoft DAO type library. By default, Access applications created in those versions don't include a reference to DAO, and the sample code in this demonstration requires this reference in order to do its work.
2.9. Create a Replacement for Access's InputBox
Problem
You'd like to be able to use Access's InputBox function in your applications, but it's so ugly! There doesn't appear to be any way to modify the way it looks, so you'd like to replace it with a standardized input form of your own. You'd also like to be able to call into your help file with a Help button on the input box.
Solution
The dialog you see when you run Access's InputBox function is just a form, like any other form, except that it's built into Access. You can create your own form, open it as a dialog form, and have it look any way you like. This solution demonstrates a technique you can use in many situations: creating a pop-up form that waits for input and, once it's done, allows the caller to retrieve the information gathered on the form. In this case, you'll call the acbInputBox function instead of InputBox, but the results will be the same.
Load and run frmTestInputBox from 02-09.MDB. This sample form gathers information and then calls the acbInputBox function to display the replacement input form. Once you're done with the input form, choose OK (to return the text you've entered) or Cancel (to discard it). The sample form will pop up a message box with the text you entered. Figure 2-16 shows the two forms at work.
Figure 2-16. Use frmTestInputBox to test the replacement input box
Follow these steps to include this functionality in your own applications:
Import frmInputBox from 02-09.MDB into your database. Modify its appearance any way you like: change its size, colors, fonts, or any other layout properties. Because the form includes a module that handles its setup, you'll want to use the form we've supplied rather than creating your own.
Import the module basInputBox from 02-09.MDB. If you modified the form's name in Step 1, you'll need to modify the code in basInputBox, making the acbcInputForm constant match the actual name of the form.
To use the new input box, call the acbInputBox function that's in basInputBox. It requires one parameter and accepts a number of optional parameters, as shown in Table 2-7. These parameters exactly match the parameters used by Access's own InputBox function. The general syntax for acbInputBox is:
varRetval = acbInputBox(Prompt[, Title][, Default][, Xpos][, Ypos] _
[, Helpfile, Context])
For example, to match the function call in Figure 2-16, you could use code