Problem with bound comboboxes and unbound textboxes

[forestpest]

Guys

I use dlookup to update an unbound textbox. But now I got something more challenging for you guys. Start by selecting the form called Patient then go to the tab called clinical data. Then hit edit go down add add a Coded Diagnosis using the button on the form. You see a subform open given you some diagnosis' do a search by epc code and add the diagnosis with epc code 03.07.01 by double clicking. This diagnosis has no codes so the combo turns up empty which is fine. Then add another diagnosis with epc 09.15.13 and check the corresponding combo it should give some values in the combobox that should be the diagnosisname in the diagnosticmasterlist table where the sectionid = 960. do the same for another diagnosis with epc code 06.02.91. the combo for that diagnosis should be values in the combobox that should be the diagnosisname in the diagnosticmasterlist table where the sectionid = 963 which are deferent then those in 960 same goes for any other diagnosis. When doubleclicking the code that sets the combobox controlsourse is in the doubleclick event of the
ListOfMasterDiagnosisList

and is

Dim length As Integer

'get jump_id
jump_id = Forms![Patient].[ListOfDiagnosisMasterList].Form![go to] 'looks like goto 5
'MsgBox (jump_id)
length = Len(jump_id)
'MsgBox (length)
'fixed jump_id
jump_id = Mid(jump_id, 4, (length - 3)) 'get just the last character
'MsgBox (jump_id)

'before jumping check is our destination is a qulifier
If jump_id >= "950" And jump_id <= "999" Then
FoundSectionID = jump_id
'MsgBox (FoundSectionID)
'Form_DblClick (0)
ElseIf jump_id >= "1600" And jump_id <= "1799" Then
FoundSectionID = jump_id
'MsgBox (FoundSectionID)
'Form_DblClick (0)
End If

'end fix for qualifiers
If FoundSectionID >= "950" And FoundSectionID <= "999" Then
'Form_ListOfPatientCodedDiagnosis.Combo22.RowSource = "SELECT DiagnosticMasterList.DiagnosisID, DiagnosticMasterList.DiagnosisName, DiagnosticMasterList.SectionID FROM DiagnosticMasterList where SectionID =" + FoundSectionID + " ORDER BY DiagnosticMasterList.DiagnosisID;"
Form_ListOfPatientCodedDiagnosis.Combo22.RowSource = "SELECT [DiagnosticMasterList].[DiagnosisID], [DiagnosticMasterList].[DiagnosisName], [DiagnosticMasterList].[OtherCode] FROM DiagnosticMasterList where SectionID =" + FoundSectionID + " ORDER BY DiagnosticMasterList.DiagnosisID;"
ElseIf FoundSectionID >= "1600" And FoundSectionID <= "1799" Then
'Form_ListOfPatientCodedDiagnosis.Combo22.RowSource = "SELECT DiagnosticMasterList.DiagnosisID, DiagnosticMasterList.DiagnosisName, DiagnosticMasterList.SectionID FROM DiagnosticMasterList where SectionID =" + FoundSectionID + " ORDER BY DiagnosticMasterList.DiagnosisID;"
Form_ListOfPatientCodedDiagnosis.Combo22.RowSource = "SELECT [DiagnosticMasterList].[DiagnosisID], [DiagnosticMasterList].[DiagnosisName], [DiagnosticMasterList].[OtherCode] FROM DiagnosticMasterList where SectionID =" + FoundSectionID + " ORDER BY DiagnosticMasterList.DiagnosisID;"

Else
FoundSectionID = "0"
'Form_ListOfPatientCodedDiagnosis.Combo22.RowSource = "SELECT DiagnosticMasterList.DiagnosisID, DiagnosticMasterList.DiagnosisName, DiagnosticMasterList.SectionID FROM DiagnosticMasterList where SectionID =" + FoundSectionID + " ORDER BY DiagnosticMasterList.DiagnosisID;"
Form_ListOfPatientCodedDiagnosis.Combo22.RowSource = "SELECT [DiagnosticMasterList].[DiagnosisID], [DiagnosticMasterList].[DiagnosisName], [DiagnosticMasterList].[OtherCode] FROM DiagnosticMasterList where SectionID =" + FoundSectionID + " ORDER BY DiagnosticMasterList.DiagnosisID;"

'Form_ListOfPatientCodedDiagnosis.Combo22.Requery
End If
This code works seperately so it's ok I added the exact coded to the after_update,before_update and change events of the combobox on the patientcodeddiagnosis form but didn't get the desired effect

Help would be cool

my database is here for download:

www dot cycoders dot com/jossif/finally_fixed2.mdb


.
~Oorang