Issues with combobox record selector and filters

[ilikecats]

In my database (attached), I have a form ('Households & Sims') with a combobox record selector to select a household. It works well until I try to add a criteria to the query.

If I add [Households].HouseholdExists=True to the form's Filter or 'WHERE (((Households.HouseholdExists)=True))' to the form's query, the associated records are visible in HouseholdPicker but nothing happens when I select them.

However, if I also add 'WHERE (((Households.HouseholdExists)=True))' as a criteria the HouseholdPicker query, this results in a glitchy behaviour similar where the combobox goes blank after selecting a record. I tried putting

Private Sub Form_Current()
Me.HouseholdPicker = Me.Recordset![Households.HouseholdName]
End Sub

into the Form's On Current but that didn't help.

Any suggestions on what to do? Thanks in advance.

 

[arnelgp]

you add HID field on your form (i added it, not Visible, and Named the textbox tHID).
then on the Current event you assign this to your combobox.

 

[The_Doc_Man]

Just as a side comment, putting special characters in any Access object name (such as 'Households & Sims') is a bad idea, long term. The special character will doom you to having to use brackets forever, whereas if you find a variant of that name that doesn't use specials or spaces, you can use that variant name without bracketing. That's just a typing suggestion, doesn't affect program flow.

 

[GPGeorge]

Just as a side comment, putting special characters in any Access object name (such as 'Households & Sims') is a bad idea, long term. The special character will doom you to having to use brackets forever, whereas if you find a variant of that name that doesn't use specials or spaces, you can use that variant name without bracketing. That's just a typing suggestion, doesn't affect program flow.

It's surprising, perhaps, how much difference a clean naming convention, as The_Doc_Man suggests, can make in maintaining code and SQL. Access is capable of reading the fussier strings easily, but as humans, it's a very different task for us.

 

[ilikecats]

Thanks everyone

I think I will try to reduce the ampersands for the objects - I think I can rename it for the categories without affecting the underlying object.

 

[pdanes]

I've had to deal with this in using foreign characters, and it has led to some weird and frustrating behavior. I learned long ago to stick with VERY strict naming - the 26 Latin letters and numerals. NOTHING else, ever.