Set Form's selected record to nothing

breinhold

New member
Local time
Today, 13:36
Joined
Mar 28, 2017
Messages
2
I have a list box on a form from which the user can select a record. I've set up the ability to filter the list box. This does not filter the form's recordset (at least not currently).

Sometimes the filter may return an empty list. If that happens, I'd like the main form to remove the record selection and empty out all the form fields. The user cannot create new records from this form, so it can't move to a new, blank record.

Is there a way to do this?
 
I guess you could set the form's record source to an empty string for this case making the form unbounded. When the filter returns something set it to it's normal record source.
 
A listbox has a recordset property. One thing you could try is set the Listbox.Recordset to the same object as the Form.Recordset, so...
Code:
Set Me.lstMyListbox.Recordset = Me.Recordset
...and experiment with that. Or do that the other way around if it makes more sense. But then you may not need to communicate state or currency from one recordset to the other, since there is only one recordset.
 
Thanks to both of you for responding so quickly. Both solutions leave me with a form with an empty recordset, which results in "#Name?" in all my form fields.

For now at least, I think I'll be better off preventing selection of a filter that returns no records.

Thanks again.
 
Oops, Sorry, didn't think about the fact that the control sources would still be there. I think your idea of preventing the problem makes more sense but if you come back to this idea you could try switching the record source to a query which has the same field names but consists of just expressions that return null.
 
Normally when you use a ListBox or ComboBox to select a record (and obviously, for the ListBox, .Multiselect must be False), you deselect the selected record implied by the control's .RowSource. You do this by setting the ComboBox's .ListIndex to -1 or setting the selected ListBox row's .Selected property to False.

There is also the ability to do an .Undo on some controls, and I think that applies to the ListBox because it has query-oriented selection. Update: I have confirmed from Object Browser that a ListBox has this method. (TextBox doesn't have a query so can't be undone, by contrast.) I don't think a ComboBox has a .Undo method, but there the .ListIndex can be reset. Note that when you attempt to do this, you MIGHT have to momentarily put the box into focus. I recall that when I did this, I had to play with it a bit.
 

Users who are viewing this thread

Back
Top Bottom