Code not working on subform!

[Cron]

Hi All,
I have a text box called lookup that filters form results after update
like so:

Private Sub lookup_AfterUpd ate()
DoCmd.ApplyFilt er , "FirstOfSur name LIKE '" & lookup & "*'"
pagetitle.Capti on = "Attendees matching '" & lookup & "'"
delbtn.Visible = True
delbox.Visible = True
tip.Visible = False
End Sub


This works great as a normal form but when I make it a subform of the
switchboard, The code does not work! I get prompted for the var
FirstOfSurname and then I get a message saying the object being
referred to has closed or does not exist. Anyone got any ideas how to
solve this please?

Thanks a lot!
Ciarán

[Allen Browne]

Re: Code not working on subform!

Instead of the line:
DoCmd.ApplyFilt er , "FirstOfSur name LIKE '" & lookup & "*'"
try:
Me.Filter = "FirstOfSur name LIKE '" & lookup & "*'"
Me.FilterOn = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cron" <cronoklee@hotm ail.comwrote in message
news:17780843-1608-4203-8308-6ddb890f4a8e@k3 0g2000hse.googl egroups.com...
Hi All,
I have a text box called lookup that filters form results after update
like so:

Private Sub lookup_AfterUpd ate()
DoCmd.ApplyFilt er , "FirstOfSur name LIKE '" & lookup & "*'"
pagetitle.Capti on = "Attendees matching '" & lookup & "'"
delbtn.Visible = True
delbox.Visible = True
tip.Visible = False
End Sub


This works great as a normal form but when I make it a subform of the
switchboard, The code does not work! I get prompted for the var
FirstOfSurname and then I get a message saying the object being
referred to has closed or does not exist. Anyone got any ideas how to
solve this please?

Thanks a lot!
Ciarán

[Cron]

Re: Code not working on subform!

On Aug 8, 9:49 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

Instead of the line:
    DoCmd.ApplyFilt er , "FirstOfSur name LIKE '" & lookup & "*'"
try:
    Me.Filter = "FirstOfSur name LIKE '" & lookup & "*'"
    Me.FilterOn = True
>
--
Allen Browne - Microsoft MVP.  Perth, Western Australia

Brilliant! I've no idea why but that actually worked - thanks Allen!

One other quick question - I would like the text box to filter the
results as I type into it but no matter what keystroke event I use, it
seems the typed letter has not yet landed in the box when the function
runs. Then when it runs, it selects the text in the box, so the next
letter typed replaces the box contents, preventing the natural typing
of a word.
Anyone got a way around this?

Thanks,
Ciarán

[Allen Browne]

Re: Code not working on subform!

Okay, firstly it worked because we were specify about *which* form to apply
the filter to (i.e. 'Me' is the form containing the code.) This is the best
way to handle subforms, which are not open in their own right, and so the
more generic ApplyFilter has problems.

You are probably using the Change event if you are trying to get a
char-by-char response. As you found, the Value of the text box has not been
updated when this event fires, and so you need to explicitly refer to its
Text property, rather than its Value (the default property.) This looks
like:
lookup.Text

But there are several other things to sort out here to get that to work.
Particularly, the text box's Value does get updated when you apply the
filter, and so the wrong thing is selected (e.g. if the cursor is not at the
end of the text box.)

Give this a try:
Find as you type - Filter forms with each keystroke
at:

http://allenbrowne.com/AppFindAsUType.html

There's a sample database to download and see how it works. You choose what
field to look in (from a combo), and what value to look for (using a text
box), and it filters with each keystroke.

There's a lot of code in the utility, but you don't need to change any of
it. Just copy it into your database, copy the combo and text box onto your
form, and set one property. It figures it all out from there. Works find
with subforms.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cron" <cronoklee@hotm ail.comwrote in message
news:47827048-dca9-49ad-850d-ba2a82a552a9@y2 1g2000hsf.googl egroups.com...
On Aug 8, 9:49 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

Instead of the line:
DoCmd.ApplyFilt er , "FirstOfSur name LIKE '" & lookup & "*'"
try:
Me.Filter = "FirstOfSur name LIKE '" & lookup & "*'"
Me.FilterOn = True
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Brilliant! I've no idea why but that actually worked - thanks Allen!

One other quick question - I would like the text box to filter the
results as I type into it but no matter what keystroke event I use, it
seems the typed letter has not yet landed in the box when the function
runs. Then when it runs, it selects the text in the box, so the next
letter typed replaces the box contents, preventing the natural typing
of a word.
Anyone got a way around this?

Thanks,
Ciarán

[Cron]

Re: Code not working on subform!

On Aug 8, 1:00 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

Okay, firstly it worked because we were specify about *which* form to apply
the filter to (i.e. 'Me' is the form containing the code.) This is the best
way to handle subforms, which are not open in their own right, and so the
more generic ApplyFilter has problems.
>
You are probably using the Change event if you are trying to get a
char-by-char response. As you found, the Value of the text box has not been
updated when this event fires, and so you need to explicitly refer to its
Text property, rather than its Value (the default property.) This looks
like:
    lookup.Text
>
But there are several other things to sort out here to get that to work.
Particularly, the text box's Value does get updated when you apply the
filter, and so the wrong thing is selected (e.g. if the cursor is not at the
end of the text box.)
>
Give this a try:
    Find as you type - Filter forms with each keystroke
at:
   http://allenbrowne.com/AppFindAsUType.html
There's a sample database to download and see how it works. You choose what
field to look in (from a combo), and what value to look for (using a text
box), and it filters with each keystroke.
>
There's a lot of code in the utility, but you don't need to change any of
it. Just copy it into your database, copy the combo and text box onto your
form, and set one property. It figures it all out from there. Works find
with subforms.
>
--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Cron" <cronok...@hotm ail.comwrote in message
>
news:47827048-dca9-49ad-850d-ba2a82a552a9@y2 1g2000hsf.googl egroups.com...
On Aug 8, 9:49 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:
>

Instead of the line:
DoCmd.ApplyFilt er , "FirstOfSur name LIKE '" & lookup & "*'"
try:
Me.Filter = "FirstOfSur name LIKE '" & lookup & "*'"
Me.FilterOn = True

>

--
Allen Browne - Microsoft MVP. Perth, Western Australia

>
Brilliant! I've no idea why but that actually worked - thanks Allen!
>
One other quick question - I would like the text box to filter the
results as I type into it but no matter what keystroke event I use, it
seems the typed letter has not yet landed in the box when the function
runs. Then when it runs, it selects the text in the box, so the next
letter typed replaces the box contents, preventing the natural typing
of a word.
Anyone got a way around this?
>
Thanks,
Ciarán

Cool, thanks a lot Allen - I'll check it out!
Ciarán

[John Mishefske]

Re: Code not working on subform!

On Aug 8, 1:00 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

>>Instead of the line:
>>DoCmd.ApplyFi lter , "FirstOfSur name LIKE '" & lookup & "*'"
>>try:
>>Me.Filter = "FirstOfSur name LIKE '" & lookup & "*'"
>>Me.FilterOn = True
>>--
>>Allen Browne - Microsoft MVP. Perth, Western Australia

>Brilliant! I've no idea why but that actually worked - thanks Allen!

Just a FYI that if your "lookup" value could contain a single quote
(perhaps O'Hearn or d'Jour) then you'll get an error.

If that is a concern you could do this:

Me.Filter = "FirstOfSur name LIKE """ & Lookup & "*"""
Me.FilterOn = True

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' Microsoft MVP 2007, 2008
'--------------------------