Can a ListBox reflect the records on a Form?

[illini]

I have a form with a query as record source. On this form, I have a listbox with the same query as the row source.

Is it possible to have the listbox's row source reflect the form's record source. Thus, whenever data is sorted on the form, the listbox would reflect the sort.

Thanks.

-illini

 

[Frink]

Hallo,

You can update a list box using its Requery method.

- Frink

 

[illini]

Thanks for the input; however, this doesn't resolve the issue.

Based on the scenario I described above, requerying the listbox will not reflect the records as sorted on the form.

-illini

 

[Frink]

Hallo,

That would depend on the way you sort the records on the form.
If the form and listbox have the same query as their *source then the orders should be the same.

If you then change the sort order on the form, they'll get out of step, I'd agree. Is this is what you are doing?

How are you changing the order on the form?

- Frink

 

[illini]

Let's say I have a simple form. Its record source is a query which has two fields (rec_id and state). The query is sorted by the rec_id. The form has two textboxes (one for each field) and a listbox which shares the same query as the form.

If I sort the form by state, the listbox will still reflect the original query (sorted by rec_id). Sorting the form's record source does not appear to actually affect the query.

-illini

 

[BlackKnight]

Hi,

When the form's record source is sorted change the RowSource property of the listbox to match the form like:

When the form is sorted by State change the Rowsource of the listbox to:
SELECT Rec_ID, State, FROM tblstate ORDER BY State

change it back to ORDER BY Rec_ID when the form is sorted by Rec_ID.

Have a good one!
BK

 

[Frink]

Hallo,

So you are applying another sort to the form. Are you applying a filter/sort from the menu bar? If you are then this will not change the list box.

Like BlackNight says, you could change the RowSource and RecordSource properties when you want to sort.

You could have a set of radio buttons indicating what you want to sort by, then the OnChange event of the radio button frame would set both the form recordsource and the list box row source accordingly.

The next logical step might be to write a subroutine (in the form module) to do that for you:

Public Sub SetFormSorting (byval strSortField as String)
  Dim strSQLSelect as String
  Dim strSQLOrder as String
  strSQLSelect = "SELECT Rec_ID, State FROM tblstate"
  If Len(strSortField) <> 0 Then
    strSQLOrder = &quot; ORDER BY &quot; & strSortField
  Else
    strSQLOrder = &quot;&quot;
  End If
  Me.RecordSource = strSQLSelect & strSQLOrder
  Me!lstMyListBox.RowSource = strSQLSelect & strSQLOrder
End Sub

Then the code in the OnChange event of the sort selection radio buttons could be:

Select Case Me!fraSortSelection
  Case 0
    SetFormSorting "Rec_ID"
  Case 1
    SetFormSorting "State"
  Case 2
    SetFormSorting "State DESC"
  Case Else
    SetFormSorting ""
End Select

Use whatever options you want.
It's good to use code like this rather than repeating SQL statements in code as it is easier to make changes.

Hope that helps,

- Frink