Need help - trying to create a searchable list

[Derek Jones]

I am currently working on an application for our Business Office to
easily search for new budget codes that we have to establish due to an
administrative system conversion we are currently undergoing. They
would like to be able to find a new code via searching by old code or
searching by the object name. I have the search by old code form done
exactly the way they want it. For the search by object name form, I
have it set up with a combo box and a subform with three fields (old
code, name, new code). I set it up so you can select a name from the
combo box and it would automatically display only the records with that
name in the subform. However, the names I am working with are rather
long, and the department I'm creating this application for would like
to be able to type in a portion of the name and be able to see all
records that contain the text that they enter (regardless of where the
text is located in the field). I've tried using the command button
wizard, but it's no help. I've tried creating macros, but was
unsuccessful there as well. Can someone out there offer me any
suggestions? Thanks in advance.

Derek Jones
Software Support Specialist
Shenandoah University
Winchester, VA

[pietlinden]

If you use a combobox, you can change the rowsource on the fly. and
then in column(1), which is second column, you can show whatever you
like and if your LimitToList option is set to true and AutoExpand is
true, the user can just start typing the value he wants and the
Combobox will scroll to that section of the list.

you could set the rowsource on the fly and then requery it.

[jimfortune]

Derek Jones wrote:

I am currently working on an application for our Business Office to
easily search for new budget codes that we have to establish due to an
administrative system conversion we are currently undergoing. They
would like to be able to find a new code via searching by old code or
searching by the object name. I have the search by old code form done
exactly the way they want it. For the search by object name form, I
have it set up with a combo box and a subform with three fields (old
code, name, new code). I set it up so you can select a name from the
combo box and it would automatically display only the records with that
name in the subform. However, the names I am working with are rather
long, and the department I'm creating this application for would like
to be able to type in a portion of the name and be able to see all
records that contain the text that they enter (regardless of where the
text is located in the field). I've tried using the command button
wizard, but it's no help. I've tried creating macros, but was
unsuccessful there as well. Can someone out there offer me any
suggestions? Thanks in advance.

Derek Jones
Software Support Specialist
Shenandoah University
Winchester, VA

You'll probably want to use the LIKE operator in SQL to get the object
names containing the substring typed in by the user. Since you are
using a substring rather than the full string, you aren't going to be
able to use the form/subform's built-in field linking. It looks like
you're going to have to change the subform's RecordSource dynamically.
Maybe put a command button next to the textbox containing the
substring, that creates the SQL string and changes the RecordSource of
the subform. The code behind the command button's Click Event would
look something like (air code):

SubformObjectNa mes.Form.Record Source = "SELECT OldCode, ObjectName, _
NewCode FROM tblEquivalents WHERE [ObjectName] LIKE _
'*" & txtSubstring.Va lue & "*'"
SubformObjectNa mes.Form.Refres h
SubformObjectNa mes.Form.Repain t

Name is a reserved word so it's not a good idea to name a field 'Name.'

James A. Fortune