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

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

Open in new window

JOHN_STIBBARDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
>> Me.lstEntities.Column(1)

Verify that you indeed should be using Column 1

Note that column indexes are zero-based (ie: the count starts at zero, not 1) and generally ID fields are in column zero.
0
JOHN_STIBBARDAuthor Commented:
Thanks for the reply mbizup,

Yes, it is definitely Column(1) as txtABNID updates correctly.

John
0
mbizupCommented:
Try changing this:

>> Me.txtABNID.Requery

to:

Me.txtABNID.Refresh
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mbizupCommented:
Also, is ABN/ABNID text or numeric?
0
JOHN_STIBBARDAuthor Commented:
Tried your suggestion, mbizup, but I get a "Compile error: Method or data member not found"
J
0
JOHN_STIBBARDAuthor Commented:
ABN is text and txtABNID is unbound.
J
0
mbizupCommented:
If ABN is Text, then you need to revise Line 9 like this (text fields need to be delimited with quotes):

        & "WHERE ([Vendor Details].ABN) = " & CHR(34) & Me.lstEntities.Column(1) & CHR(34) & ") " _



The CHR(34)'s are double-quotes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JOHN_STIBBARDAuthor Commented:
Tried your suggestion, but lstPrincipalHospitals still goes blank.
J
0
mbizupCommented:
Are you able to upload a copy of your database (just relevant forms and tables, with any sensitive data masked or removed)?
0
JOHN_STIBBARDAuthor Commented:
Sorry, employer not happy to upload a copy database.
J
0
JOHN_STIBBARDAuthor Commented:
Can't post a copy of the database unfortunately, but any further suggestions please?
J
0
JOHN_STIBBARDAuthor Commented:
mbizup, points to you for pointing me in the right direction with the double-quotes.  To get it to work I had to enclose it in single-quotes as well.

WHERE ([Vendor Details].ABN) = ' " & Me.lstEntities.Column(1) & " ' "

cheers

J
0
JOHN_STIBBARDAuthor Commented:
Please see my final post at the end
Cheers
J
0
mbizupCommented:
>> To get it to work I had to enclose it in single-quotes as well.

Glad you've got this resolved.  

I'm not sure if you noticed it or not but you actually made another correction in the code you posted that I didn't spot last night.  There was an exraneous closing parenthesis ( ")") which you removed in the code you posted.  I'm pretty sure that is what was causing you grief.

The single quote syntax that you used:

>> WHERE ([Vendor Details].ABN) = ' " & Me.lstEntities.Column(1) & " ' "

And the double quote syntax I used (which should work like this, with the extraneous parenthesis removed):

>>         & "WHERE ([Vendor Details].ABN) = " & CHR(34) & Me.lstEntities.Column(1) & CHR(34)

Are more or less equivalents of each other.

Just a word of caution with the single-quote syntax - it should be fine for your ABN ID field, but will cause problems with data such as Names where values containing single quotes (eg: Irish names like O'Brien) will produce errors.
0
JOHN_STIBBARDAuthor Commented:
Thanks mbizup, will keep the name comment in mind.
Cheers
John
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.