The following are the step by step instructions on setting up a drop-down GoTo feature on a form to search, select and go to a record quickly.
1. Set up a Query using the columns you want to see. Below is an example.
2. Type <>Nz([Form]![ID],0) in the criteria line of the ID column. Remember, the name of your column that has your primary key may be titled differently. Everything is typed exactly like below except you may have different column names, therefore the word “ID” may need changed in the formula to the name of your column that contains your primary key.
3.Save the query and call it “GoToQuery” and close it.
4. Open the form where you are adding the GoTo drop-down feature and change to Design View.
5. To add the drop-down box, find the “Design” tab at the top and find the one that says “Combo Box”. Drag your cursor on the form where you want your drop-down box. You can format and design it later.
6. Open up the Property Sheet.
7. Go to the “Data” tab. Under “Row Source” and type the following:
SELECT [GoToQuery].[ID], [GoToQuery].[Contact Name], [GoToQuery].[Company], [GoToQuery].[E-mail Address] FROM GoToQuery ORDER BY [Contact Name];
Example of adding additional sorting field:
SELECT [GoToQuery].[ID], [GoToQuery].[Contact Name], [GoToQuery].[Company], [GoToQuery].[E-mail Address] FROM GoToQuery ORDER BY [Contact Name], [you could actually sort by a second field if you wanted, but if you don’t, remove this and end the previous one with a semi-colon instead of the comma];
8. Now, go to the Events Tab of the Property Sheet. Under the section “After Update”, click the …(three dots at the very right of that row and Build A Macro. See image below and enter exactly how it’s listed. NOTE: If you get to the SetValue line and you can’t get it to bring up the next lines, click the “Show All Actions” button in the Ribbon of that screen and try again.
NOTE: Where it says “ID”, needs to be replaced with the name you have for your primary key field. Everything else above is exactly as seen. MAKE SURE YOU CLICK THE SAVE BUTTON AT THE TOP.
9. Close out of that macro and you’ll be back in your form design and property sheet. Four lines under the After Update line, you’ll see “On Got Focus”. Click the …(three dots at the very right of that row and Build A Macro. Complete exactly as follows:
10. Now, there are a couple more short steps. Under the Property Sheet (of the new drop-down combo box), go to the “Format” tab. Under the Column width line, there needs to be numbers for each field. So in our example, we have 4 fields (ID, Contact Name, Company, E-mail Address). Below you can see the widths are set to 0″;2″;2″;2″.
In order to hide your ID or primary key, you must put 0″ for the width in whatever place the field is in.
Secondly, under the “Data” tab, under the “Bound Column” row, the number represents the field you want to click on to select the record to open. When it comes to counting columns in a query, you start with 0, 1, 2, 3…In our examples, the ID column would be considered 0, the Contact Name is column 1 and the Company is column 2 and so on. As you can see below, in order for us to search by Contact Name, we used 1.
You should now have a working GoTo drop-down. I hope this was well explained!
UPDATE: If you want to set up a hotkey on your form so you can drop down the combo box using Alt+?, no matter what field you’re in, see my post dated 03/30/2016 for instructions.
By Rana Kory
