Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    3

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

    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

    Print Screen

    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
    Last edited by TazQuebec; 01-28-10 at 12:52. Reason: Add info

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I am not familiar with the .ServerFilter (never found the need to ever, ever use it and I use SQL Server all the time as a backend) but if it's similar to filtering records, I try to avoid doing this. I had way to many upsizing issues and other issues with bad data entry using a filter type command (I always found it problematic.)

    If you need to populate a "search" type field and then requery the recordset based upon that search value (ie the search field is a combo box), I would use criteria in a LIKE type statement in the query or form's recordset which would look like this: Like Forms!MyFormName!MySearchField & *

    Then issue a me.requery command (or to another form.)

    Or if needed, change the recordsource.

    I just avoid any filter type commands at all costs.
    Last edited by pkstormy; 01-28-10 at 23:53.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2010
    Posts
    3

    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 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.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. Thanks for posting your solution!
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •