Search Button

[bomber72]

Hey everybody. I'm pretty new to Access and was hoping to get some good help here.

I'm trying to create a search function with a search bar and a button on a form. It will be used to search for employee IDs within a table. When the button is clicked, the program should do the following:

1) read the Employee ID in the search bar


2) Locate that Employee ID in the "Employees" table
3)Display the resulting Employee, along with other information about the employee on the table (region, pay grade, etc.)
-If the information does not exist-
4)Bring up a message box saying 'This Employee ID could not be found'

I'm not sure how I want the information displayed yet, and am looking for suggestions. The only two things I can think of is either on the bottom of the current form or activate a different form displaying this info.

I've been looking through these forums trying to find a search button solution that would work for me but I can't seem to find it. If anybody knows one that will help go ahead and refer me to that one.

Thanks!

Bomber

[June7]

Both approaches are possible (display on same or another form).

1. Same form approach uses code to set the form's filter property and toggles the filter on/off. Form must be bound to source data table/query.
Me.FilterOn = False
Me.Filter = "strEmpID='" & Me.cbxEmpID & "'"
Me.FilterOn = True

2. Another form approach uses code to open form filtered to the desired record.
DoCmd.OpenForm "formname", , , strEmpID

The 'search bar' should be an unbound combobox that offers a list of existing employees to choose from. If employee not in the list then give user opportunity to add employee record. A button is not necessary if there is only the one criteria. Use the AfterUpdate event of the combobox. Check out the OnNotInList event of the combobox as well as the LimitToList property.

[bomber72]

Thanks for getting back to me so fast June.

Unfortunately I won't be able to use the combobox. I wish that I could because that is an extremely easy solution. But this program will be used to search through thousands of employees, and a combobox simply isn't a feasible option.

The solution that I am trying to go with has an unbound text box with a "search" button next to it. The user types in the employee ID and then presses this search button to find and display the information about the employee.

Unfortunately I do not know how to program a search button of this sort, and was hoping for some help with the coding for that. Any thoughts?

Thanks

[bomber72]

June,

Thanks again for all your help. I found a solution for my problem.

Bomber

[winteram]

Bomber,

Is there a way that you could post what you used as your solution? I am running into the same problem. I have hundreds of records, so manually sifting through one by one is not feasible.

Thanks!!

[bomber72]

So here's what I did.

First off, I created the Query which I would be calling on. It includes an employee number which is what the search is based on. I had a text box called txtSearch and a button called butFind.

I went into the Query design mode and under my first category, Employee ID, under the Criteria section, I brought up the expression builder. From the first box on the bottom left I selected Forms, Loaded Forms, User Form (the name of my form), and finally in the second box, I selected txtSearch (see above). The code that came up looked like this - [Forms]![User Form]![txtSearch]

Then I went back to the form. I clicked on the button and brought up it's properties. Under the event tab I went to On Click and selected event procedure and then clicked on the three little dots that comes up and brought up the VBA Code.

Under the sub on the code, I typed in this -

DoCmd.OpenQuery ("Employee Query")

Saved the sub and closed it. That should work. I type in an Employee ID in the search box and it searches the query and displays that result, along with the other information that the query is supposed to bring up.

Let me know how it goes.

Bomber

[winteram]

Bomber,

Thank you so much for your help. I am running into one error. I did everything that you wrote, and when I type something into the search box and click the button the query filters down to only the record that I am referring to, but it will not automatically open the query. Do you know why this could be? Also, is there any way that the query could be opened in form mode so that the record would be easily editable?

Thank you!