Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2015
    Posts
    2

    Unanswered: Combo Box After Update Filter Not Updating Subform

    After selection from unbound Combo Box on Main Form, the subform is not updating or changing records. I get no VBA error message. It just says calculating at bottom of window and nothing changes.

    Combo Box on Main Form: Combo129
    Combo129 sql
    SELECT tblShipTo.ShipToID, tblShipTo.CustomerID, tblShipTo.ShipToCompany, tblShipTo.ShipToCity, tblShipTo.ShipToState
    FROM tblCustomers INNER JOIN (tblShipTo INNER JOIN tblOrders ON (tblShipTo.ShipToID = tblOrders.ShipToID) AND (tblShipTo.ShipToID = tblOrders.ShipToID)) ON (tblCustomers.CustomerID = tblShipTo.CustomerID) AND (tblCustomers.CustomerID = tblOrders.CustomerID)
    WHERE (((tblShipTo.CustomerID)=[Forms]![frmOrders]![Combo27]));
    Bound Column is ShipToID

    Combo129 After Update Code:


    Private Sub Combo129_AfterUpdate()
    Dim strFilter As String
    If IsNull(Me.Combo129) Then
    Exit Sub
    End If
    strFilter = "[ShipToID]= """ & Me.Combo129 & """"
    Me.sfrShiptoOrders.Form.FilterOn = True
    Me.sfrShiptoOrders.Form.Filter = strFilte
    End Sub
    Subform Name: sfrShiptoOrders


    Any help would be appreciated.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Put a breakpoint on the code, then run the code. Step through it to see what is actually happening. If you dont know how to use the debugger, then it sounds like now us a great time to start to learn

    I take it you are declaring 'option explicit' in each module (or have made a project wide setting) to force explicit declaration of variables before first use. That usually clears up a lot if these sort of problems.

    Also check the sequence of how you set a form filter. Usually its:-
    Build the filter string
    Assign that that to the form / reports filter
    Turn on the filter....

    Make certain your filter string is valid and also says what you think it should. A classic problem with combo boxes is that you dont pick up the index column but the text description.

    ..but all of that will come clear when you get to grips with the debugger.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Check the datatyoes also. I exoect columns ending in ID to be numeric yet you are delimiting the value of the combobox as if its a string / text value NOT numeric
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2015
    Posts
    2
    Quote Originally Posted by healdem View Post
    Check the datatyoes also. I exoect columns ending in ID to be numeric yet you are delimiting the value of the combobox as if its a string / text value NOT numeric
    Yes. Thank you. I had thought I had considered that datatype issue but obviously not. I have corrected (I believe) the code to reflect a numeric data type:

    Code:
    Private Sub Combo129_AfterUpdate()
    Dim strFilter As String
    If IsNull(Me.Combo129) Then
    Exit Sub
    End If
    strFilter = "[ShipToID]= " & Me.Combo129
    Me.sfrShiptoOrders.Form.FilterOn = True
    Me.sfrShiptoOrders.Form.Filter = strFilter
    End Sub
    What's happening now is: The combo129 is filtering the choices by customer ID (criteria in the combo box query). Limiting my choices to the Customer ID that is used in the main form "Orders". If I am in a previously saved record and I select another choice from the Combo Box, the subform (sfrShiptoOrders) filters to the correct record.

    However, if I go to add a new order, assign that order to a customer with an existing ShipToID and the go to combo129, the correct choice is there but after selecting it, no record goes into the subform (sfrShipToOrders). I'm not sure what's going on here. Is filtering the wrong choice, by chance??

    Thanks again

Posting Permissions

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