Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

using combo boxes with Filter by Form

Discussion in 'Information Technology' started by baconroll, Oct 29, 2005.

  1. baconroll

    baconroll
    Expand Collapse
    Guest

    On my form I have two combo boxes. The 1st one you select a product from then
    requerys the 2nd combo box with only items relating to that product which was
    selected in the 1st combo box - all works fine until...

    the form opens in filterby form. You can filter the 1st combo box but the
    2nd combo box does not contain any choices like it does when the form is in
    normal mode. i.e. it is not requering like it normally does

    any suggestions as always greatly appreciated...
     
  2. Loading...


  3. Ofer

    Ofer
    Expand Collapse
    Guest

    Have you change the form name or copy to a new one?
    If you did then did you change the path in the second combo to the first
    combo?
    Forms![NewFormName]![ComboName]
    =======================================
    Do you have a
    Me.Combo2Name.Requery
    On the after update of the first combo?
    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benifit from it.

    Good luck



    "baconroll" wrote:

    > On my form I have two combo boxes. The 1st one you select a product from then
    > requerys the 2nd combo box with only items relating to that product which was
    > selected in the 1st combo box - all works fine until...
    >
    > the form opens in filterby form. You can filter the 1st combo box but the
    > 2nd combo box does not contain any choices like it does when the form is in
    > normal mode. i.e. it is not requering like it normally does
    >
    > any suggestions as always greatly appreciated...
     
  4. baconroll

    baconroll
    Expand Collapse
    Guest

    thanks but not sure if this applys to me as all i am doing in opening up the
    same form but in filterbyform mode as follows:

    Private Sub Form_Open(Cancel As Integer)
    DoCmd.Maximize
    DoCmd.OpenForm "frmPatientInformation"
    On Error GoTo ErrHandler

    RunCommand acCmdFilterByForm
    RunCommand acCmdClearGrid

    Exit Sub

    ErrHandler:

    MsgBox "Error in Form_Open( ) in" & vbCrLf & _
    Me.Name & " form." & _
    vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description
    Err.Clear

    End Sub

    "Ofer" wrote:

    > Have you change the form name or copy to a new one?
    > If you did then did you change the path in the second combo to the first
    > combo?
    > Forms![NewFormName]![ComboName]
    > =======================================
    > Do you have a
    > Me.Combo2Name.Requery
    > On the after update of the first combo?
    > --
    > If I answered your question, please mark it as an answer. That way, it will
    > stay saved for a longer time, so other can benifit from it.
    >
    > Good luck
    >
    >
    >
    > "baconroll" wrote:
    >
    > > On my form I have two combo boxes. The 1st one you select a product from then
    > > requerys the 2nd combo box with only items relating to that product which was
    > > selected in the 1st combo box - all works fine until...
    > >
    > > the form opens in filterby form. You can filter the 1st combo box but the
    > > 2nd combo box does not contain any choices like it does when the form is in
    > > normal mode. i.e. it is not requering like it normally does
    > >
    > > any suggestions as always greatly appreciated...
     
  5. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    First, place the error handling command at the beginning of this procedure,
    because if you ever change the name of the frmPatientInformation form, it
    will fail to open and you'll have an unhandled error. If you ever add other
    code that has a possibility of failing, then that would cause an unhandled
    error, too. Your error handler routine is useless if it's not assigned prior
    to any error occurring during the execution of the code.

    Next, is the form you want to apply the filter-by-form feature to the
    frmPatientInformation form or the current form? The code I gave you earlier
    with the form OnOpen( ) event was intended to be placed on the form that
    actually needs the filter-by-form feature. In other words, the
    frmPatientInformation form is the form that should have the OnOpen( ) event
    code I gave you, if that's the actual form you want to filter.

    If this is the case, then in the current form, try:

    Private Sub Form_Open(Cancel As Integer)

    On Error GoTo ErrHandler

    DoCmd.Maximize
    DoCmd.OpenForm "frmPatientInformation"

    Exit Sub

    ErrHandler:

    MsgBox "Error in Form_Open( ) in" & vbCrLf & _
    Me.Name & " form." & _
    vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description
    Err.Clear

    End Sub

    And in the frmPatientInformation form, try:

    Private Sub Form_Open(Cancel As Integer)

    On Error GoTo ErrHandler

    RunCommand acCmdFilterByForm
    RunCommand acCmdClearGrid

    Exit Sub

    ErrHandler:

    MsgBox "Error in Form_Open( ) in" & vbCrLf & _
    Me.Name & " form." & _
    vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description
    Err.Clear

    End Sub

    However, if you want the frmPatientInformation form to be opened from the
    first form after the first form is maximized and the filter-by-form feature
    assigned to the frmPatientInformation form when it opens, then the following
    could be used, where SomeBtn is the name of the button on the first form:

    Private Sub SomeBtn_Click()

    On Error GoTo ErrHandler

    DoCmd.Maximize
    DoCmd.OpenForm "frmPatientInformation "
    RunCommand acCmdFilterByForm
    RunCommand acCmdClearGrid

    Exit Sub

    ErrHandler:

    MsgBox "Error in SomeBtn_Click( ) in" & vbCrLf & _
    Me.Name & " form." & _
    vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description
    Err.Clear

    End Sub

    And secondly, when the filter-by-form feature is invoked, no events fire, so
    the event code you expected to be executed after making the selection in the
    first combo box won't happen. All choices are available for these combo
    boxes during filter-by-form, no matter which combination is selected, even if
    the combination doesn't exist in the database.

    For example, if one had a combo box listing all of the States in the United
    States and another combo box listing cities, normal form operations would
    allow one to select the State of Illinois, and then the second combo box
    would be filtered on all cities in Illinois, so Chicago could be selected.
    However, during Filter-by-form, selection of the State of California in the
    first combo box will also allow the selection of Chicago in the second combo
    box, even though there is no city of Chicago in California. When the filter
    is invoked, there will be no records displayed, because no records match
    these filter criteria.

    Have you noticed that the button controls and other unbound controls are
    disabled during the filter-by-form? That's because they rely on events or
    user input, not data that can be retrieved by filtering on whichever items
    are selected in the bound controls.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.

    (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    be forwarded to me.)
    - - -
    If my answer has helped you, please sign in and answer yes to the question
    "Did this post answer your question?" at the bottom of the message, which
    adds your question and the answers to the database of answers. Remember that
    questions answered the quickest are often from those who have a history of
    rewarding the contributors who have taken the time to answer questions
    correctly.


    "baconroll" wrote:

    > thanks but not sure if this applys to me as all i am doing in opening up the
    > same form but in filterbyform mode as follows:
    >
    > Private Sub Form_Open(Cancel As Integer)
    > DoCmd.Maximize
    > DoCmd.OpenForm "frmPatientInformation"
    > On Error GoTo ErrHandler
    >
    > RunCommand acCmdFilterByForm
    > RunCommand acCmdClearGrid
    >
    > Exit Sub
    >
    > ErrHandler:
    >
    > MsgBox "Error in Form_Open( ) in" & vbCrLf & _
    > Me.Name & " form." & _
    > vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description
    > Err.Clear
    >
    > End Sub
    >
    > "Ofer" wrote:
    >
    > > Have you change the form name or copy to a new one?
    > > If you did then did you change the path in the second combo to the first
    > > combo?
    > > Forms![NewFormName]![ComboName]
    > > =======================================
    > > Do you have a
    > > Me.Combo2Name.Requery
    > > On the after update of the first combo?
    > > --
    > > If I answered your question, please mark it as an answer. That way, it will
    > > stay saved for a longer time, so other can benifit from it.
    > >
    > > Good luck
    > >
    > >
    > >
    > > "baconroll" wrote:
    > >
    > > > On my form I have two combo boxes. The 1st one you select a product from then
    > > > requerys the 2nd combo box with only items relating to that product which was
    > > > selected in the 1st combo box - all works fine until...
    > > >
    > > > the form opens in filterby form. You can filter the 1st combo box but the
    > > > 2nd combo box does not contain any choices like it does when the form is in
    > > > normal mode. i.e. it is not requering like it normally does
    > > >
    > > > any suggestions as always greatly appreciated...
     

Share This Page