Access Tip - Create A User Defined Filter Or Search Form

Many times it is necessary to provide your users with an easy way to filter or search for data in large table of information. This tip will show you one quick and easy way to solve the problem by giving the user a field to enter a search string and updating the form with those fields that fit the search pattern. It's quick and easy, so give it a try.

For this example I used a table of keywords and how often they were found on the internet in the last month. You can use any table you want, just make sure you adjust the code to reflect the right fields in the SQL string. So, if in this example I wanted to know how many times "Access" was used in a search string I can enter Access in the search field and the form shows me all the search strings containing the field "access" and then totals it for me.

Preparation

Create a table named "keywords" with these fields and settings: kwid - autonumber kwcount - number kword - text

Note: you can use any field names you wish, just adjust the code below accordingly.

1. Create a new form using the "keywords" table as the data source.

2. Put all the fields from the table on the form.

3. Arrange them all in a line and add a form detail and set the form type to continuous. I like to put the field headers above the fields in the form header section and place the fields in the detail section.

4. Next create an unbound text box and name it "srch" by adding a text field with no data source.

5. Define the after update event so that after your user enters a string in the srch field the form redisplays with those records that have that string in it.

Private Sub srch_AfterUpdate()

Dim srchstrng As String

srchstrng = Me.srch.Value

sql = " SELECT Keywords.kwid, Keywords.kwcount, Keywords.kword FROM Keywords WHERE (((Keywords.kword) Like '*" & srchstrng & "*'));"

Form.RecordSource = sql

DoCmd.RunCommand acCmdRefreshPage

End Sub

6. To get a total of the count of the keywords you need to add another unbound box in the form footer and make it's data source:

=Sum([kwcount])

Save the form and give it a try.

Access Application Idea: License Compliance solved with Access Database

This customer has to track as many as fifteen different medical licenses and credential verifications for over 3000 employees in the home health care field. They can not have an employee attend to a patient that does not have the correct licenses and credentials due to safety regulations enforced by the state. These licenses all have to be renewed and they have different periods and start dates. The only way that they are able to enforce the license renewals is through payroll. Another words, get your licenses renewals up to date or we can no longer send you to our patients. With payroll weekly, this became a huge problem just tracking the expiration dates and sending out notifications so that they would not loose good employees. This process previously required an average of twenty man hours per week to complete and due to the rush often had mistakes allowing unlicensed and or uncredentialed care givers visit patients. Our customer could have been censored or even lost their license over a violation like this, so they had to have a better way of tracking compliance.

Solution An automated human resources license tracking tool built with MS Access that checks license renewal dates for all licenses and credentials that every care giver employee is required to have in order to see patients. The tool polls the employee data each payroll and checks the renewal dates for all licenses listed in the employee user profile and generates a letter that goes to the employee designating which licenses are nearing renewal dates and or are expired. The system also flags all employees with expired licenses and prevents them from being scheduled for patient care for the specific license if needed.

Benefits The license tracking process now requires only a few minutes per week to complete. The company has virtually eliminated problems with license compliance. Employees license compliance has gone to near 100% and those employees that don't have licenses are no longer sent to the wrong patients.

Feel free as well to let us know if this is helpful or to send ideas for the types of subjects you would like to see covered in the future.

Hot Game !!!