|
 |
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.
Customer Success: Health Care Company Solves License Compliance with Access Database
Situation
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.
You are receiving this newsletter because you requested more information from us on our website. Please let us know if you want to opt out of receiving this newsletter by clicking here .
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 by clicking here.
Category: SoftwareAccess DatabaseTip: Create a user defined search field for a list box was written by:Author:Jon WatsonAdded: Sat, 20 Oct 2007 07:40:24 -0400 This Article Has Been Read 1330 times View all Jon Watson's articlesAbout the Author: Jon Watson is the founder of Biomation Systems, Inc. With 26 years experience helping Fortune 500 companies with process improvement he formed BioMation to bring the same expertise to smaller companies that need the same improvements at an affordable price. |
More Articles About Software
Not What You Were Looking For?
 Moxie Drive Expressions
 Submit Articles
 Contact us
Add us to favorites
Hint - Hold "T" button down and right click to open Sphinn and Subbmitt in a new window. 
|