Filter Subform By Multi-Choice Lookup Value in Record Source and Focus on Subform

[benthamq]

Hello,




I have a form (frmOrders) for entering customer orders which includes a tab control with four tabs. Three of the tabs are for entering addresses -a billing address, shipping address, and a third-party freight billing address if applicable. I have placed the same subform on all three tabs (sbfrmCustomerAddresses). The main form record source is an orders table (tblOrders) and the subform record source is a query with a snapshot
recordset type just to show the available addresses and allow the user to select one on each tab:

SELECT tblCustomerAddresses.*
FROM tblCustomerAddresses;

The main form includes a combobox for selecting a customer (CustomerID) and by linking that master field with a child field on the query of the subform (also CustomerID) I am getting the addresses to correctly filter by customer in the subform based on the customer selection made in the main form.

However, that subform record source also includes a multi-choice look-up field called AddressPurpose with the following values: "Bill-to", "Ship-to", "Third-party Factor", "Third-party freight bill-to". My goal is to further filter the records in the subform according to which options are checked in the multi-choice field, and which tab is selected. So, in other words, if the user selects customer A on the main form then because the subform is linked to the main form master field customer ID it will also only show addresses for customer A, which is good. But then I need the subform records to be further filtered based on the tab that is selected - if the tab for billing address is selected, then only addresses for Customer A that have "Bill-to" and/or "Third-party Factor" should appear in the subform, likewise if the tab for shipping address is selected then only addresses for Customer A that have "Ship-to" selected should appear, etc.

Another problem that I have with this same subform is that whenever I click the subform, data validation rules for the main form are getting triggered - it behaves as though I am trying to save a record or close the form without filling out required fields, even though all I am doing is clicking the subform.

I have been struggling to get both of these problems solved. I am very grateful for any help anyone can offer and thanks very much in advance!

[June7]

What you want is probably not easy. I refuse to use multi-value fields because of the issues they cause, as you are experiencing. Must understand that a multi-value field is not just a field in table. A multi-value field actually involves a hidden child table that stores a record for each of the values displayed in the primary record. Querying multi-values requires expanding the child table. Review this http://office.microsoft.com/en-us/ac...33722.aspx#BM7

[benthamq]

Thank you for your reply. I trust your judgement as far as multi-value fields being bad, so as a potential solution I got rid of the multi-value field and replaced it with four yes/no fields: one for bill-to, one for ship-to, one for third-party factor, and one for third-party freight bill-to.

I am trying to use these fields and the customer ID field in the table to dynamically set the record source of the subform based on two critera: firstly, the customer selected in the main form and secondly, the tab that is selected on the tab control that contains the subform, since there is one tab for entering the shipping address, billing address, and third-party freight-billing address.

I am trying the code below but it doesn't work correctly - currently, it is correctly setting the record source on the first (shipping tab) instance of the subform but it doesn't reset the record source for the subform when I switch tabs. In fact, it just shows no record source for the subform on the other tabs.

I also still have the problem of data validation rules on the main form being triggered when I click the tab control. It seems like the form is trying to save the record in the record source of the main form just when I click the tab, and therefore I can't access the tab or do anything with it unless I fill in all the data on the main form first.

Option Compare Database
Option Explicit


Private Sub Form_Load()
    Me.sbfrmCustomerAddresses.Tag = Me.tbCtlfrmOrders.Value
End Sub


Private Sub CustomerID_AfterUpdate()

Select Case Me.CustomerID
    Case Is <> ""
        With Me.txtCustID
            .Value = Me.CustomerID.Value
            .Visible = True
        End With
            Me.Label21.Visible = True
Case Else
        With Me.txtCustID
            .Value = 0
            .Visible = False
        End With
            Me.Label21.Visible = False
End Select

Call SetCustAddressRS

End Sub


Private Sub sbfrmCustomerAddresses_Enter()

End Sub

Private Sub ShippingPayment_AfterUpdate()

Select Case Me.ShippingPayment
    Case "Third-party"
        Me.pgeThirdPartyFreight.Visible = True
    Case Else
        Me.pgeThirdPartyFreight.Visible = False
End Select

End Sub

Private Sub tbCtlfrmOrders_Change()

'tblCtlfrmOrders tab index 0 = Billing, 1 = Shipping, 2 = Third-party Freight, 3 = Order Detail
Me.sbfrmCustomerAddresses.Tag = Me.tbCtlfrmOrders.Value

If Me.CustomerID <> "" Then Call SetCustAddressRS

End Sub

Sub SetCustAddressRS()

Dim custID As Long
custID = Me.CustomerID

With Me.sbfrmCustomerAddresses.Form

    Select Case Me.tbCtlfrmOrders.Value
    
        Case 0
            .RecordSource = "SELECT tblCustomerAddresses.* " & _
                            "FROM tblCustomerAddresses " & _
                            "WHERE (((tblCustomerAddresses.CustomerID)=" & custID & ") " & _
                            "AND ((tblCustomerAddresses.BillTo)=Yes)) OR (((tblCustomerAddresses.ThirdPartyFactor)=Yes));"
        Case 1
            .RecordSource = "SELECT tblCustomerAddresses.* " & _
                            "FROM tblCustomerAddresses " & _
                            "WHERE (((tblCustomerAddresses.CustomerID)=" & custID & ") " & _
                            "AND ((tblCustomerAddresses.ShipTo)=Yes));"
        Case 2
            .RecordSource = "SELECT tblCustomerAddresses.* " & _
                            "FROM tblCustomerAddresses " & _
                            "WHERE (((tblCustomerAddresses.CustomerID)=" & custID & ") " & _
                            "AND ((tblCustomerAddresses.ThirdPartyFreightBillTo)=Yes));"
    End Select
 
.Requery

End With
 

End Sub

Again, thanks for any help offered. I really appreciate it because I am not an Access Professional and using Access for me is a real struggle.

[June7]

Just hit me what you are trying to do. The With qualifier won't work because the code must reference the subform container control. Name the container controls different from the object they hold, like ctrBillTo

With Me
    Select Case Me.tbCtlfrmOrders.Value
    
        Case 0
            .ctrBillTo.Form.RecordSource = "SELECT tblCustomerAddresses.* " & _
                            "FROM tblCustomerAddresses " & _
                            "WHERE (((tblCustomerAddresses.CustomerID)=" & custID & ") " & _
                            "AND ((tblCustomerAddresses.BillTo)=Yes)) OR (((tblCustomerAddresses.ThirdPartyFactor)=Yes));"
        Case 1
            .ctrShipTo.Form.RecordSource = "SELECT tblCustomerAddresses.* " & _
                            "FROM tblCustomerAddresses " & _
                            "WHERE (((tblCustomerAddresses.CustomerID)=" & custID & ") " & _
                            "AND ((tblCustomerAddresses.ShipTo)=Yes));"
        Case 2
            .ctrFreightBillTo.Form.RecordSource = "SELECT tblCustomerAddresses.* " & _
                            "FROM tblCustomerAddresses " & _
                            "WHERE (((tblCustomerAddresses.CustomerID)=" & custID & ") " & _
                            "AND ((tblCustomerAddresses.ThirdPartyFreightBillTo)=Yes));"
    End Select
End With

[benthamq]

Thank you! I did what you suggested and the subform containers are now correctly relating to the records in the main form and it is filtering correctly.

The only problem I still have now is that when I click the navigation buttons on the subform to scroll through the records, the data validation rules I have on the main parent form ("IS NOT NULL" for several required fields) are being executed. I only need this data validation executed when I close the whole form or try to create a new record on the main form - apparently, clicking the subform changes the focus and tries to save the record on the main form. This makes it impossible to use is the subform before all the fields in the main form are completed, which I don't necessarily want. Is there any way to get around this?

Sub SetCustAddressRS()

Dim custID As Long
custID = Me.CustomerID

With Me

    Select Case .tbCtlfrmOrders.Value
    
        Case 0
            .cntBillTo.Form.RecordSource = "SELECT tblCustomerAddresses.* " & _
                            "FROM tblCustomerAddresses " & _
                            "WHERE (((tblCustomerAddresses.CustomerID)=" & custID & ") " & _
                            "AND ((tblCustomerAddresses.BillTo)=Yes)) OR (((tblCustomerAddresses.ThirdPartyFactor)=Yes));"
        
            .BillTo = .cntBillTo.Form.CustomerAddressID
            
        Case 1
            .cntShipTo.Form.RecordSource = "SELECT tblCustomerAddresses.* " & _
                            "FROM tblCustomerAddresses " & _
                            "WHERE (((tblCustomerAddresses.CustomerID)=" & custID & ") " & _
                            "AND ((tblCustomerAddresses.ShipTo)=Yes));"
                            
            .ShipTo = .cntBillTo.Form.CustomerAddressID
            
        Case 2
            .cnt3rdPtyFr.Form.RecordSource = "SELECT tblCustomerAddresses.* " & _
                            "FROM tblCustomerAddresses " & _
                            "WHERE (((tblCustomerAddresses.CustomerID)=" & custID & ") " & _
                            "AND ((tblCustomerAddresses.ThirdPartyFreightBillTo)=Yes));"
                        
            .ThirdPartyShippingBillTo = .cntBillTo.Form.CustomerAddressID
    End Select


End With
 

End Sub

[June7]

Don't use the ValidationRule property. Control validation with VBA code.

[benthamq]

Will do. Thank you again.