Datasheet combo box with one binding, different rowsets

[Chad1984]

Due to some quirky business rules, I need a combobox on a form to use different rowsets for different records. In my specific example, one record in a household has a relationship of "self." Any other record will have the choice of any possible relationship except "self." So I have code that looks like this:

Private Sub Rol_ID_GotFocus()
If Nz(Per_SSN.Value) = Nz(Form_Main.Hou_Per_SSN) Then
Rol_ID.RowSource = "SELECT Role.Rol_Description, Role.Rol_ID FROM Role WHERE Role.Rol_ID = 1;"
Else
Rol_ID.RowSource = "SELECT Role.Rol_Description, Role.Rol_ID FROM Role WHERE Role.Rol_ID > 1;"
End If
End Sub

This field is displayed in a subform in datasheet view on the "Main" form. However, the comboboxes DON'T always display their value. I may have two people, one with a role of "Self," and one with a role of "Daughter." When I load the form, the first person's rol_id will show "Self," and the second one will be blank. If I click on the daughter's Rol_ID and then click something, both values display- until I, say, switch windows, and then ONLY the daughter's role displays, not the "Self". The correct values are stored, and the drop-down is populated only with the correct choices, but they are not always displayed.

Now here's the REALLY quirky bit. It worked just fine in the last backup I made (yes, I pulled it up and checked). I changed the Main form, but made no alterations in this subform... yet, even if I look at it directly in the subform instead of through the Main form, it still misbehaves. Any ideas?

 

[Chad1984]

Never mind. I answered m e own question. Well... I didn't, really, but I got around the problem. I thought the problem MUST be in my code somewhere, so I opened the non-working portion of the database, opened the working backup, then copied over the new code for the main form. My sandbox copy still worked. Hmm. I copied over the new code for the subform. It still worked. Hmm. Then it stopped working for no reason that I could tell. After playing around with it, I scrapped it, then created another new copy from the working backup, and once again copied over my code (and made a few small changes to tables that had been made in the newest development version). So... even though it is, as far as I can tell, identical to the non-working version... it works.

If anyone has ever heard of anything like this and knows of any plausible reason why comboboxes should act in such a fashion- some silly little thing I didn't notice- then great. Otherwise, I'll chalk it off as a bizarre Access bug, and not my bug.