I have done this many times but, for some reason, the code just will not work for me in this instance. I am just trying to fill an unbound list box (lstSpec) on my form with the specialtyid in my table tblSpecialty_by_SME, linked to the SMEID in tblSME. I had it working, db corrupted so I lost it and can't seem to re-create. I know it's simple!
Row source for my combo box cboSME:
SELECT tblSME.SMEID, tblSME.SME, tblSME.[Date Added], tblSME.Location, tblSME.[email address], tblSME.[FY'12 Participation Goal], tblSME.[Facilitation Course Completed], tblSME.[Content Train the Trainer Completed], tblSME.[Pilot Course Taught], tblSpecialty_by_SME.specialtyid
FROM tblSpecialty INNER JOIN (tblSME INNER JOIN tblSpecialty_by_SME ON tblSME.SMEID = tblSpecialty_by_SME.smeid) ON tblSpecialty.SpecialtyID = tblSpecialty_by_SME.specialtyid;
After update event of cboSME:
Private Sub cboSME_AfterUpdate()
Dim strSource As String
Me.lstSpec.SetFocus
strSource = "SELECT smeid,specialtyid " & _
"FROM tblSpecialty_by_SME " & _
"WHERE smeid = " & Me.cboSME.Column(0) & _
"ORDER BY specialtyid"
Me.lstSpec.RowSource = strSource
Any ideas as to what I am doing wrong on this one?
Thanks!