Access 2003 FORMS: when I set at runtime with VBA the "RowSource" for a ListBox persist even if I close and then open

Question

Access 2003 FORMS: when I set at runtime with VBA the "RowSource" for a ListBox persist even if I close and then open...

How to fix this, I would like to have clean "RowSource" when I open a new form...

Answer 1

You can set the RowSource during form load.

Private Sub Form_Load()
    Dim strSql As String
    strSql = "SELECT f.id, f.fname FROM foo AS f ORDER BY f.fname;"
    Me.lstNames.RowSource = strSql
End Sub

Answer 2

Setting the RowSource of the List Box changes the Form's design. Access wants to save those changes for you (actually, I think the default behavior is to ask). If you want to close the form without changes, put this code in a Command Button's OnClick:

DoCmd.Close acForm, Me.Name, acSaveNo

The last parameter tells Access to not save the changes. Another alternative is what HansUp gave you in his second comment to his answer--just disable the List Box. Then when you figure out what its RowSource should be (on user input), set the RowSource & the Enabled property.