Problems executing a filter on a sb form 1

[JamesBBB]

Hi All,

I have a problem with filters and sub forms, I just cannot get the code to work.

when I use the filter on the subform as a stand alone form the filter works, the Button code I use is:-

as1 = "[Staff Number] like '" & Pubstaffno & "'"
DoCmd.GoToControl "staff number"
DoCmd.ApplyFilter , as1
Me.FilterOn = True

The Main form is called VISITORS and the sub form itself is called VISITS SUBFORM CONT.

When I execute the filter code when it is actually a sub form, it fails, because the subform is now contained within the main form.

I have tried all sorts of changes to try and make it work, but nothing I do seems to work. If anyone can help I would be extremely grateful.

many thanks

James

 

[PHV]

Where is located the button (mainform or subform) ?
What is Pubstaffno ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JamesBBB]

The Button is on the subform

 

[PHV]

Again, What is Pubstaffno ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

it fails
What happens ?
Any error message ? Computer crash ? Unexpected behaviour ? ...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JamesBBB]

Im sorry I should have explained a little more, sorry.
OK, Pubstaffno is a public variable that contains the numeric staff number selected for the filter. So when the user selects the member of staff from the combo box, the staff number is stored in a public variant for use in the filter statement.

The Error I get when using the filter code from the subform with the main form is:-

"The action or method is invalid becuase the form or report isn't bound to a table or query"

Hope this offers some help

thanks

James

 

[PHV]

the form or report isn't bound to a table or query
Is that true ?
You may try this:
Me.Filter = "[Staff Number] Like '" & Pubstaffno & "'"
Me.FilterOn = True

BTW, if [Staff Number] is numeric then use this:
Me.Filter = "[Staff Number]=" & Pubstaffno

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JamesBBB]

FANTASTIC !!!!

Works first go, thanks a million, I just cant thank you enough. that little problem has been giving me grief for quite a few hours now.

It feels really good now I see it working.

thanks again PHV

cheers

James

BTW Yes it was numeric, so thanks for little addon at the end.
Awesome.