Changing a List Box's Row Source when UserForm is loaded.

  • Thread starter Thread starter Jan G. Thorstensen
  • Start date Start date
J

Jan G. Thorstensen

Hi. I am using Access2k and win98.

I have a form with several bound Controls. I also have an
unbound List Box Control with four Columns where the
Row Source is based on a query.

For quick lookup, the user use the vertical scrollbar in the List Box
and find the item to choose and the form mooves to a record that
have the same Id as the selected item in the List Box. (Se code
below).

Now, I want to have the List Box sorted Ascending after different
Columns. Is it possible to change the Row Source of the List Box
by code when the form is loading (showing)?
Since the Row Source is a query, I want to change the sql sentence
to, lets say:
SELECT Varer.VareID, Va.............ORDER BY Varer.Varenavn;
or toggle back to ...........ORDER BY Varer.Model.

My question is:
Is it possible to change the query sentence and update the List Box
according to the new query, while the form is open???

Any help will be very much apprieciated. Thanks in advance..


Jan

PS.
My code for going to selected record:
--------------------------------------
Private Sub lstVarer_Click()
On Error GoTo ShowTrap
Dim strValue As String, strMsg As String

strValue = Me.lstVarer.Column(0) ' Get the ID...
DoCmd.OpenForm "frmVarer", acNormal, , "VareID = " & strValue

ShowTrapExit:
Exit Sub

ShowTrap:
strMsg = "Error number: " & Err.Number & "caused " & _
"failure. Its description is:" & vbCrLf & _
Err.Description
MsgBox strMsg, vbExclamation, _
"Custom Program"
Resume ShowTrapExit
End Sub
 
Yes, a listbox's RowSource is just a string property (as are many
properties); you can change it in code:
MyListBox.RowSource = "SELECT ...."
Changing that property should cause the listbox to requery, but to be sure
you can add
MyListBox.Requery

HTH
- Turtle
 
Back
Top