Dynamically Updating Listbox

[pat_nospam]

I have a listbox running a query of data, that is then used to select the records displayed on the form.

I.e. First name, last name, Sport, etc..

When it's clicked on, the form moves to that ID and displays the appropriate data.

I want to be able to put buttons on the side of the list box (or wherever) to click for the various sports, ie Football, Basketball, and when they are clicked for the listbox to update and filter based on the sport selected.

What's the proper method of doing this, I can accomplish this easily by adding another form to go through, which selects the sport, before they open this form. But I'd rather do it all in one form.

Ideas? Thanks

 

[dcx693]

To change the contents of a listbox, you need to access the rowsource.

You can explicitly set the rowsource like this:
Me.listboxName.RowSource=SQL string or queryname

Or you can add to the existing rowsource like this:
Me.listboxName.RowSource=Me.listboxName.RowSource & SQL string or queryname
(Just be careful with the syntax if you're going to use that method. There typically is a ";" at the end of SQL strings.)

See the Access online help for more info. Post back if you need more help.

 

[pat_nospam]

Alright, I understand what you are saying. Here is what I have so far. In the AfterUpdate, the following runs from my listbox to bring up the appropriate record.

-------
Private Sub List65_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List65], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
-------

The list box is unbound and populated by the following SQL query:

 

[pat_nospam]

Oops - to continue SQL query:

SELECT ATHLETES.ID, ATHLETES.Last_Name, ATHLETES.First_name, ATHLETES.MI, ATHLETES.Sport FROM ATHLETES WHERE (((ATHLETES.Active)=True));


So, I should add a second listbox with just the Sports in it, then set the following:

Me.List65.RowSource=Me.SportList.RowSource & SELECT ATHLETES.ID, ATHLETES.Last_Name, ATHLETES.First_name, ATHLETES.MI, ATHLETES.Sport FROM ATHLETES WHERE (((ATHLETES.Active)=True));


Am I in the ballpark?

Thanks

 

[pat_nospam]

Nevermind, I figured it out and just put a requery in the afterupdate() and altered my query to rely on the initial box.

Thanks