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