troubleshooting Question

Populate a text box from a list and update a second list from the result

Avatar of JOHN_STIBBARD
JOHN_STIBBARDFlag for Australia asked on
Microsoft Access
15 Comments1 Solution218 ViewsLast Modified:
Hello Experts,

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?

Cheers

John
Me.txtABNID = Me.lstEntities.Column(1)
  
Me.txtABNID.Requery
    
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"
    
Me.lstPrincipalHospitals.Requery
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros