filter & command button question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

I have a form that is based on a stored parameter query ( this is an adp
project ).
It works fine when I first open the form from a main menu. I am prompted for
a client number. However, when I want to look up another client number, I
can click on a button "change client " which has as it's event
("=openform("client ")... Well, I get a message telling me"apply/filter
action contains a filter name that can't be applied". If I right-click on
the button, I can remove the filter and I am prompted. I dont want to have
users to that. I also tried putting in the event procedure - a close
statement for the form , then I reopen it. That works ok, but how can I get
rid of that filter without closing the form? its driving me nuts. Any
suggestions?
 
What kind of data source is used for the form? A parameterised query? A
not-parameterised query? A parameterized stored procedure? A
not-parameterized stored procedure? A dynamically build SQL Select
statement?

(Personally, I don't understand what you mean by « stored parameter
query ».)

How is the parameter applied the first time and then the second time? Is
there any Requery performed?
 
It is a stored procedure. It prompts the user for input. It gets applied
the first time when the form is opened. The record source for this form is a
stored query(prompting the user for input). Then, the command button is
supposed to be used to prompt the user for another client number. So, the
form gets re-opened when this button is clicked.
 
Maggie,
If you are using a parameterized stored proc as the
underlying recordset, simply change the input param when you click the
"change client" button.

I have a form with a control that allows the user to enter a jobsite name.
They then click a button to populate the subform with matching sites.....

Something like this...
Private Sub btnFetchJob_Click()
Me.sub_SiteLookUpBySite.Form.RecordSource = "dbo.rs_JobSiteBySite2"
Me.sub_SiteLookUpBySite.Form.InputParameters = Chr(34) & Me.tbJobLookUp &
Chr(34)
End Sub

HTH,
bob.
 
I set the input parameter to blanks, but now I dont get prompted at all.
The fields on the form are bound to the output from this query. Should I
still be able to do what you're saying? Im new at this, so I probably dont
explain things very well right now. I should have explained that the fields
 
Hi John,

I put the following code for the sub_CMDclient :
Me.Form.RecordSource = Me.Form.RecordSource
It's along the lines of what you told me to do. It works, but can you
explain why? Is it because it reset the value ? I'm confused on filters -
for forms , reports,etc. There's a lot more to it than I thought. Well,
thanks for your help. I appreciate it.

maggie
 
Hello Maggie,

It's weird that this line of code get this to work. You may want to refer
to the following articles for more information:

275089 Form or report that is bound to a stored procedure or a function
does not apply the WHERE condition
http://support.microsoft.com/default.aspx?scid=kb;EN-US;275089


235359 ACC2000: Implementing Query-by-Form in an Access Project
http://support.microsoft.com/default.aspx?scid=kb;EN-US;235359


Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi Maggie....
Let me preface this reply by saying that I can not point you to
any kb articles on this. This is simply what I have found to work.. and it
works consistently for me. That being said.

What I was suggesting was not a filter on the sub form
but rather a requery. When you reset the recordsourceyou in essence are telling it, forget the previous recset and use this.....

Then, you say... when you bring back the return resultset, I want it
returned using this parameter.
The dynamic filtering, if you want to call it that, is taking place by
changing the input params of the stored proc.

hth,
bob.
 
Back
Top