Bound Combo box is Empty on selection of Null value on Exit Sub

[nstalkol]

Im a rookie.

I have am unbound combo box populated with values (rowsource) from a query which has 2 tables in it. Each table has its own ID, but both are included in the combo box (info3). Sometimes the combobox has ID values from Table A, but the corresponding ID value in table B is NULL. The bound column is the ID from B, because I use it to populate text boxes, by going to that record using:

strCriteria = "[ID] =" & Me.info3
Me.RecordsetClone.FindFirst strCriteria
Me.Bookmark = Me.RecordsetClone.Bookmark

I have a statement which checks if the info3 (the bound column is from Table B), is NULL.

If it is, it "does some things" and at the end, i have Exit Sub.

THE PROBLEM: When I select an item from the combo, with value for ID from Table A, but NULL (bound column) from Table B, it exits the Sub, and clears the selection from the combobox, as if its not selected.

If i step through the code, it "does some things", like updating text boxes, but when it gets to Exit Sub, it all disappears, blank values in the text boxes (does some stuff), and shows the combo box blank.

Why does this happen when the Exit Sub is executed ?

Another clue: When i do Debug.print of the text boxes being updated (stuff), it actually updates them, and does Not empty them upon Exit Sub. However, the combobox still goes blank.

PLEASE HELP. I give props to whoever can solve this. I been trying for 3 weeks, about 20 different ways.

Many many many thanks to the guru who understands this.

[Delerna]

I can't make head or tail of that description.
You start out with

I have am unbound combo box .......
Sometimes the combobox has ID values from Table A, but the corresponding ID value in table B is NULL

and when you get to the problem you say

but NULL (bound column) from Table B

What ???? you lost me.

The one thing I did notice

Why does this happen when the Exit Sub is executed ?

I suspect you might have some code somewhere else that is getting executed after the Exit sub ?
Try breakpointing the exit sub and when the code stops there
breakpoint every line of code so you can follow the flow after that point

[MMcCarthy]

If I understand you correctly, you are using the value in column 2 of the combobox as the bound column but this column can sometimes have Null value. What are you trying to bind that value to. Have you set a control source for the combobox. Prehaps a field that won't allow a null value.

[nstalkol]

Let me demonstrate a small example of my issue in the table below.

ID-A ID-B Name
1 23 First
2 26 Second
3 Null Third

The control source for the form is Table B, which has ID-B, since I want the Row source to move there with corresponding other text fields. I also have unbound text fields which use the corresponding ID-A field to fill corresponding information from Table A. The bound column (ID-B) may be Null as a result (as in the 3rd row in example above). I tried stepping through the code. All is good until the Exit Sub is executed.

[MMcCarthy]

Without knowing the structure of these two tables and how you are coming up with the data set for the drop down box it's difficult to advise you. I suspect you are trying to do something with the unbound textboxes that you really can't do. Can you post the metadata of these two tables.

Here is an example of how to post table MetaData :
Table Name=tblBookings

 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Also tell us the row source of the dropdown box. Is it a query? If so please post the sql.