Subform that has selectable addresses. Subform based on combo box.

[lamaine]

I am not a complete novice but am trying to understand these more
advanced concepts. I am stuck on this problem. Any help would be
greatly appreciated. I am trying to convert a Access 97 form to Access
2007. The form works fine in Access97. The user selects an Agency and
based on this selection it populates the BillingAddressSub form and
another combo box to select Shipping Address. There are 3 tables:
Agency, BillingAddress and ShippingAddress which are linked by
AgencyID. For each agency there may be 30 billing addresses and 60
shipping addresses. I have everything working except I am not able to
select the billing address that I need from the subform. I have
created a cascading combo box and it populates fine but does not show
the Billing Addresses unless I click the dropdown arrow. Here is what
I have now. Thanks in advance for any input.


Private Sub ComboAgencyID_AfterUpdate()
If Me!WorkshopID = 0 Then
MsgBox "You must select Workshop before selecting Agency"
DoCmd.GoToControl "WorkshopID"
Else
Me!ShipName = Me!ComboAgencyID.Column(3)
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Forms![ServiceContract]![BillingAddressSub].Requery
Me!ShipName.Requery
Me!BillingAddressID.Requery

End If
End Sub

Private Sub ComboAgencyID_DblClick(Cancel As Integer)
DoCmd.OpenForm "Agency", , , , , acWindowNormal, "GotoNew"
Me!ComboAgencyID.Requery
End Sub

Private Sub ComboAgencyID_Exit(Cancel As Integer)
If IsNull(ComboAgencyID) Or IsEmpty(ComboAgencyID) Or ComboAgencyID
= 0 Then
MsgBox "Please Select Agency"
DoCmd.GoToControl "ContractDate"
ComboAgencyID.SetFocus
End If
End Sub