I have been working on this for two days and haven't been able to get a result.
We have a form with two list boxes (lstEntities and lstPrincipalHospitals and a text box (txtABNID). When the form is current, lstEntities and lstPrincipalHospitals are populated by select queries from tables REFProviderRegister and VendorDetails which have a 1 to many relationship on ProviderID.
What is required is that with the OnClick event in lstEntities, txtABNID is updated from lstEntities.Column(1) and lstPrincipalHospitals should update with the details of the hospital names where ABN = txtABNID.
Everything works when the form is current, however when the lstEntities is clicked, lstPrincipalHospitals goes blank (no data at all).
Could you please peruse the code and suggest how this might be remedied?
Me.txtABNID = Me.lstEntities.Column(1)
Me.lstPrincipalHospitals.RowSource = "SELECT [REF Provider Register].Provider_surname, [REF Provider Register].ProviderID, " _
& "[REF Provider Register].Principal, [REF Provider Register].PrincipalID, [REF Provider Register].Facility, " _
& "[Vendor Details].VendorID, [Vendor Details].ABN FROM [REF Provider Register] LEFT JOIN [Vendor Details] " _
& "ON [REF Provider Register].ProviderID = [Vendor Details].ProviderID " _
& "WHERE ([Vendor Details].ABN) = " & Me.lstEntities.Column(1) & ") " _
& "ORDER BY [REF Provider Register].Provider_surname"