Issues with combobox record selector and filters (1 Viewer)

ilikecats

New member
Local time
Today, 00:33
Joined
Jan 1, 2020
Messages
11
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

Code:
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.
 

Attachments

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.
 

Attachments

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.
 
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.
 
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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom