Form/ListBox Madness! (1 Viewer)

I’m an Access New Guy, so please be patient!

I’m having a hard time figuring out a problem with a form I’m working on. First, the structure of my database. I’m using simpler example for clarity:

EmployeeTbl, with the following data columns: Name (listing many varied names), Division (a limited list of Division names), Class (a limited list of Employee classifications)
EmpQry, a select query to list all of the employees, with possible filters (see below for details).
EmpFrm, a Form for displaying the employee using a multi-selectable ListBox.

On the form, I added an unbounded ComboBox to select an item to be used as a filter to update the records displayed in the ListBox, limited to items on the Division column. The ComboBox is used to select one of the following:

“*” (for all records; also the DEFAULT for the ComboBox)
“A” (records with Division set to “A”)
“B” (records with Division set to “B”)
“C” (records with Division set to “C”)

Changing the ComboBox doesn’t affect the list, by design, unless the end user clicks another button below titled “Filter List”.

In EmpQry, I wrote a criteria for the DIVISION entry that reads as follows:

Like [Forms]![EmpFrm]![ComboBox]

THE PROBLEM: When I have the Query and the Form open, I can switch back and forth from Design to Form or Datasheet view. The Form works perfectly; changing my selection in the ComboBox and clicking my Filter List button causes the ListBox on the Form to update appropriately. For example, using the ComboBox to select Division A causes the ListBox to update only showing Division A employees, etc.

HOWEVER: If I save and close the Query and the Form… and later reopen them…. Both of them show NO RECORDS.

I’m stymied as to why the Query and Form work fine until closed and reopened? It seems to have something to do with the Query Criteria (If I remove it, the records come back…. Then add the criteria back, and it works fine… until I save and close, then, NOTHING…)

If anyone has some insight into what I’m doing wrong, I’d love to hear it! Thanx in advance…

without being able to see the object, my guess is that when you open the form from nav pane, the combo has no value, thus the form has no records - because you've set its recordsource (query) using the combo as criteria. You might want to open the form loaded first, then apply a filter based on the combo. You can filter using the WHERE parameter when building the filter sql, or you can specify the name of the query as the filter query parameter since you already have the query.

You make a good point, Micron...

I was wondering exactly what the "firing order" of these bits was going to be. It's possible that loading the Form immediately calls the Query, passing the ComboBox's value (unspecified) to it, which give a null value to the criteria?

I have the ComboBox default value set, but for some reason, it's not passing it when the Form loads. Odd.

I've got some ideas to test this out. I'll let you know how it turns out.

It's possible that loading the Form immediately calls the Query,

Click to expand...

That's exactly how it works when it's designed that way. Also, the default value only applies to new records AND it only works for bound controls. If you are using properties and methods solely based on their names, you will sometimes find yourself going down the wrong path. Did you research "default" property, for example, or just go about figuring it would do what you expect?

Also, your post title refers to listbox, but your post seems to be about a combo. Again, either open the form and allow it to display records, then use the combo to apply the query as a filter, OR open the form to no records then apply. Usually this is done using the AfterUpdate event of a combo.