Search and update data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 06 Jul 2007 17:26 GMT

I am trying (in vane) to get a basic db function. Search for a record and
allow the user to update it once it is found.
I do not want to use the basic Filter By Form feature as there are too many
records in the drop downs to make this practical.

The functionality I need is to have the end user be able to search for a
record using a keyword and have all records matching that keyword appear in
the form much like what happens when the Filter By Form feauture is used.

A query based on a form for the keyword returns the table view - not
acceptable.

Any help is appreciated.

Reply to this Message

Dale Fye - 06 Jul 2007 17:38 GMT

John,

Place an unbound textbox (txt_Filter) in the forms header and add a command
button (cmd_Filter) next to it. You might even want to add a second button
(cmd_Clear_Filter) next to that.

In the Click event of the command button enter the following code.

Private sub cmd_Filter_Click

Dim strFilter as string
strFilter = "[field1] Like '*" & me.txt_Filter & "*'"
'you can expand this to multiple fields if you'd like

me.Filter = strFilter
me.FilterOn = True

End Sub

Private sub cmd_Clear_Filter_Click()

me.FilterOn = False

End Sub

HTH
Dale

Signature

Email address is not valid.
Please reply to newsgroup only.

> I am trying (in vane) to get a basic db function. Search for a record and
> allow the user to update it once it is found.
[quoted text clipped - 9 lines]
>
> Any help is appreciated.

Reply to this Message

John - 06 Jul 2007 21:26 GMT

This worked perfectly (once i fixed my typo causing a 2448 runtime error)
Type carefully people.

Thanks Dale

> John,
>
[quoted text clipped - 37 lines]
> >
> > Any help is appreciated.

Reply to this Message

John - 11 Jul 2007 19:02 GMT

I thought I could do without but it is becomming apperant that I need to have
the search look across multiple fields as you suggested. What is the syntax
for expanding it to multiple fields? "OR" did not work and adding an
identical line (w/ field name changed) did not work (just looked in one
field).

Thanks

> John,
>
[quoted text clipped - 37 lines]
> >
> > Any help is appreciated.

Reply to this Message

Dale Fye - 11 Jul 2007 23:02 GMT

John,

Try:

strFilter = "[field1] Like '*" & me.txt_Filter & "*' OR "_
& "[Field2] Like '*" & me.txt_Filter & "*' OR " _
& "[Field3] Like '*" & me.txt_Filter & "*' "

HTH
Dale

>I thought I could do without but it is becomming apperant that I need to
>have
[quoted text clipped - 54 lines]
>> >
>> > Any help is appreciated.

Reply to this Message

John - 12 Jul 2007 00:52 GMT

It works - Thanks again Dale.

> John,
>
[quoted text clipped - 65 lines]
> >> >
> >> > Any help is appreciated.

Reply to this Message

Marshall Barton - 06 Jul 2007 17:58 GMT

>I am trying (in vane) to get a basic db function. Search for a record and
>allow the user to update it once it is found.
[quoted text clipped - 7 lines]
>A query based on a form for the keyword returns the table view - not
>acceptable.

Presumable you have a form to display the data. If so, this
commonly done by using a text box in the form's header
section for users to enter a keyword. Then the text box's
AfterUpdate event can set the form's Filter something like:

Me.Filter="[field to search]=""" & Me.[the text box] & """"
Me.FilterOn = True

Signature

Marsh
MVP [MS Access]

Reply to this Message