ms access combo box refresh

Question

i have 3 tables

chapters (id, name) heading (id, name, parentid) subheading (id, name, parentid)

the parents are the ids of the tables above each other.

i have a form with 3 combo boxes

chapter heading subheading.

i have vba for the onchange for chapter and heading. This code works fine:

Private Sub Chapter_AfterUpdate()
Me.Heading.RowSource = "SELECT [Headings].[ID], [Headings].[Headings], [Headings].[Parent] FROM Headings WHERE [Headings].[Parent] =" & Me.Chapter & " ORDER BY [Headings];"
Me.Heading.Requery
End Sub


Private Sub Heading_AfterUpdate()
Me.Subheading.RowSource = "SELECT [Subheadings].[ID], [Subheadings].[SubHeading], [Subheadings].[Parent] FROM Subheadings WHERE [Subheadings].[Parent] =" & Me.Heading.Value & " ORDER BY [SubHeading];"
Me.Subheading.Requery
End Sub

The above works fine, and the form saves the info into the new table correctly.

BUT...

When i open the form fresh (after closing access), the chapter appears filled into the combo, but the heading and subheading do not. ALTHOUGH, if i click on the chapter the vba runs, the currently saved record in the table i saved the record displays the correct sub-value. and the same for the subheading.

i am not getting any data loss, but i cannot get the combo boxes to display the saved data on a fresh for open.

even after a fresh of the form.

any help would be great thanks.

Requerying after setting the rowsource is redundant -- you need only assign the rowsource. — David-W-Fenton

Fionnuala Fionnuala · Accepted Answer · 2010-01-12T11:41:32

Put the update code for heading and subheading in the Current event of the form as well as the After Update event.

ms access combo box refresh

1 Answers