[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-02
15
Medium Priority
?
194 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:JOHN_STIBBARD
  • 9
  • 6
15 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36901020
>> 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
 

Author Comment

by:JOHN_STIBBARD
ID: 36901045
Thanks for the reply mbizup,

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

John
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36901055
Try changing this:

>> Me.txtABNID.Requery

to:

Me.txtABNID.Refresh
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 61

Expert Comment

by:mbizup
ID: 36901062
Also, is ABN/ABNID text or numeric?
0
 

Author Comment

by:JOHN_STIBBARD
ID: 36901065
Tried your suggestion, mbizup, but I get a "Compile error: Method or data member not found"
J
0
 

Author Comment

by:JOHN_STIBBARD
ID: 36901076
ABN is text and txtABNID is unbound.
J
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36901095
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
 

Author Comment

by:JOHN_STIBBARD
ID: 36901121
Tried your suggestion, but lstPrincipalHospitals still goes blank.
J
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36901147
Are you able to upload a copy of your database (just relevant forms and tables, with any sensitive data masked or removed)?
0
 

Author Comment

by:JOHN_STIBBARD
ID: 36901174
Sorry, employer not happy to upload a copy database.
J
0
 

Author Comment

by:JOHN_STIBBARD
ID: 36901384
Can't post a copy of the database unfortunately, but any further suggestions please?
J
0
 

Author Comment

by:JOHN_STIBBARD
ID: 36901447
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
 

Author Closing Comment

by:JOHN_STIBBARD
ID: 36901451
Please see my final post at the end
Cheers
J
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36902659
>> 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
 

Author Comment

by:JOHN_STIBBARD
ID: 36905898
Thanks mbizup, will keep the name comment in mind.
Cheers
John
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question