Quick question: how easy is it to search in an Access form?

[Dave Linsalata]

Hey all,

Really quick question: How easy is it for a newbie to Access to create the
following form:

Picture a page with a few hundred rows of data, with each row starting with
a name and havingn about 10-15 different columns. Now have a static area up
top with a text box. Into this text box, I can type a name and have it
filter the rows below on the contents of the text box. This can be done
either automatically or after I click a button.

Is this simply done? If so, any tips? Or will this require a fair bit of
coding...

Thanks!
Dave

 

[Rick Brandt]

Hey all,

Really quick question: How easy is it for a newbie to Access to create the
following form:

Picture a page with a few hundred rows of data, with each row starting with
a name and havingn about 10-15 different columns. Now have a static area up
top with a text box. Into this text box, I can type a name and have it
filter the rows below on the contents of the text box. This can be done
either automatically or after I click a button.

Is this simply done? If so, any tips? Or will this require a fair bit of
coding...

Two lines of code. In the AfterUpdate event of the TextBox...

Me.Filter = "[NameField] = '" & Me.TextBoxName & "'"
Me.FilterOn = True

 

[Dave Linsalata]

Woah, very cool! Much easier than in Excel...

That said, I'm running into a problem on how to reset the filtering. I
stuck upon the idea of turning off the filtering with a button, and I used
the code:
Me.FilterOn = False

That worked..for a second. In other words, when I hit the button I see the
records flash up...and then they disappear again. Do I need to set Me.Filter
= to something...like "all"?

Thanks!
D

Hey all,

Really quick question: How easy is it for a newbie to Access to create the
following form:

Picture a page with a few hundred rows of data, with each row starting with
a name and havingn about 10-15 different columns. Now have a static area up
top with a text box. Into this text box, I can type a name and have it
filter the rows below on the contents of the text box. This can be done
either automatically or after I click a button.

Is this simply done? If so, any tips? Or will this require a fair bit of
coding...

Two lines of code. In the AfterUpdate event of the TextBox...

Me.Filter = "[NameField] = '" & Me.TextBoxName & "'"
Me.FilterOn = True

 

[Dave Linsalata]

Err, and a 2 quick follow-ups

1) I tried setting Me.Filter = "*", hoping the wildcard would work, but that
didn't fly...

2) If one of the names was "Linsalata" and I did a search on "Linsalata,"
the correct record would show up. Ideally, I'd like to be able to search on
"Lin" and have all possibilities come up. I tried doing something like:
Me.Filter = "[Last] = '" & Me.TextBoxSearch & "*" & "'"

...but it broke it. I'm currently researching more about wildcards and
filtering, since I think the concept is right but the syntax is wrong.

Thanks
D

Hey all,

Really quick question: How easy is it for a newbie to Access to create the
following form:

Picture a page with a few hundred rows of data, with each row starting with
a name and havingn about 10-15 different columns. Now have a static area up
top with a text box. Into this text box, I can type a name and have it
filter the rows below on the contents of the text box. This can be done
either automatically or after I click a button.

Is this simply done? If so, any tips? Or will this require a fair bit of
coding...

Two lines of code. In the AfterUpdate event of the TextBox...

Me.Filter = "[NameField] = '" & Me.TextBoxName & "'"
Me.FilterOn = True

 

[Rick Brandt]

Woah, very cool! Much easier than in Excel...

That said, I'm running into a problem on how to reset the filtering.
I stuck upon the idea of turning off the filtering with a button, and
I used the code:
Me.FilterOn = False

That worked..for a second. In other words, when I hit the button I
see the records flash up...and then they disappear again. Do I need
to set Me.Filter = to something...like "all"?

Thanks!
D

Me.FilterOn = False

should show all records. Try...

Me.Filter = ""
Me.FilterOn = True

It might seem counterintuitive, but if you apply a blank filter the result is
the same as having no filter applied.