Re: Search function

Dave,
I'm a little confused by your use of "RowSource" to
effect the change in display. There isn't any "RowSource"
for forms, nor the controls for text-boxes. Do you mean
to change the "RecordSource" for the form?
Bill


"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FA0EE969-70C3-4373-A744-F526C5F492F2@xxxxxxxxxxxxxxxx
The same approach will work for either a form or a subform. It is just a
matter of what you want to do.
Hope I was able to help.
--
Dave Hargis, Microsoft Access MVP


"Bill" wrote:

I see what you mean. I didn't use a sub-form for this
application. Rather, I used the primary form and
simply set the filter and required. That, of course,
changes the contents of the display in accordance
with what was searched. Since a sub-form would
be simple to create, I'll ask the user what their
preference would be between the two approaches.

BTW, I saved your suggestion for general purpose
searches on a single record form for future use.

Thanks,
Bill



"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DAE3DB64-A661-44D1-847D-AD2250717A27@xxxxxxxxxxxxxxxx
The filtering you have figured out. The way I would use it would be to
modify
the subform's row source. It should not require a requery. A form
will
requery when you change the row source. You can either create a
different
stored query or you can create it in VBA, which ever is easiest for
you.

Me.!MySubformName.Form.Rowsource = ????
--
Dave Hargis, Microsoft Access MVP


"Bill" wrote:

A little experimentation answered that question, though
it requires:

Me.Filter = "strIn(1,[myfield]," & strShow & ") > 0"

Where strShow is a string variable set upon user's
request to show all records where "MyField" contains
the substring in strShow.

Bill


"Bill" <billstanton@xxxxxxxx> wrote in message
news:stGdnZf3s6OeWnjbnZ2dnUVZ_hOdnZ2d@xxxxxxxxxxxxxxx
What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BB813696-2775-40A3-9016-414FF8D03D26@xxxxxxxxxxxxxxxx
It would be similar, but with some differences.
If occurances of the substring are found in more than one record,
do
you
want to filter the subform to show all that have an occurance in
that
field?
--
Dave Hargis, Microsoft Access MVP


"Bill" wrote:

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill



"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:64AB2646-CEFB-4C51-88AB-75F6095C013F@xxxxxxxxxxxxxxxx
The most common approach for a search is to use an unbound combo
box
that
has
its row source based on the field you want to search in. You
use
the
combo's
After Update event to look for the record and make it the form's
current
record.

Let's say we have a form for entering and editing Employee
information.
We
want to be able to look up an employee by name, but the primary
key
of
the
Employee table is the EmployeeID. So for the combo, we need
both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name, but
search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the
name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


"Bill" wrote:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill
















.

Relevant Pages

  • Re: Search function
    ... form, not a sub form, it may be better to use the form' s filter property. ... Dave Hargis, Microsoft Access MVP ... "Bill" wrote: ...
    (microsoft.public.access.formscoding)
  • Re: Search function
    ... form, not a sub form, it may be better to use the form' s filter property. ... Dave Hargis, Microsoft Access MVP ... "Bill" wrote: ...
    (microsoft.public.access.formscoding)
  • Re: Search function
    ... Dave Hargis, Microsoft Access MVP ... "Bill" wrote: ... simply set the filter and required. ... Let's say we have a form for entering and editing Employee ...
    (microsoft.public.access.formscoding)
  • Re: Search function
    ... Dave Hargis, Microsoft Access MVP ... "Bill" wrote: ... effect the change in display. ... simply set the filter and required. ...
    (microsoft.public.access.formscoding)
  • Re: Search function
    ... simply set the filter and required. ... Dave Hargis, Microsoft Access MVP ... "Bill" wrote: ... Let's say we have a form for entering and editing Employee ...
    (microsoft.public.access.formscoding)