By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Code not working on subform!

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

Private Sub lookup_AfterUpdate()
DoCmd.ApplyFilter , "FirstOfSurname LIKE '" & lookup & "*'"
pagetitle.Caption = "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
Aug 8 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Instead of the line:
DoCmd.ApplyFilter , "FirstOfSurname LIKE '" & lookup & "*'"
try:
Me.Filter = "FirstOfSurname 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" <cr*******@hotmail.comwrote in message
news:17**********************************@k30g2000 hse.googlegroups.com...
Hi All,
I have a text box called lookup that filters form results after update
like so:

Private Sub lookup_AfterUpdate()
DoCmd.ApplyFilter , "FirstOfSurname LIKE '" & lookup & "*'"
pagetitle.Caption = "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

Aug 8 '08 #2

P: n/a
On Aug 8, 9:49*am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
Instead of the line:
* * DoCmd.ApplyFilter , "FirstOfSurname LIKE '" & lookup & "*'"
try:
* * Me.Filter = "FirstOfSurname 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
Aug 8 '08 #3

P: n/a
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" <cr*******@hotmail.comwrote in message
news:47**********************************@y21g2000 hsf.googlegroups.com...
On Aug 8, 9:49 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
Instead of the line:
DoCmd.ApplyFilter , "FirstOfSurname LIKE '" & lookup & "*'"
try:
Me.Filter = "FirstOfSurname 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

Aug 8 '08 #4

P: n/a
On Aug 8, 1:00*pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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...@hotmail.comwrote in message

news:47**********************************@y21g2000 hsf.googlegroups.com...
On Aug 8, 9:49 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
Instead of the line:
DoCmd.ApplyFilter , "FirstOfSurname LIKE '" & lookup & "*'"
try:
Me.Filter = "FirstOfSurname 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
Aug 9 '08 #5

P: n/a
On Aug 8, 1:00 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>>Instead of the line:
DoCmd.ApplyFilter , "FirstOfSurname LIKE '" & lookup & "*'"
try:
Me.Filter = "FirstOfSurname 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 = "FirstOfSurname LIKE """ & Lookup & "*"""
Me.FilterOn = True

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' Microsoft MVP 2007, 2008
'--------------------------
Aug 9 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.