Pull up records based on search criteria other than main ID#?

  • Thread starter Thread starter StargateFanFromWork
  • Start date Start date
S

StargateFanFromWork

I just can't figure out how to do this. A colleague created a db
solution and I'm to help with creating the forms. So far, so good
except for create a search button of the forms (via command button
feature) that will pull up records based on search on any field _other_
than the record ID # Access assigns to a record. Making a button for
that was easy, but can't figure out how to search via any of the other
fields. What am I doing wrong please? thanx.
 
You are not giving us enough info.

When you click this 'search' button, does a new form come up that lists all
the available fields and allows the user to make an entry in some/all of
them?

Is there a field in your form where you make an entry, and then the user
clicks this 'search' button? If so, what should happen at this time?

I can think of at least ten different scenarios that you could have on a
form to help users dynamically search for records that match. You would
have to tell us what you envision happening when this button is pressed, how
the user will tell the system what string to look for, and how the user will
tell the system which field(s) that string should appear in.

More details please...
 
Thanks for the feedback on what to ask for. It's rather simple in that
regard, it would be neat to have users to be able to do a search on any
field we choose. i.e., if I put a find button beside the field for the old
docket #, that it pulls up a search of that field alone. If the search
button is beside the file description, that the user can then search based
on a particular string that would be found in the description of the needed
file/docket. I know that I'm restricted because I'm a Filemaker developer
and that there are going to be huge differences but I can do that so easily
in FMP so know that there must be an equivalent ability in Access. I think
that it's perhaps in how I choose to make the button (?). I've been using
the "command button" option in the toolbox because that's what a search in
the help file pulled up. But I can only seem to get the search to find the
main "record ID" number and not a pertinent search to any field of my choice
based on the contents of any particular field. Is there some other way,
just as easy, to create search buttons for any of the fields in our
solution? Thanks and apologize for the newbiness of the question. I'll do
better down the road when I become more familiar with Access and don't get
so muddled because of my FMP experience.
 
Have you tried the "Filter by Form" built in feature? That allows you to
use your normal data entry form to search for a value in any field on the
form. You can search by one criteria against one field or multiple
criteria against multiple fields.
hth
Al Camp
 
(Answering 2 messages. First msg below and second msg interspersed.)

Oh, thanks for teaching me that. That's good to know.

It's not enough for this task, though. We'd had to "filter by form" all the
time in Excel and that's what the ops hated about the Excel "database". We
need to find a record just by hopefully clicking a button and typing in the
record # and then that record comes up. Yes, we are switching over to the
Access-generated numbers but we have quite a few hundred records on the old
system so we created a field to input those old numbers. I'll have a mutiny
if we have to use the filter to find those old records <g>.

In FileMaker it's so easy to set up a search for any item in any field, so
know it can be done; it's just how to do this in Access?

Thanks much. :oD

Al Camp said:
Have you tried the "Filter by Form" built in feature? That allows you to
use your normal data entry form to search for a value in any field on the
form. You can search by one criteria against one field or multiple
criteria against multiple fields.
hth
Al Camp
Click to expand...

No. Just a box that gives us ability to search for the Access-generated
record ID #. I've looked for other items on that box, but nothing else
comes up.

Yes, I put in a search button using the features in the "Command button"
from the toolbox. It _only_ pulls up the Access-generated ID # and not a
record we manually put in based on the old spreadsheet we used to use.
There seems to be _only_ the option of searching on that field.

Just to search on _any_ field based on the contents of that field. In this
case, yes, it's just a numerical entry, but with other fields they might
need to search on a particular word or string to try to find a record.
 
I just saw today what is happening. When one creates a search button using
the command button feature in the toolbox, when you actually go to use it,
the search is dependent on the field you happen to be in. See, accidentally
and without realizing it, I initiated for the first time a search while I
was in a field other than the default one that appears on opening the form.
What prompted me was the "Look In:" pulldown in usual "Find and Replace" box
that came up. The values for the first time were different. Sure enough,
trying that search button but while in a different field each time would
allow me to find a record by searching on that field.

But this is no good. We must make search buttons specific to a particular
field, whatever it might be, right off the bat without having to select the
field in question. Is there any way to do this? This would be much more
user friendly and a whole lot less hassle!

Thanks much! And thanks for everyone's patience. I'm really new to Access
esp. building up manually from the tables the way my colleague is doing <g>.
Cheers. :oD

[snip]
 
You can code the find yourself if yo
want

The code below was not tested, it is pseudocode )but I have use
similar before)

dim tempRec as dao.recordse
set temprec =me.recordse
temprec.findFirst "FIELD_NAME='text'" (if you are searching for
number don't use single quotes
set me.recordset=tempRe

There is also a findNext function

Does this help?[/i
 
Back
Top