The ServerFilter of my subform is save when the form close.

[TazQuebec]

I want the informations shown in a subform to change when the value of a Drop
Down Field. I use the property serverfilter of my subform.

It works if there was no value beforehand in the property.

Otherwise Access does not take in account the new criteria.

I use Access 2003 with SQL Server 2005. The form use a view

http://www.tazquebec.com/programmation/frmModele.jpg

Thanks to the advance of your help and here is my code:

Private Sub drpdwn_Section_Q_Change()
Select Case drpdwn_Section_Q.Column(2)
Case "S"
Me.frmMD_with_Desc_of_Activite.Form.Controls("Vale ur_Avant_Étiquette").
Caption = "NB Suite"
Me.frmMD_with_Desc_of_Activite.Form.Controls("Nb_H rs_Avant_Étiquette").
Caption = "Hrs Suite"
Me.frmMD_with_Desc_of_Activite.Form.Controls("Vale ur_Apres_Étiquette").
Caption = "NB Nouveau"
Me.frmMD_with_Desc_of_Activite.Form.Controls("Nb_H rs_Apres_Étiquette").
Caption = "Hrs Nouveau"
Case "A"
Me.frmMD_with_Desc_of_Activite.Form.Controls("Vale ur_Avant_Étiquette").
Caption = "NB Année 1"
Me.frmMD_with_Desc_of_Activite.Form.Controls("Nb_H rs_Avant_Étiquette").
Caption = "Hrs Année 1"
Me.frmMD_with_Desc_of_Activite.Form.Controls("Vale ur_Apres_Étiquette").
Caption = "NB Année 2"
Me.frmMD_with_Desc_of_Activite.Form.Controls("Nb_H rs_Apres_Étiquette").
Caption = "Hrs Année 2"
Case Else
Me.frmMD_with_Desc_of_Activite.Form.Controls("Vale ur_Avant_Étiquette").
Caption = "Hrs ?"
Me.frmMD_with_Desc_of_Activite.Form.Controls("Vale ur_Apres_Étiquette").
Caption = "Hrs ?"
End Select
Me.frmMD_with_Desc_of_Activite.Form.ServerFilter = "ref_Modele = " &
drpdwn_Modeles.Value & " AND ref_Phase = " & drpdwn_Phase.Value & " AND
ref_SA = " & drpdwn_Section_Q.Value
Me.frmMD_with_Desc_of_Activite.Form.Requery
Me.frmMD_with_Desc_of_Activite.Form.Repaint
End Sub

 

[TazQuebec via AccessMonster.com]

On my way to work I had a flash.

I solve my problem by emptying the ServerFilter after the Requery. So when
the form is close the ServerFilter propriety is save with a empty value.
Yesterday I try that on the close of the form but it did not work.

Me.frmMD_with_Desc_of_Activite.Form.ServerFilter = "ref_Modele = " &
drpdwn_Modeles.Value & " AND ref_Phase = " & drpdwn_Phase.Value & " AND
ref_SA = " & drpdwn_Section_Q.Value
Me.frmMD_with_Desc_of_Activite.Form.Requery
Me.frmMD_with_Desc_of_Activite.Form.ServerFilter = ""
Me.frmMD_with_Desc_of_Activite.Form.Repaint

Thanks you for your time all and I hope my solution may help someone else.

 

[Daryl S]

TazQuebec -

You need to turn the filter on, not just set the filter. Also, I suspect
you may be missing some delimeters in your filter. I have added single
quotes around the drpdwn_Phase.Value in your statement below, assuming that
was text, but you may need to change if it is numeric, and if any of the
other values are strings.

Me.frmMD_with_Desc_of_Activite.Form.ServerFilter = "ref_Modele = " &
drpdwn_Modeles.Value & " AND ref_Phase = '" & drpdwn_Phase.Value & "' AND
ref_SA = " & drpdwn_Section_Q.Value

Me.frmMD_with_Desc_of_Activite.Form.ServerFilterByForm = True

I assume you are compiling your code. If this doesn't fix your problem, let
us know what the problem is - any error messages? Have you stepped through
the code in debug mode to make sure it is executing?

 

[TazQuebec via AccessMonster.com]

I have tried that.

It did not work the Serverfilter is save on the close of the form.

The next time the form is use it ignore the new filter value and use the save
one.

Setting the ServerFilterByForm to true has no effect.

My solution was to set the Serverfilter to empty after the requery.

Thanks

TazQuebec -

You need to turn the filter on, not just set the filter. Also, I suspect
you may be missing some delimeters in your filter. I have added single
quotes around the drpdwn_Phase.Value in your statement below, assuming that
was text, but you may need to change if it is numeric, and if any of the
other values are strings.

Me.frmMD_with_Desc_of_Activite.Form.ServerFilter = "ref_Modele = " &
drpdwn_Modeles.Value & " AND ref_Phase = '" & drpdwn_Phase.Value & "' AND
ref_SA = " & drpdwn_Section_Q.Value

Me.frmMD_with_Desc_of_Activite.Form.ServerFilterByForm = True

I assume you are compiling your code. If this doesn't fix your problem, let
us know what the problem is - any error messages? Have you stepped through
the code in debug mode to make sure it is executing?

I want the informations shown in a subform to change when the value of a Drop
Down Field. I use the property serverfilter of my subform.

[quoted text clipped - 43 lines]

.