Solved

Access 2007 Form - Find Command or Search Text Box Needed

Posted on 2009-05-06
1
721 Views
Last Modified: 2013-11-28
I have a form in Access 2007 that uses a parameter query to display a record set.  In this case, based on the fields; YEAR and SCHOOL.  Each record set will normally display several hundred student records.  Within that record set, I need to find students by searching the name field.  I will generally have to update multiple records for a particular school, so the name field cannot be part of the forms parameter query.

The FIND command works wells using the any part of the field option, but it has two drawbacks.  First, after updating a record, the user has to click back into the NAME field to set the Look in: Otherwise, Look in: defaults to the last field with focus.   Second, the FIND dialog box covers much of the form and the user constantly has to move it out of the way.

There will also normally be multiple records with matching names, so there has to be a Next Record function to scroll through the matches.

If I could get the Find Look in: option to remain on the NAME field, even after clicking in a different field on the form; that would work.  Obviously, that is not the way the FIND dialog is intended to work.

Filtering is too slow and cumbersome.  

Summary:

I want a way to make the FIND command default and remain looking in the NAME field.  Or, a textbox search on the NAME field and the ability to Next Record through the matching subset of records.
0
Comment
Question by:Quig653
1 Comment
 
LVL 1

Accepted Solution

by:
Quig653 earned 0 total points
Comment Utility
I solved the issue with a textbox and one command button using docmd.findrecord and another button using docmd.findnext.

I'll clean it up later and try to incorporate both into one button.
Private Sub Command122_Click()

On Error GoTo Err_Command122_Click
 

Dim SearchValue As String
 

SearchValue = Trim(Me.FindName)

If Len(SearchValue) > 0 Then

    DoCmd.GoToControl "Name"

    DoCmd.FindRecord SearchValue, acAnywhere, , acSearchAll, , acCurrent

    

End If
 
 

Exit_Command122_Click:

    Exit Sub
 

Err_Command122_Click:

    MsgBox Err.Description

    Resume Exit_Command122_Click

    

End Sub

Private Sub Command123_Click()

On Error GoTo Err_Command123_Click
 
 

    Screen.PreviousControl.SetFocus

    DoCmd.FindNext
 

Exit_Command123_Click:

    Exit Sub
 

Err_Command123_Click:

    MsgBox Err.Description

    Resume Exit_Command123_Click

    

End Sub

Open in new window

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now