Clear a dependent listbox

[Jeff H]

I'm trying to develop a small garden knowledge base in Access 365. The table has a Topic and a Subtopic field. On the form I have a dropdown to select a Topic and a listbox to display its Subtopics so I can click one to bring it up. I use a Clear Filter button (cmdFilterOff) to remove the filter. Works great.

What I can't figure out is how to clear the Subtopic listbox when I launch the form and when I clear the Topic dropdown. I've experimented with a few things I found online but in the end, once I clear the listbox it won't repopulate the next time I select a Topic.

Here's the code I have so far:

Code: Select all

Private Sub cboFindTopic_AfterUpdate()
Dim sFilter As String

sFilter = "[Topic]=  '" & Me.cboFindTopic & "'"
If IsNull(Me.cboFindTopic) Then
   Me.FilterOn = False
Else
   Me.Filter = sFilter
   Me.FilterOn = True
End If
Me.lstSubtopics.Requery

End Sub


Private Sub cmdFilterOff_Click()
Me.FilterOn = False
Me.cboFindTopic = Null

End Sub


Private Sub lstSubTopics_AfterUpdate()
'===Converted from wizard generated macro
On Error GoTo lstSubTopics_AfterUpdate_Err

DoCmd.SearchForRecord , "", acFirst, "[InfoID] = " & Str(Nz(Screen.ActiveControl, 0))

lstSubTopics_AfterUpdate_Exit:
Exit Sub

lstSubTopics_AfterUpdate_Err:
MsgBox Error$
Resume lstSubTopics_AfterUpdate_Exit

End Sub

The Row Sources are:
cboTopics

Code: Select all

SELECT tblInfoTopic.Topic FROM tblInfoTopic; 

lstSubTopics

Code: Select all

SELECT tblGeneralInfo.InfoID, tblGeneralInfo.Subtopic, tblGeneralInfo.Topic 
FROM tblGeneralInfo 
WHERE (((tblGeneralInfo.Topic)=[Forms]![frmGeneralInfo]![Topic])); 

[HansV]

What happens now when you clear the Topic combo box? You requery the list box, so it should display only records with an empty topic - ideally there should be none...

[Jeff H]

That's what I thought, but after selecting a topic, which populates the listbox proprerly, then clicking the Clear Filter button, the topic dropdown is cleared but the listbox isn't. Also, whenever I open the form the listbox is populated with the first topic's subtopics.

[HansV]

Could you attach a zipped copy of the database?

[Jeff H]

It's too big (has pictures). I'll have to email it.

[HansV]

That's OK.

[HansV]

Thanks for sending me the database. The Row Source of lstSubTopics should refer to the combo box, not to the Topic field on the form:

SELECT InfoID, Subtopic, Topic FROM tblGeneralInfo WHERE Topic=[Forms]![frmGeneralInfo]![cboFindTopic] ORDER BY Subtopic;

[Jeff H]

I see why the reference was wrong as I had it. Now, with the revised Row Source SQL, when I open the form the Sub Topic listbox is empty. I just added "Me.lstSubtopics.Requery" to the Clear Button click event and now it is cleared when I remove the filter too.

I really appreciate you sharing your expertise!

- Jeff