Typing in a field to find records that match

[Guest]

I have a continuous form that displays names of people alphabetically. I
would like to create a text box on the same form that will allow the user to
type in the first letter (or 2 or 3 letters) of the last name and have the
form scroll to find those records on the form. Is this possible?

 

[Allen Browne]

Couple of approaches, Sarah

One is to use an unbound combo to find the record like this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

Here's another one that does not require to you write any code at all:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
You select the field you want to search (in a combo), and type the name you
want to find (in a text box.) To set it up, you copy the combo and text box
to your form, and set one property.

 

[Guest]

Thanks for the help...the 2nd option is awesome! However, I do have a
problem...I see the combo box and text boxes in the footer, and the combo box
shows the fields from my table, but I can't select anything in the combo box
or type anything in the text box to search for.

When I chose compile in VBA as you said, I got an error - "Method or data
member not found." It highlighted the cboMoveTo in the following line:

If Not IsNull(Me.cboMoveTo) Then
'Save before move.

What am I missing?

 

[Allen Browne]

Okay, the first link referred to the combo by the name cboMoveTo. If you did
not set its Name property, the code won't work.

Of if you have the code from the first link (without the combo), it won't
work.

 

[Guest]

I figured out what I did wrong. I started out by trying to do the first
option and then saw how neat the 2nd was, so I did that also without deleting
the first code (it was late). Once I deleted the first code, I no longer got
the error when I was compiling. At first it still wouldn't work, but I
figured out that it was because I had the form's property for Allow Edits set
to no. Now it works great.

This is absolutely brilliant and I plan to incorporate it into some other
work I've done. Thank you so much!!

 

[Guest]

I guess I do have one more question if you would be so kind...

Is there a way to make the combo box default to a certain field? Right now
it goes to my field called Inactive Date, but I would like it to default to
Last Name - I'm assuming that is what my users will search for most often.

 

[Allen Browne]

The FindAsYouType utility goes through all the controls on your form to
figure out the tab order of the controls and the names the user knows them
by, and it defaults to the first field in the tab order.

If you want it to choose a different field, you would need to code that. You
could try setting the On Load property of the form to:
[Event Procedure]
and entering the code as:
Call FindAsUType(Me)
Me.cboFindAsUTypeField = "LastName"

Haven't tested that.

 

[Guest]

I tried it, but it just left the combo box blank, which is fine, too. I
actually just changed the tab order for the form so Last Name would be first.
All the field are locked anyway, so there is no reason for anyone to need to
tab through it. Thanks again for all your help. I'll leave you alone now.

The FindAsYouType utility goes through all the controls on your form to
figure out the tab order of the controls and the names the user knows them
by, and it defaults to the first field in the tab order.

If you want it to choose a different field, you would need to code that. You
could try setting the On Load property of the form to:
[Event Procedure]
and entering the code as:
Call FindAsUType(Me)
Me.cboFindAsUTypeField = "LastName"

Haven't tested that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I guess I do have one more question if you would be so kind...

Is there a way to make the combo box default to a certain field? Right
now
it goes to my field called Inactive Date, but I would like it to default
to
Last Name - I'm assuming that is what my users will search for most often.

 

[Guest]

Allen -

You helped me with this search utility a while back, and I am now having a
problem with it. The database has been put on a shared drive and some of the
machines will bring up an error when we try to open the form that has the
search utility on it. We get this message, "Database or project contains a
missing or broken reference to the file 'MSCOMCT2.OCX' version 2.0"

If you click on OK, it opens VB and goes right to the following:
'Remove the trailing separator, and assign to the RowSource of
cboFindAsUTypeField.
lngI = Len(strOut) - Len(mstrcSep)
If lngI > 0 Then
With frm.cboFindAsUTypeField
.RowSource = Left(strOut, lngI)
.Value = .ItemData(0) 'Initialize to the first
item in the list.
End With
bResult = True 'Return True: the list loaded
successfully.
End If
End If

In the line .RowSource = Left(strOut, lngI), the word Left is highlighted.

I don't know what to do other than remove the search utility. It isn't
affecting all the computers, but I wasn't sure what the cause was to begin
with. Any ideas? Any help would be very much appreciated! Thank you.

The FindAsYouType utility goes through all the controls on your form to
figure out the tab order of the controls and the names the user knows them
by, and it defaults to the first field in the tab order.

If you want it to choose a different field, you would need to code that. You
could try setting the On Load property of the form to:
[Event Procedure]
and entering the code as:
Call FindAsUType(Me)
Me.cboFindAsUTypeField = "LastName"

Haven't tested that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I guess I do have one more question if you would be so kind...

Is there a way to make the combo box default to a certain field? Right
now
it goes to my field called Inactive Date, but I would like it to default
to
Last Name - I'm assuming that is what my users will search for most often.

 

[Allen Browne]

When you have a broken reference, it messes up all your code - even basic
functions like Left().

To remove the broken reference, choose References on the Tools menu (in the
code window). Uncheck the box beside the reference marked MISSING.

Then test if your code compiles without that reference. Choose Compile on
the Tools menu.

More on fixing reference problems:
http://allenbrowne.com/ser-38.html

 

[Guest]

Thanks for your quick response. I will take a stab at this and see what I
can do. Doesn't sound too difficult...even for a basic user like myself.