Microsoft Access

Microsoft Access GoTo Feature

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.

Lookup Image

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.

NzformulaCapture

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.

Property Sheet capture.PNG

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.

macro image 1

macro image 2.png

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:

macro 3.PNG

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.

column width bound.PNG                       Bound

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s