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
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.RequeryASKER
Thanks for the reply mbizup,
Yes, it is definitely Column(1) as txtABNID updates correctly.
John
Yes, it is definitely Column(1) as txtABNID updates correctly.
John
Try changing this:
>> Me.txtABNID.Requery
to:
Me.txtABNID.Refresh
>> Me.txtABNID.Requery
to:
Me.txtABNID.Refresh
Also, is ABN/ABNID text or numeric?
ASKER
Tried your suggestion, mbizup, but I get a "Compile error: Method or data member not found"
J
J
ASKER
ABN is text and txtABNID is unbound.
J
J
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tried your suggestion, but lstPrincipalHospitals still goes blank.
J
J
Are you able to upload a copy of your database (just relevant forms and tables, with any sensitive data masked or removed)?
ASKER
Sorry, employer not happy to upload a copy database.
J
J
ASKER
Can't post a copy of the database unfortunately, but any further suggestions please?
J
J
ASKER
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
WHERE ([Vendor Details].ABN) = ' " & Me.lstEntities.Column(1) & " ' "
cheers
J
ASKER
Please see my final post at the end
Cheers
J
Cheers
J
>> 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.
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.
ASKER
Thanks mbizup, will keep the name comment in mind.
Cheers
John
Cheers
John
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.