Use combo or list box to populate another list box

[wpryan]

I am working on a CRM database for my company. We manufacture devices that are installed in clinics around the US. It is common that a doctor will use different machines in different locations. I want to be able to track this. To this end I made a table and associated form Doctors, and in this form I want to be able to have a list box to show which clinics (machines) the doctor is associated with. This data is coming from the Customer table, and I'll call it List Box A. I want the user to be able to select the clinic from a drop down or list box (List Box A), and have that selected item transfer to the list box in the Doctors table (List Box B). I have some experience with Excel VBA but it doesn't seem to be helpful with Access. I tried this code:

Private Sub cboAffiliatedClinic_AfterUpdate()
lboAffiliatedClinic.Value = cboAffiliatedClinic.Value
End Sub

where cboAffiliatedClinic is coming from the Customer table, and lboAffiliatedClinic is in the Doctors table, but it's not working. I also tried it in different events, with no luck. Any suggestions are greatly appreciated.

 

[boblarson]

If you want a doctor to be associated with multiple clinics then you would need to have a junction table which stores that association

DoctorID - Long Integer (FK)
ClinicID - Long Integer (FK)

and then you could use a listbox with the multi-select set to Simple or Extended but you would need code to add those to the table for that doctor. You would also need to include it in the form's On Current event.

I have to go to a meeting but here's a page which basically shows how you add a record from a multiple select listbox and then you might be able to see how you can read it back to the list box. If not, I'll be back later:

http://www.baldyweb.com/MultiselectAppend.htm