ListBox Help

[Zippyfrog]

I am trying to do some work with listboxes but am having some trouble. I don't know the best way to ask for help on this, so I will be as specific as possible

I have 2 tables in my database
tblAthletes (with primary key AthleteID, also fields lastname and firstname)
tblRoster (with primary key RosterID being an autonumber, and AthleteID which is the foreign key that matches up with AthleteID from tblAthlete)

My problem is the roster form. I have a list box, list0, which has AthleteID, lastName, and FirstName from tblAthlete.

When I click a command button on my form, I want the AthleteID that is selected in List0 to be "copied" over to the AthleteID field in tblRoster.

Example: If I have the athlete highlighted in List0 whose AthleteID is 147, I want that 147, which is from tblAthlete, to be placed into a new record in the AthleteID spot on tblRoster.


How would I go about doing this? Any help you can give would be greatly appreciated. Thanks.

-Chris

 

[Fornatian]

1. Use an append query to update the tblRoster table with the list box's value by setting the append value as Forms!YourForm!List0, changing YourForm obviously.

2. Then requery the listbox displaying tblRoster something like:

Private Sub Button1_Click()
Docmd.OpenQuery "YourAppendQuery"
Me.ListBoxRoster.Requery

There are more complicated and prettier ways of achieving the same but that works.

Could you please tell me what you are trying to acheive because this seems a redundant exercise if there is no other data stored in your roster table - you would be better with a yes/no field in your athlete table to indicate whether that person is on a roster or not, that is if the rosterID doesn't actually mean anything but to indicate they are on a roster. Using a yes/no you can then use a query to determine the rostered athletes.