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

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

Attached Images

frmModele.jpg (145.3 KB, 0 views)

I solve my problem by emptying the ServerFilter after the Requery. So when the form is close the ServerFilter propriety is save with an empty value. Yesterday I try that on the close event 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.

Thanks for posting your solution.

__________________
(RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 - acXP, ac07
If your issue is resolved...follow this link for directions on how to use the Solved thread tool!
Teaching is not filling a bucket but lighting a fire. Borrowed quote..."Docendo discimus"