Search field on a form

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?
SteveL13Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
SteveL13Author Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
SteveL13Author Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
SteveL13Author Commented:
This worked perfectly:

.Filter = "instr(Fieldname1 & fieldname2 & fieldname3 & Fieldname4 & fieldname5 & fieldname6 & fieldname7 ,""" & ActiveControl.Text & """) >0"
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
awesome!
0
All Courses

From novice to tech pro — start learning today.