Solved

Search field on a form

Posted on 2016-11-28
7
47 Views
Last Modified: 2016-11-28
I have a form that has a subform.  On the main form's header I want to put a textbox field and when the user starts typing in the field, the subform starts to narrow down the possibilities.    The subform itself is a datasheet view form that has 7 fields.

How can this be done?
0
Comment
Question by:SteveL13
  • 4
  • 3
7 Comments
 
LVL 19
ID: 41904466
you can use the CHANGE event of a control to apply a filter to the subform
Private Sub MyFilterControl_Change()
'strive4peace
   With Me.subform_controlname.form
      If Len(ActiveControl.Text) > 0 Then
         '------------------------------------------ use ONE of these:
                   'this will filter for the beginning
         .Filter = "Left(FieldName," & Len(ActiveControl.Text) & ")="""   & ActiveControl.Text & """"
                   'this will filter for any part
         .Filter = "instr(Fieldname,""" & ActiveControl.Text & """) >0"
         '------------------------------------------
         .FilterOn = True
      Else
         .FilterOn = False
      End If
   End With
End Sub

Open in new window


WHERE
subform_controlname is the Name property of the subform control
FieldName is the name of the field you want to filter
-- to search all fields, use Fieldname1 & Fieldname2 & ... and convert if fields are not text

You probably want to reset the filter on the Current event of the mainform
0
 

Author Comment

by:SteveL13
ID: 41904489
This seems to work for just one field.  Here is my code:

Private Sub txtSearch_Change()
'strive4peace
   With Me.subfrmIntrepreters.Form
      If Len(ActiveControl.Text) > 0 Then
         .Filter = "Left(IntCity," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """"
         .FilterOn = True
      Else
         .FilterOn = False
      End If
   End With

End Sub

Open in new window


But I need it to filter on 7 different fields.  Is this possible?
0
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41904506
search from beginning:
         .Filter = "Left(IntCity," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """ OR " _
           "Left(Fieldname2," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """ OR " _
           "Left(Fieldname3," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """ OR ... and so on" 

Open in new window

for this, you probably also want to do this and substitute accordingly:
   dim iLen as integer
   iLen=Len(ActiveControl.Text) 

Open in new window

search any part:
.Filter = "instr(Fieldname1 & fieldname2 & fieldname3 & Fieldname4 & fieldname5 & fieldname6 & fieldname7 ,""" & ActiveControl.Text & """) >0"

Open in new window

0
Back Up Your Microsoft Windows Server®

Back up 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.

 

Author Comment

by:SteveL13
ID: 41904540
I just entered this and it all turns red... ????

         .Filter = "Left(IntCity," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """ OR " _
           "Left(InterpreterID," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """ OR " _
           "Left(IntFullName," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """ OR " _
           "Left(IntStreetNumber," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """ OR " _
           "Left(IntAptOrOther," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """ OR " _
           "Left(IntState," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """ OR " _
           "Left(IntZipCode," & Len(ActiveControl.Text) & ")=""" _
            & ActiveControl.Text & """ OR "
0
 
LVL 19
ID: 41904569
sorry, someone came to the door so I just clicked Submit

everything should start with & ... ie: & "Left(IntStreetNumber," ...

take away the last OR

also, if you have fields that are stored as numbers then you will need to convert them
for instance, perhaps: format(InterpreterID,'#')
0
 

Author Closing Comment

by:SteveL13
ID: 41904572
This worked perfectly:

.Filter = "instr(Fieldname1 & fieldname2 & fieldname3 & Fieldname4 & fieldname5 & fieldname6 & fieldname7 ,""" & ActiveControl.Text & """) >0"
0
 
LVL 19
ID: 41904588
awesome!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

808 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